В экселе формулы: Самые полезные формулы Excel — Раздел 5. Урок 1 — Онлайн-курс «Digital-аналитика» — This is Data

Комментарии к ячейке в формуле в Excel — TutorExcel.Ru

Поговорим про комментарии в формулах к ячейкам в Excel, которые помогают дать текстовое описание или разъяснение для числовых формул.

---

Приветствую всех, уважаемые читатели блога TutorExcel.Ru!

Как часто мы разбираясь в чужой или хорошо забытой своей формуле пытаемся вспомнить как она вообще считалась и что значат отдельные ее элементы? Лично мне в этот момент очень не хватает комментария, который бы мог дать короткие пояснения что и откуда берется.

Поэтому сегодня у нас не совсем обычная тема, потому что добавлять комментарии в ячейку на первый взгляд может показаться странной затеей. Ведь можно добавить примечание к ячейке или написать комментарий где-то рядом в соседней ячейке.

Однако и у этих способов есть ряд недостатков, например, структура книги может быть такой, что нет возможности или попросту неудобно добавить комментарий рядом с формулой, а примечание неудобно смотреть в режиме редактирования ячейки. К тому же вышеуказанные способы могут дать комментарии в целом к формуле, но не к отдельным ее частям, что тоже время от времени бывает необходимо.

В общем давайте переходить к делу и на конкретном примере посмотрим как можно вставить комментарий в ячейке к формуле, а заодно сделаем формулу гораздо более читаемой.

Как добавить комментарий к ячейке в Excel?

Давайте для примера рассмотрим небольшую таблицу, где участникам проставлены оценки за соревнования и рассчитывается средняя оценка за выступление:

Но при этом, как на практике часто бывает, средняя оценка будет считаться не как среднее арифметическое всех чисел, а как среднее за вычетом минимального и максимального значения. Давайте вот эту формулу и возьмем за пример для разбора инструмента:

При этом нам вообще не так важно какая именно формула записана в ячейке, она может быть любой сложности.

Взглянув на формулу с большим количеством скобок не всегда понятно где какая часть начинается, а где какая заканчивается, что зачастую мешает грамотному редактированию формулы, как нам, так и любому другому человеку, работающему с этим файлом.

Поэтому первым делом приведем формулу в более читаемый вид.

Как сделать формулу в Excel более читаемой?

Давайте немного задумаемся что вообще такое читаемый и удобный вид для формулы.

Обычно мы ее пишем в одну строчку, без пробелов, но с точки зрения читаемости иногда это выглядит как чтение книги в которой нет абзацев — один сплошной текст. Как только мы видим абзацы и красные строки, то текст преобразуется и его гораздо легче воспринимать. Аналогичного эффекта можно достичь и с формулами в Excel.

В режиме редактирования нам на самом деле доступно много возможностей для настройки внешнего вида формулы без фактического изменения значения формулы.

Например, мы прекрасно понимаем что запись вида «2+3» и «2 + 3» это одно и тоже, хоть они записаны немного по разному. Excel тоже так умеет, поэтому мы можем ставить пробелы и переносить часть формулы на следующие строки, в тех случаях когда не нарушается логика чтения синтаксиса формулы.

Давайте перенесем каждую из функций в формуле на отдельную строку, чтобы наглядно показать как эта формула считается, проставив пробелы и переносы строк в соответствующих местах:

Напомню, что перенос строки в Excel делается с помощью горячих клавиш — в режиме редактирования нужно воспользоваться сочетанием клавиш Alt + Enter.

Вставка комментария в формуле к ячейке в Excel

Теперь давайте перейдем непосредственно к добавлению комментария. Для этого нам понадобится стандартная функция Ч в Excel, которая преобразует данные в числовые значения:

Ч (значение)

Преобразует нечисловые значения в числа, даты — в даты, представленные числами, значения ИСТИНА в 1, все остальные значения — в 0 (ноль).

Полезная особенность этой функции заключается в том, что текстовые значения она преобразует в 0, таким образом добавляя в формулу эту функцию с текстовым комментарием мы никак не изменяем значение формулы — фактически мы к значению формулы прибавляем 0:

В результате мы получаем весьма понятный для чтения вид формулы, после просмотра которого наверняка большинство вопросов по расчету формулы наверняка отпадут и тем самым сэкономят много времени.

И при этом, конечно, значение ячейки осталось точно таким же, чего мы и добивались.

Скачать файл с примером.

Спасибо за внимание!
Если у вас есть вопросы по теме статьи — заходите, спрашивайте и делитесь мыслями в комментариях.

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Поделиться с друзьями:

Поиск по сайту:

Как написать формулу в Excel

С момента своего появления в составе пакета Office Эксель стал постоянным рабочим инструментом для тех, кто занимается анализом и структурированием данных. С годами благодаря постоянной разработке и улучшениям этого продукта пользоваться им смогли все — от «продвинутых» до самых обычных людей. Курс на упрощение работы с данными от Microsoft выражается в постоянном упрощении методов работы с сохранением функционала, так что вполне объяснимо, что в руках пользователя Excel становится мощнейшим инструментом.

Для проведения расчётов используют готовые функции

Тем не менее, многие функции пока что не получается упростить. Это не значит, что они потребуют от вас навыков программирования или знания высшей математики. Хотя написание макросов и может предъявить вам такие условия, но у них в принципе высокий порог вхождения. Большую часть функционала Office вы в состоянии использовать с помощью кликов мышью и коротких команд с клавиатуры.

Создание формул в Excel

Для обработки значений и их диапазонов — от банальной суммы до методов математической статистики вроде медианы или моды — используются формулы. Это очень гибкий и при этом достаточно простой инструмент, который широко используется наряду с условным форматированием при создании таблиц с данными. В самом простом случае для записи формулы в ячейку используется обычный текстовый ввод. Чтобы провести, например, сложение значений из нескольких полей, можно прямо в ячейке написать «=СУММ()» и в скобках указать адреса значений через точку с запятой. Или, если они расположены рядом друг с другом, указать начальную и конечную через двоеточие. А чтобы закрепить в этой формуле ячейку, например, чтобы не менялись столбец или строка при копировании формулы в другую, поставьте перед нужным символом знак «$». Тогда Эксель не будет менять этот экранированный символ, при этом меняя другие.

В принципе можно так ввести любую формулу. Так как Excel поддерживает текстовый ввод, нужно только заранее знать нужные значения, которые вы туда подставите. Но если для простых действий это приемлемо, то для более-менее сложных это уже затруднительно, так как придётся запоминать слишком уж много параметров. Поэтому легче использовать встроенный в программу конструктор формул. Для того чтобы его вызвать, нужно нажать на кнопку в верхней части страницы, слева от поля ввода значения в ячейку.

Появится диалоговое окно, в котором пользователь сможет создать правило обработки данных. В левой части окна есть список упорядоченных по категориям формул. Есть математические, статистические, текстовые группы и т. д. Выбрав нужную, щёлкните по ней левой кнопкой мыши. В правой части диалогового окна будет отображена форма заполнения значений. В ней вы сможете указать диапазоны данных или конкретные одиночные адреса. Чтобы это сделать, нажмите в правой части поля ввода кнопку выделения, окно свернётся, и вы получите доступ к рабочей части листа Excel. Так как в этом режиме можно выбирать как одиночные значения, так и их диапазоны, ввести нужные значения не составит труда. После этого снова нажмите ту же кнопку, и окно восстановит прежний размер. Если в формуле требуется не один набор данных, а несколько, то повторите описанную процедуру требуемое количество раз.

В этом поле, кстати, правила экранирования также работают. Поэтому если вы потом содержимое этой ячейки будете распространять и на другие, и при этом хотите сохранить значения данных, не забудьте поставить в нужные места знак доллара.

Когда ввод в конструкторе будет завершён, нажмите ОК. На этом этапе Excel проверяет введённые значения на соответствие типов и на другие ошибки, поэтому может выкинуть текст ошибки. По нему вы сможете понять, что именно нужно изменить, чтобы формула работала так, как вы от неё ожидаете. Если же ошибок не будет, окно закроется, а в ячейке появится значение. Иногда бывает так, что какой-то из указанных диапазонов ещё не заполнен, но присвоенный ячейке тип содержимого соответствует нужному. Тогда на этапе проверки ошибок не будет, но и значение будет подсчитано без него, так что будьте внимательны.

Удаление формул

В Excel данные можно менять. И если структура таблицы изменилась, то, возможно, некоторые формулы больше не нужны. Либо отпала необходимость в расчёте величины, либо теперь она рассчитывается в другом файле и т. д. Тогда такую формулу надо убрать, так как Excel при каждом запуске будет пытаться её посчитать. Чтобы удалить из ячейки содержимое, просто выделите её и нажмите Delete. Это полная очистка, и в ней не останется никаких данных. В случае, если вы хотите сохранить формулу, чтобы после её использовать, можно просто в значении ячейки убрать перед ней знак равенства, с которого начинается любая формула. Тогда весь её тело будет считаться текстовыми данными, и не будет выполнять никаких действий.

Заключение

Конструируемые формулы в Excel позволяют работать с данными, анализировать их и менять на основе других данных. Это очень гибкий и при этом простой в работе инструмент, который с помощью встроенных средств может быть эффективным в руках практически любого пользователя.

Основные формулы и функции Excel с примерами

Учебное пособие содержит список основных формул и функций Excel с примерами и ссылками на соответствующие подробные учебные пособия.

Первоначально разработанная как программа для работы с электронными таблицами, Microsoft Excel чрезвычайно мощная и универсальная, когда дело доходит до вычисления чисел или решения математических и инженерных задач. Это позволяет вам подсчитать или усреднить столбец чисел в мгновение ока. Кроме того, вы можете рассчитать сложные проценты и средневзвешенное значение, получить оптимальный бюджет для вашей рекламной кампании, минимизировать стоимость доставки или составить оптимальный график работы для ваших сотрудников. Все это делается путем ввода формул в ячейки.

Цель этого руководства — научить вас основам работы с функциями Excel и показать, как использовать основные формулы в Excel.

Основы формул Excel

Прежде чем предоставить список основных формул Excel, давайте определим ключевые термины, чтобы убедиться, что мы находимся на одной странице. Итак, что мы называем формулой Excel и функцией Excel?

Например, вместо того, чтобы указывать каждое значение для суммирования, как в приведенной выше формуле, вы можете использовать функцию СУММ для сложения диапазона ячеек: =СУММ(A2:A4)

Все доступные функции Excel можно найти в Библиотеке функций на вкладке Формулы :

В Excel существует более 400 функций, и их число увеличивается от версии к версии. Конечно, запомнить их все практически невозможно, да это и не нужно. Мастер функций поможет вам найти функцию, наиболее подходящую для конкретной задачи, а Excel Formula Intellisense подскажет синтаксис и аргументы функции, как только вы введете имя функции, которому предшествует знак равенства в ячейке:

Если щелкнуть имя функции, оно превратится в синюю гиперссылку, которая откроет раздел справки для этой функции.

Совет. Вам не обязательно вводить имя функции заглавными буквами, Microsoft Excel автоматически сделает его заглавным, как только вы закончите вводить формулу и нажмете клавишу Enter, чтобы завершить ее.

10 основных функций Excel, которые вы обязательно должны знать

Далее следует список из 10 простых, но очень полезных функций, которые необходимы каждому, кто хочет превратиться из новичка в Excel в профессионала.

СУММА

Первая функция Excel, с которой вы должны быть знакомы, — это та, которая выполняет базовую арифметическую операцию сложения:

СУММ( число1 , [число2], …)

В синтаксисе всех функций Excel аргумент, заключенный в [квадратные скобки], является необязательным, требуются другие аргументы.

Это означает, что ваша формула суммы должна включать как минимум 1 число, ссылку на ячейку или диапазон ячеек. Например:

=СУММ(B2:B6) — суммирует значения в ячейках с B2 по B6.

=СУММ(B2, B6) — суммирует значения в ячейках B2 и B6.

При необходимости можно произвести другие расчеты в рамках одной формулы, например, сложить значения в ячейках с B2 по B6, а затем разделить сумму на 5:

=СУММ(B2:B6)/5

Для суммирования с условиями используйте функцию СУММЕСЛИ: в 1-м аргументе вы вводите диапазон ячеек для проверки на соответствие критериям (A2:A6), во 2-м аргументе — сам критерий (D2), а в последнем аргумент — ячейки для суммирования (B2:B6):

=СУММЕСЛИ(A2:A6, D2, B2:B6)

В листах Excel формулы могут выглядеть примерно так:

Совет. Самый быстрый способ суммировать столбец или ряд чисел — это выбрать ячейку рядом с числами, которые вы хотите суммировать (ячейка непосредственно под последним значением в столбце или справа от последнего числа в строке).

) и нажмите кнопку AutoSum на вкладке Home в Formats 9Группа 0004. Excel автоматически вставит формулу СУММ.

Полезных ресурсов:

• Excel Примеры формул суммы — формулы для суммирования столбца, строки, только отфильтрованных (видимых) ячеек или суммирования по листам.
• Excel AutoSum — самый быстрый способ суммирования столбца или строки чисел.
• СУММЕСЛИ в Excel — примеры формул для условного суммирования ячеек.
• СУММЕСЛИМН в Excel — примеры формул для суммирования ячеек на основе нескольких критериев.

СРЕДНЕЕ

Функция Excel AVERAGE делает именно то, что следует из ее названия, т. е. находит среднее или среднее арифметическое чисел. Его синтаксис аналогичен SUM:

.

СРЕДНЕЕ(число1, [число2], …)

Присмотревшись к формуле из предыдущего раздела ( =СУММ(B2:B6)/5 ), что она на самом деле делает? Суммирует значения в ячейках с B2 по B6, а затем делит результат на 5. А как вы называете сложение группы чисел, а затем деление суммы на количество этих чисел? Ага, средний!

Функция Excel AVERAGE выполняет эти вычисления в фоновом режиме. Итак, вместо того, чтобы делить сумму на количество, вы можете просто поместить эту формулу в ячейку:

=СРЕДНЕЕ(B2:B6)

Для усреднения ячеек на основе состояния используйте следующую формулу СРЗНАЧЕСЛИ, где A2:A6 — диапазон критериев, D3 — критерии, а B2:B6 — ячейки для усреднения:

=СРЗНАЧЕСЛИ(A2:A6, D3, B2:B6)

Полезных ресурсов:

• Excel СРЗНАЧ — усреднение ячеек с числами.
• Excel AVERAGEA — найти среднее значение ячеек с любыми данными (числами, логическими и текстовыми значениями).
• Excel СРЗНАЧЕСЛИ — усреднение ячеек по одному критерию.
• Excel СРЗНАЧЕСЛИМН — усреднение ячеек на основе нескольких критериев.
• Как рассчитать средневзвешенное значение в Excel
• Как найти скользящее среднее в Excel

МАКС.

И МИН.

Формулы MAX и MIN в Excel получают наибольшее и наименьшее значение в наборе чисел соответственно. Для нашего примера набора данных формулы будут такими простыми, как:

=МАКС(В2:В6)

= МИН(B2:B6)

Полезных ресурсов:

• Функция MAX — найти наибольшее значение.
• MAX IF формула — получить наибольшее число с условиями.
• Функция MAXIFS — получить наибольшее значение на основе нескольких критериев.
• Функция MIN — возвращает наименьшее значение в наборе данных.
• Функция MINIFS — найти наименьшее число на основе одного или нескольких условий.

СЧЁТ И СЧЁТ

Если вам интересно узнать, сколько ячеек в заданном диапазоне содержит числовые значения (числа или даты), не тратьте время на их ручной подсчет. Функция СЧЁТ в Excel моментально подсчитает:

.

СЧЁТ(значение1, [значение2], …)

В то время как функция COUNT работает только с теми ячейками, которые содержат числа, функция COUNTA подсчитывает все ячейки, которые не являются пустыми , независимо от того, содержат ли они числа, даты, время, текст, логические значения TRUE и FALSE, ошибки или пустые текстовые строки. («»):

СЧЕТЧИК (значение1, [значение2], …)

Например, чтобы узнать, сколько ячеек в столбце B содержат числа, используйте следующую формулу:

=СЧЁТ(В:В)

Чтобы подсчитать все непустые ячейки в столбце B, используйте этот:

=СЧЁТ(В:В)

В обеих формулах используется так называемая «ссылка на весь столбец» (B:B), которая относится ко всем ячейкам в столбце B.

На следующем снимке экрана видно различие: в то время как COUNT обрабатывает только числа, COUNTA выводит общее количество непустых ячеек в столбце B, включая текстовое значение в заголовке столбца.

Полезных ресурсов:

• Функция СЧЕТ Excel — быстрый способ подсчета ячеек с числами.
• Excel Функция COUNTA — подсчет ячеек с любыми значениями (непустые ячейки).
• Функция СЧЁТЕСЛИ в Excel — подсчёт ячеек, удовлетворяющих одному условию.
• Excel Функция СЧЁТЕСЛИМН — подсчитывает ячейки по нескольким критериям.

ЕСЛИ

Судя по количеству комментариев в нашем блоге о ЕСЛИ, это самая популярная функция в Excel. Проще говоря, вы используете формулу ЕСЛИ, чтобы попросить Excel проверить определенное условие и вернуть одно значение или выполнить одно вычисление, если условие выполнено, и другое значение или вычисление, если условие не выполнено:

ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

Например, следующий оператор IF проверяет, выполнен ли заказ (т. е. есть ли значение в столбце C) или нет. Чтобы проверить, не является ли ячейка пустой, вы используете оператор «не равно» (<>) в сочетании с пустой строкой («»). В результате, если ячейка C2 не пуста, формула возвращает «Да», иначе «Нет»:

=ЕСЛИ(C2<>"", "Да", "Нет")

Полезных ресурсов:

• Функция ЕСЛИ в Excel с примерами формул
• Как использовать вложенные ЕСЛИ в Excel
• Формулы ЕСЛИ с несколькими условиями И/ИЛИ

ТРИМ

Если ваши явно правильные формулы Excel возвращают только кучу ошибок, первое, что нужно проверить, — это лишние пробелы в ячейках, на которые ссылаются пока что-то не пойдет не так!).

Существует несколько способов удаления ненужных пробелов в Excel, самый простой из которых — функция ОБРЕЗ:

ОТДЕЛКА(текст)

Например, чтобы убрать лишние пробелы в столбце A, введите следующую формулу в ячейку A1, а затем скопируйте ее вниз по столбцу:

=ОТРЕЗКА(A1)

Он удалит все лишние пробелы в ячейках, кроме одного пробела между словами:

Полезных ресурсов:

• Функция ОБРЕЗА в Excel с примерами формул
• Как удалить разрывы строк и непечатаемые символы
• Как удалить неразрывные пробелы ( )
• Как удалить определенный непечатаемый символ

ЛЕН

Всякий раз, когда вы хотите узнать количество символов в определенной ячейке, используйте функцию LEN:

ДЛСТР(текст)

Хотите узнать, сколько символов в ячейке A2? Просто введите приведенную ниже формулу в другую ячейку:

.

=ДЛСТР(A2)

Имейте в виду, что функция ДЛСТР в Excel считает абсолютно все символы включая пробелы :

Хотите получить общее количество символов в диапазоне или ячейках или подсчитать только определенные символы? Пожалуйста, ознакомьтесь со следующими ресурсами.

Полезных ресурсов:

• Формулы Excel LEN для подсчета символов в ячейке
• Подсчитать количество символов в ячейках и диапазонах

И и ИЛИ

Это две самые популярные логические функции для проверки нескольких критериев. Разница в том, как они это делают:

• И возвращает ИСТИНА, если все условия выполнены, в противном случае ЛОЖЬ.
• ИЛИ возвращает ИСТИНА, если выполняется какое-либо условие , иначе ЛОЖЬ.

Хотя эти функции редко используются сами по себе, они очень удобны в составе более крупных формул.

Например, чтобы проверить результаты теста в столбцах B и C и вернуть «Пройдено», если оба значения больше 60, и «Не пройдено» в противном случае, используйте следующую формулу ЕСЛИ со встроенным оператором И:

=ЕСЛИ(И(B2>60, B2>60), "Пройдено", "Не пройдено")

Если достаточно иметь только один результат теста выше 60 (либо тест 1, либо тест 2), вставьте оператор ИЛИ:

=ЕСЛИ(ИЛИ(B2>60, B2>60), "Пройдено", "Не пройдено")

Полезных ресурсов:

• Excel Функция И с примерами формул
• Функция Excel ИЛИ с примерами формул

СЦЕПИТЬ

Если вы хотите взять значения из двух или более ячеек и объединить их в одну ячейку, используйте оператор конкатенации (&) или функцию СЦЕПИТЬ:

СЦЕПИТЬ(текст1, [текст2], …)

Например, чтобы объединить значения из ячеек A2 и B2, просто введите следующую формулу в другую ячейку:

= СЦЕПИТЬ (A2, B2)

Чтобы разделить объединенные значения пробелом, введите пробел (» «) в списке аргументов:

= СЦЕПИТЬ (A2, " ", B2)

Полезных ресурсов:

• Как объединить в Excel — примеры формул для объединения текстовых строк, ячеек и столбцов.
• Функция CONCAT — более новая и улучшенная функция для объединения содержимого нескольких ячеек в одну ячейку.

СЕГОДНЯ И СЕЙЧАС

Чтобы видеть текущую дату и время всякий раз, когда вы открываете рабочий лист, не обновляя его вручную ежедневно, используйте:

=СЕГОДНЯ() для вставки текущей даты в ячейку.

=СЕЙЧАС() для вставки текущей даты и времени в ячейку.

Прелесть этих функций в том, что они вообще не требуют никаких аргументов, вы вводите формулы точно так, как написано выше.

Полезных ресурсов:

• Функция Excel NOW — как вставить текущую дату и время в качестве динамического значения.
• Как вставить сегодняшнюю дату в Excel — различные способы ввода текущей даты в Excel: как неизменяемая отметка времени или автоматически обновляемая дата и время.
• Функции даты Excel с примерами формул — формулы для преобразования даты в текст и наоборот, извлечения дня, месяца или года из даты, вычисления разницы между двумя датами и многого другого.

Рекомендации по написанию формул Excel

Теперь, когда вы знакомы с основными формулами Excel, эти советы дадут вам некоторые рекомендации о том, как использовать их наиболее эффективно и избежать распространенных ошибок в формулах.

Не заключайте числа в двойные кавычки

Любой текст, включенный в ваши формулы Excel, должен быть заключен в «кавычки». Однако вы никогда не должны делать это с числами, если только вы не хотите, чтобы Excel обрабатывал их как текстовые значения.

Например, чтобы проверить значение в ячейке B2 и вернуть 1 для «Пройдено», 0 в противном случае, вы помещаете следующую формулу, скажем, в C2:

=ЕСЛИ(B2="пройти", 1, 0)

Скопируйте формулу в другие ячейки, и вы получите столбец из 1 и 0, который можно вычислить без проблем.

А теперь посмотрите, что произойдет, если вы удвоите число в кавычках:

=ЕСЛИ(B2="пройти", "1", "0")

На первый взгляд вывод нормальный — тот же столбец из 1 и 0. Однако при ближайшем рассмотрении вы заметите, что результирующие значения по умолчанию выровнены по левому краю в ячейках, что означает, что это числовые строки, а не числа! Если позже кто-то попытается вычислить эти 1 и 0, он может в конечном итоге рвать на себе волосы, пытаясь понять, почему 100% правильная формула суммы или счета не возвращает ничего, кроме нуля.

Не форматировать числа в формулах Excel

Пожалуйста, запомните это простое правило: числа, используемые в ваших формулах Excel, должны быть введены без какого-либо форматирования, такого как десятичный разделитель или знак доллара. В Северной Америке и некоторых других странах запятая является разделителем аргументов по умолчанию, а знак доллара ($) используется для создания абсолютных ссылок на ячейки. Использование этих символов в числах может просто свести с ума ваш Excel 🙂 Итак, вместо того, чтобы вводить 2000 долларов, просто введите 2000, а затем отформатируйте выходное значение по своему вкусу, настроив собственный числовой формат Excel.

Совпадение со всеми открывающими и закрывающими скобками

При создании сложной формулы Excel с одной или несколькими вложенными функциями вам придется использовать более одного набора скобок для определения порядка вычислений. В таких формулах обязательно правильно соединяйте скобки, чтобы на каждую открывающую скобку была закрывающая скобка. Чтобы облегчить вам работу, Excel окрашивает пары скобок в разные цвета, когда вы вводите или редактируете формулу.

Скопируйте ту же формулу в другие ячейки вместо повторного ввода

После того, как вы ввели формулу в ячейку, нет необходимости вводить ее снова и снова. Просто скопируйте формулу в соседние ячейки, перетащив маркер заполнения (небольшой квадрат в правом нижнем углу ячейки). Чтобы скопировать формулу во весь столбец, поместите указатель мыши на маркер заполнения и дважды щелкните знак «плюс».

Примечание. После копирования формулы убедитесь, что все ссылки на ячейки указаны правильно. Ссылки на ячейки могут изменяться в зависимости от того, являются ли они абсолютными (не изменяются) или относительными (изменяются).

Подробные пошаговые инструкции см. в разделе Копирование формул в Excel.

Как удалить формулу, но сохранить рассчитанное значение

При удалении формулы нажатием клавиши Delete вычисляемое значение также удаляется. Однако вы можете удалить только формулу и оставить полученное значение в ячейке. Вот как:

• Выберите все ячейки с вашими формулами.
• Нажмите Ctrl + C, чтобы скопировать выбранные ячейки.
• Щелкните правой кнопкой мыши выделенный фрагмент и выберите 9.0003 Вставить значения > Значения , чтобы вставить вычисленные значения обратно в выбранные ячейки. Или нажмите сочетание клавиш «Специальная вставка»: Shift+F10, а затем V.

Подробные инструкции со снимками экрана см. в разделе Как заменить формулы их значениями в Excel.

Убедитесь, что для параметров расчета установлено значение «Автоматически».

Если вдруг ваши формулы Excel перестали автоматически пересчитываться, скорее всего, Параметры расчета каким-то образом переключился на Ручной . Чтобы исправить это, перейдите на вкладку Формулы > группу Расчет , нажмите кнопку Параметры расчета и выберите Автоматически .

Если это не помогает, выполните следующие действия по устранению неполадок: Формулы Excel не работают: исправления и решения.

Таким образом вы создаете основные формулы и управляете ими в Excel. I как вы найдете эту информацию полезной. В любом случае, я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе.

Вас также может заинтересовать

Как использовать формулы и функции Excel

Практическое руководство

Формулы — это мощные инструменты для выполнения вычислений и анализа данных в Excel. В этом руководстве для начинающих вы узнаете, как использовать формулы, и познакомитесь с некоторыми популярными встроенными функциями.

Шимон Брэтуэйт

Внештатный писатель, Компьютерный мир |

Thinkstock Содержание

• Что такое формула в Excel?
• Что такое функция в Excel?
• 1. Основные математические формулы и функции
• 2. Функция ЕСЛИ
• 3. Функции СУММЕСЛИ и СЧЁТЕСЛИ
• 4. Функция СЦЕП
• 5. Функция ВПР

Показать больше

Одна из наиболее часто используемых программ Microsoft, Excel очень удобна для сбора, обработки и анализа данных. Однако, чтобы полностью использовать возможности Excel, вам нужно использовать формулы.

Формулы Excel позволяют выполнять вычисления, анализировать данные и быстро и точно выдавать результаты. Полезность формул становится еще выше, когда вы начинаете работать с большими наборами данных. При правильной формуле Excel может обрабатывать огромные объемы информации за считанные секунды.

Что такое формула в Excel?

Формула — это выражение, которое работает со значениями в диапазоне ячеек в Excel. Используя формулы, вы можете выполнять вычисления и анализ данных о содержимом ячеек. Формулы могут быть такими же простыми, как сложение столбца чисел вместе, или такими же сложными, как возврат эксцесса набора данных. Они могут быть невероятно полезны, когда вы хотите превратить данные электронной таблицы в значимую информацию для принятия бизнес-решений.

Что такое функция в Excel?

Функция — это встроенная формула в Excel — по сути, ярлык для выполнения вычисления или другой операции с данными ячейки. Существует около 500 функций Excel, и их список продолжает расти с каждым годом. К счастью, большинство действий, которые хотел бы выполнить типичный бизнес-пользователь, можно выполнить с помощью всего нескольких функций.

В этой статье мы рассмотрим пять полезных типов формул и функций, которые помогут вам начать анализ данных в Excel. Попутно вы изучите несколько различных способов ввода формул и функций в Excel.

Мы продемонстрируем использование Excel для Windows в рамках подписки Microsoft 365. Если вы используете другую версию Excel, у вас могут быть не совсем такие же интерфейс и параметры, но формулы и функции работают одинаково.

1. Основные математические формулы и функции

Мы собираемся сгруппировать эти формулы, так как они очень просты и имеют схожий синтаксис. Все формулы в Excel начинаются со знака равенства (=) и строятся от него.

Сложение, вычитание, умножение и деление

Чтобы сложить числа в двух ячейках вместе, сначала нажмите на целевую ячейку, где вы хотите, чтобы сумма отображалась. Затем введите = в ячейку, чтобы запустить формулу.

Шимон Брэтуэйт / IDG

Запуск формулы в Excel.

Затем щелкните ячейку, содержащую первое число, которое вы хотите добавить, и ссылка на эту ячейку (например, A2) появится рядом со знаком равенства в формуле.

Шимон Брэтуэйт / IDG

При выборе ячейки при построении формулы в формуле появляется ссылка на эту ячейку.

Введите + рядом с первой ссылкой на ячейку. Затем щелкните ячейку, содержащую второе число, которое вы хотите добавить, и ссылка на эту ячейку (например, A3) появится рядом со знаком +. Полный синтаксис формулы для сложения значений в ячейках A2 и A3:

=A2+A3

Шимон Брэтуэйт / IDG

Полная формула сложения отображается как в целевой ячейке, так и в строке формул выше.

Обратите внимание, что формула отображается не только в целевой ячейке, но и в строке формул непосредственно над рабочим листом. После того, как вы вставили начальный знак = в целевую ячейку, вы можете ввести формулу в строке формул. Иногда проще увидеть всю формулу и работать с ней в строке формул, чем на странице рабочего листа.

Если вы хотите добавить дополнительные числа к сумме, вы должны ввести еще один знак +, выбрать другую ячейку и так далее. Когда ваша формула будет завершена, нажмите Введите , и результат появится в целевой ячейке.

Шимон Брэтуэйт / IDG

Нажмите Введите , и результат появится в целевой ячейке.

Вычитание, умножение и деление выполняются одинаково. Вы просто меняете оператор на — символ, сообщающий Excel, какую математическую операцию вы хотите выполнить — с 9 наЗнак 0515 + к знаку – для вычитания, знак * для умножения или знак / для деления.

Шимон Брэтуэйт / IDG

Вычитание, умножение и деление. Формула для каждого показана в строке формул; результат в целевой ячейке.

Сложение чисел с помощью функции СУММ

Есть более быстрый способ сложить группу чисел. Здесь на помощь приходит встроенная в Excel функция СУММ.

Сначала щелкните целевую ячейку, в которой вы хотите отобразить итог. Затем введите =СУММ , чтобы запустить функцию. Появится список вариантов. Нажмите на первый вариант, SUM . Теперь вы увидите =СУММ( в целевой ячейке.

Шимон Брэтуэйт / IDG

Запуск функции СУММ.

Прямо под ячейкой с функцией СУММ находится всплывающая подсказка, показывающая синтаксис СУММ:

=СУММ(число 1, [число2],…)

Чтобы объединить отдельные ячейки, выберите ячейку, введите запятую, выберите другую ячейку и т. д. (Кроме того, вы можете ввести ссылку на ячейку, ввести запятую, ввести другую ссылку на ячейку и т. д.)

Если вы хотите добавить последовательные ячейки (например, в строке или столбце), выберите первую ячейку, затем удерживайте нажмите клавишу Shift и выберите последнюю ячейку в группе. (Или вы можете ввести ссылки на ячейки для первой и последней ячеек, разделенные двоеточием — например, A2:A7 выбирает A2, A7 и все ячейки между ними. )

Шимон Брэтуэйт / IDG

Выберите диапазон ячеек, которые вы хотите сложить.

Когда все ячейки, которые вы хотите сложить, выбраны, нажмите Enter .

Теперь вы должны увидеть окончательный результат, представляющий собой сумму выделенных вами чисел. Если вы снова выделите эту целевую ячейку, вы увидите полную формулу в строке формул — в нашем примере это:

=СУММ(A2:A7)

Шимон Брэтуэйт / IDG

Функция СУММ отображается в строке формул; результат появится в целевой ячейке.

Для всех формул Excel важно отметить, что они производят относительных значений. Это просто означает, что если какое-либо из значений в выбранных ячейках изменится, окончательное число изменится, чтобы отразить это.

Шимон Брэтуэйт / IDG

Если значение ячейки, используемой в формуле, изменяется, результат также изменяется.

Если вы хотите сделать его абсолютным значением , числом, которое не изменится, даже если ячейки, которые использовались для его вычисления, изменятся, тогда вам нужно щелкнуть правой кнопкой мыши ячейку и выбрать Скопируйте из всплывающего меню. Затем снова щелкните ячейку правой кнопкой мыши и в разделе «Параметры вставки» выберите кнопку Values ​​ (значок буфера обмена с цифрой 123).

Шимон Брэтуэйт / IDG

Скопируйте и вставьте значение в ячейку, чтобы предотвратить изменение значения при изменении одного из значений исходной ячейки.

Теперь, когда вы выберете эту ячейку, вы увидите простое число, а не формулу, в строке формул.

Шимон Брэтуэйт / IDG

Теперь ячейка содержит абсолютное значение, а не формулу.

Совет: В определенных обстоятельствах Excel предоставляет ярлык SUM. Если у вас есть ряд чисел в строке или столбце, Excel предполагает, что вы хотите сложить их вместе. Поэтому, если вы поместите курсор в ячейку справа от ряда чисел и нажмете кнопку AutoSum (Σ) ближе к правому концу вкладки «Главная» ленты, Excel автоматически выберет числа в строке, а затем сложит их вместе. когда вы нажимаете Введите . Аналогичным образом, если вы поместите курсор в ячейку под столбцом чисел, щелкните AutoSum и нажмите Enter , Excel суммирует числа в столбце.

Шимон Брэтуэйт / IDG

AutoSum — это ярлык для добавления строки или столбца чисел. (Щелкните изображение, чтобы увеличить его.)

Вычисление среднего значения с помощью функции СРЗНАЧ

Чтобы вычислить среднее значение группы чисел, повторите те же шаги, но с использованием синтаксиса =СРЕДНЕЕ и выделение ячеек, содержащих числа, которые вы хотите использовать в расчете.

Шимон Брэтуэйт / IDG

Чтобы быстро вычислить среднее значение группы чисел, используйте функцию СРЗНАЧ.

Совет: Как и в случае с СУММ, есть ярлык для использования функции СРЗНАЧ, если у вас есть ряд чисел в строке или столбце. Поместите курсор в ячейку справа от ряда чисел или в ячейку под столбцом чисел. Щелкните маленькую стрелку вниз справа от AutoSum , выберите Average из появившегося меню и нажмите Enter . Excel вычисляет среднее значение значений в этой строке или столбце.

Шимон Брэтуэйт / IDG

Для функции СРЗНАЧ также имеется ярлык автосуммы.

Дополнительные сведения, примеры и рекомендации по использованию этих функций см. на страницах поддержки функций SUM и AVERAGE от Microsoft.

2. Функция ЕСЛИ

Эта функция помогает автоматизировать процесс принятия решений, применяя к вашим данным логику «если-то». Используя эту функцию, вы можете заставить Excel выполнить расчет или отобразить определенное значение в зависимости от результата логического теста. Например, вы можете создать тест, который проверяет, является ли значение ячейки больше или равным значению 18, и ввести «Да» или «Нет» соответственно.

Пока мы изучаем эту функцию, мы рассмотрим еще один способ ввода функций в Excel: с помощью вкладки «Формулы» на ленте. Здесь вы найдете кнопки, обеспечивающие быстрый доступ к функциям по категориям: Автосумма, Финансы, Логика, Текст, Дата и время и так далее. Возможность просматривать функции по категориям может быть полезна, если вы не можете вспомнить точное название функции или не знаете, как его правильно написать.

Чтобы ввести функцию ЕСЛИ, выберите целевую ячейку и на вкладке «Формулы» щелкните значок Логическая кнопка , затем выберите IF из появившегося списка функций.

Кроме того, вы можете нажать кнопку Вставить функцию полностью слева от вкладки Формулы. Появится панель «Вставить функцию», в которой показан список часто используемых функций.

Шимон Брэтуэйт / IDG

Вместо ввода = для запуска функции можно перейти на вкладку Формулы и выбрать Вставить функцию . (Щелкните изображение, чтобы увеличить его.)

Выберите IF из списка и нажмите OK . (Если нужной функции нет в списке «Часто используемые», выберите другую категорию или Все , чтобы увидеть все доступные функции.)

Появится панель аргументов функции, и вы увидите =IF() в целевой ячейке.

Шимон Брэтуэйт / IDG

Вы можете использовать панель аргументов функции для построения функции. (Щелкните изображение, чтобы увеличить его.)

Синтаксис функции ЕСЛИ следующий:

=ЕСЛИ(логическая_проверка,»значение_если_истина»,»значение_если_ложь»)

Вы заметите, что панель Аргументы функции для функции ЕСЛИ имеет поля для Логическая_проверка, Значение_если_истина и Значение_если_ложь. В нашем примере «больше или равно 18» логическая проверка заключается в том, является ли число в выбранной ячейке больше или равным 18, значением, если оно истинно, является «Да», а значением, если ложь, является «Нет». Таким образом, мы должны ввести следующие элементы в поля панели следующим образом:

Logical_test: B2>=18

Значение_если_истина: «Да»

Значение_если_ложь: «Нет»

или просто введите полную формулу в целевую ячейку:

9000 2 =ЕСЛИ(B2>=18,»Да «,»Нет»)

Это сообщает Excel, что если значение ячейки B2 больше или равно 18, в целевой ячейке должно быть указано «Да».