Разное

Эксель работа с формулами: Функции Excel (по категориям) — Служба поддержки Майкрософт

Содержание

Формулы и функции Excel | Blog ManyProg

Зачем нужны формулы для работы в Excel? Приложение Microsoft Excel – не просто программа для создания электронных таблиц. На самом деле это мощный инструмент для работы с табличными данными. Важнейшим преимуществом MS Excel являются встроенные функции, а также возможность создавать формулы для расчета. Эти формулы могут быть как простейшими, так и очень сложными.
Как создать формулу в Excel
Использование функций в Excel
Финансовые функции Excel
Функции даты в Excel
Математические функции Excel
Статистические функции Excel
Работа с массивами в Excel
Функции баз данных Excel
Текстовые функции Excel
Логические функции Excel

Как создать формулу в Excel

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

MS Excel позволяет выполнять различные операции над данными таблиц двумя способами:

  1. Создание формул вручную.
  2. Использование встроенных функций Excel.

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

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


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

    1. Установите курсор в ячейку D2.
    2. В строке формул напишите =B2*C2 и нажмите Enter.

В ячейке D2 появится результат умножения содержимого ячеек B2 и C2.
Если вы скопируете ячейку D2, скопируется не содержимое ячейки (число 48), а формула.

Причем эта формула будет автоматически меняться в зависимости от номеров ячеек. Например, если ячейку D2 скопировать в D3, то номера ячеек в формуле увеличатся на 1, то есть формула в ячейке D3 примет вид =B3*C3.

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

Если необходимо, чтобы копировалась не формула, а содержимое ячейки, следует немного изменить формулу, поставив знак $ к ее элементам. Например, для ячейки D2 эта формула примет вид =$B$2*$C$2.


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

Можно также сделать неизменной лишь часть элементов формулы. Например, перемножим количество товара в каждой ячейке на цену товара с кодом 111111. Для ячейки D2 формула пример вид =B2*$C$2. Если эту формулу скопировать, а потом вставить в другую ячейку, второй множитель останется неизменным. То есть, в данном примере количество любого товара будет умножаться на 12.

В этих примерах мы рассмотрели лишь простейшие формулы. На самом деле, они могут быть с большим количеством элементов, математических знаков, скобок. Важно помнить приоритет выполнения расчетов внутри формулы. Сначала всегда выполняется действие в скобках. Затем рассчитывается возведение в степень, умножение и деление. И только после этого сложение и вычитание. Если вы сомневаетесь в том, что правильно определили последовательность расчетов в формуле Excel, заключите нужные элементы в скобки. Даже если их можно опустить по правилам математики.

Иногда пользователи забывают, что обязательным условием при перемножении элементов формулы является постановка знака умножить *, потому что правила математики разрешают опускать знак умножения. Например, формула «(a+b)c» по правилам математики будет считаться корректно написанной, в то время как Excel выдаст ошибку. Формула в этом случае должна иметь вид =(A2+B2)*C2.

Использование функций в Excel

Функции в Excel – еще один важный элемент этого приложения. По своей сути функция – это краткая запись часто используемых формул. Например, вместо того, чтобы писать формулу =B2+B3+B4+B5+B6, можно использовать функцию =СУММ(B2:B6). Функций существует очень много, они разделены по категориям. При помощи них можно выполнять не только математические вычисления, но и производить статистические, финансовые, инженерные расчеты и многое другое.

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

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

  1. Щелкните мышкой по ячейке, в которой будет указана итоговая сумма.
  2. Нажмите на значок вызова функций.
  3. Выберите нужную функцию из списка категорий и нажмите кнопку ОК. В данном случае это будет функция СУММ. К каждой функции есть краткое описание того, что она делает.
  4. Выберите аргументы функции. В данном случае это будет диапазон ячеек с D2 по D8.
  5. После нажатия кнопки «ОК» в итоговой ячейке появится результат расчета общей суммы.

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

Финансовые функции Excel

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

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

В появившемся списке финансовых функций следует выбрать нужную функцию и нажать на кнопку «ОК».

Далее в новом окне следует указать аргументы функции и нажать «ОК».

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

  1. Выберите мышкой ячейку, в которой будет находиться значение этой функции. Найдите в списке финансовых функций ДОХОД и выберите ее.
  2. В окне аргументов щелкните мышкой в поле первого аргумента, в данном случае это будет аргумент «Дата_согл», затем щелкните мышкой по ячейке со значением его. В поле аргумента появится значение этой ячейки. Аналогичным образом заполните остальные поля.
  3. После нажатия кнопки «ОК» в ячейке «Доход» появится рассчитанное значение данной функции.

Наиболее часто используемые финансовые функции:

  • БС. Находит будущую стоимость инвестиций.
  • ВСД. Внутренняя ставка доходности.
  • МВСД. Модифицированная внутренняя ставка доходности.
  • ПРПЛТ. Процентные платежи за указанный период.
  • ПС. Приведенная стоимость инвестиций.
  • СТАВКА. Вычисляет ставку процентов по аннуитету.
  • ЭФФЕКТ. Фактическая процентная ставка.

Функции даты в Excel

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

Имеется таблица со столбцами «Год», «Месяц», «День». Требуется добавить столбец с датой в формате дд.мм.гггг.

  1. Щелкните мышкой по ячейке Дата и вызовите мастер функций. Выберите категорию «Дата и время», а в списке функций выберите «Дата».
  2. В окне аргументов введите адреса ячеек «Год», «Месяц», «День». И нажмите ОК.
  3. В ячейке «Дата» появится дата в нужном формате. Для того, чтобы дата появилась в остальных ячейках столбца, достаточно скопировать эту ячейку, потянув мышкой за правый нижний угол.

Вот еще несколько часто используемых функций из этого раздела.

  • ДЕНЬ. Возвращает день из указанной даты.
  • ДАТАМЕС. Подсчитывает дату через указанное количество месяцев. Если указать отрицательное значение аргумента «Месяц», то функция подсчитает дату, предшествующую указанной.
  • КОНМЕСЯЦА. Показывает дату последнего дня указанного месяца
  • ЧАС. Преобразует указанное число в часы или выделяет из выбранной даты только часы.
  • ЧИСТРАБДНИ. Подсчитывает количество рабочих дней между двумя указанными датами.

Математические функции Excel

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

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

Выберите функцию СУММ в данной категории и нажмите «ОК».

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

Часто используемые математические функции следующие:

  • СУММЕСЛИ. Подсчитывает сумму в указанных ячейках, но с учетом указанных условий.
  • ОКРУГЛ. Показывает округленную величину числа с точностью до указанного количества знаков. Применяется только к отдельным ячейкам, а не к диапазону ячеек.
  • ПРОИЗВЕД. Подсчитывает произведение отдельных чисел или содержимого указанных ячеек.
  • СЛУЧМЕЖДУ. Показывает случайное число в диапазоне между указанными значениями.
  • РИМСКОЕ. Преобразует арабские числа в указанных ячейках в римские.

Статистические функции Excel

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

  • СРЗНАЧ. Функция предназначена для вычисления среднего значения в диапазонах ячеек. Причем, диапазонов может быть несколько. В расчетах могут использоваться и несмежные диапазоны и ячейки.
  • СРЗНАЧЕСЛИ. Функция похожа на предыдущую, но значения для расчета среднего выбираются по указанному условию.
  • МАКС. Находит максимальное значение в указанном диапазоне ячеек.
  • НАИБОЛЬШИЙ. Находит указанное по величине значение из диапазона ячеек. Например, можно найти второе по величине значение или десятое по величине значение из списка.
  • МОДА. Эта функция находит в указанном массиве данных максимально часто встречающееся значение.

Работа с массивами в Excel

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

Для примера разберем использование функции ТРАНСП. Она преобразует горизонтальный диапазон ячеек в вертикальный и наоборот. При этом первая строка массива становится первым столбцом нового массива и так далее. К примеру, имеется таблица со строками Январь, Февраль, Март и количеством дней в каждом месяце. Необходимо преобразовать ее так, чтобы названия месяцев шли по горизонтали.

  1. Выделите мышкой массив пустых ячеек с тремя столбцами и двумя строками.
  2. Откройте мастер функций, найдите там категорию «Ссылки и массивы» и выберите функцию «ТРАНСП». Нажмите «ОК».
  3. Выделите мышкой все элементы начального массива. Нажмите «ОК».
  4. Для того, чтобы функция начала действовать, щелкните мышкой в строке формул и нажмите сочетание клавиш CTRL+SHIFT+ENTER. Этим сочетанием клавиш должна заканчиваться любая функция для работы с массивами.
  5. Новая таблица будет иметь такой вид.

Функции баз данных Excel

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

  1. В таблице не должно быть пустых строк или столбцов. Нельзя использовать объединенные ячейки.
  2. Каждый столбец должен иметь заголовок. Заголовок не должен содержать пустых или объединенных ячеек.
  3. Данные в каждом столбце должны быть строго однотипны. То есть либо числовые, либо текстовые, либо дата и так далее. Числовые ячейки не могут быть пустыми. При отсутствии значения там ставится ноль.

Для работы с функциями баз данных подойдет, например, такая таблица:

Каждая из функций для работы с базами данных использует три аргумента:

  • База данных. Это комплекс связанных между собой ячеек, где строки – это записи таблицы базы данных, а столбцы – это поля.
  • Поле. Это столбец таблицы, который использует функция для расчета. Это может быть либо его название в кавычках (например, «Цена»), либо номер этого столбца по порядку (например, 3 для столбца «Цена»).
  • Критерий. Это интервал ячеек с заданными условиями. Он должен содержать по крайней мере одно название столбца и одну ячейку с условием под ним.

Примером функции баз данных является функция БСЧЁТ. При помощи нее можно подсчитать строки в таблице базы данных, которые удовлетворяют указанным условиям. Эту функцию удобно использовать при наличии сложных критериев на основе формул.

Текстовые функции Excel

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

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

  1. Зайдите в мастер функций и выберите категорию «Текстовые». Выберите функцию СТРОЧН и нажмите «ОК».
  2. В появившемся окне аргументов функции укажите ячейку, содержимое которой надо преобразовать, и нажмите «ОК».

Существует немало других функций для работы с текстовыми данными.

  • СЦЕПИТЬ. Объединяет содержимое нескольких указанных ячеек в одной ячейке.
  • ПРОПИСН. Делает все буквы в указанной ячейке прописными.
  • ПРОПНАЧ. Делает прописной только первую букву.
  • СОВПАД. Сравнивает содержимое двух текстовых строк с учетом регистра. Если значение совпадает, функции возвращает значение «ИСТИНА». В противном случае – «ЛОЖЬ».
  • СЖПРОБЕЛЫ. Удаляет все лишние пробелы, кроме единичных.

Логические функции Excel

Логические функции – одни из самых распространенных функций Excel. Зачастую их используют при выполнении финансовых расчетов. Логические функции проверяют истинность заданных условий и, если условия выполняются, функция выдает значение «Истина», а если не выполняются – «Ложь». Все они в своей работе используют знаки сравнения, такие как «=», «<», «>» и так далее.

Для того, чтобы использовать в работе логические функции, следует зайти в мастер функций и выбрать категорию «Логические».
Рассмотрим кратко некоторые из них.

  • ЕСЛИ. Эта функция возвращает один результат, если заданное условие истинно, и другой результат, если ложно.
  • ИСТИНА. Эта функция используется только в совокупности с другими функциями. Она возвращает лишь логическое выражение «Истина» и не имеет аргументов.
  • ЛОЖЬ. Аналогична функции ИСТИНА, только возвращает логическое выражение «Ложь».
  • НЕ. Меняет полученное логическое значение противоположным значением, заменяя «Истина» на «Ложь» и наоборот.

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

Category: Файлы Excel | Tags: Microsoft Excel, MS Excel, Логические функции, Математические функции, Работа с массивами, Статистические функции, Текстовые функции, Финансовые функции, Формулы в Exce, Функции Excel, Функции баз данных, Функции даты в Excel

Работа с Excel 2007: Формулы

Начало работы

Начните со знака равенства

Для расчёта в Excel используют формулы как обучающий пример рассмотрим следующее:

Два компакт-диска, купленные в феврале, стоили 12,99 и 16,99 грн. . Их суммарная стоимость равна расходам на компакт-диски в этом месяце.

Введите формулу в ячейку C6.
Нажмите клавишу ВВОД, чтобы отобразить результат вычислений по формуле.
Формула отображается в строке формул каждый раз при выборе ячейки C6.


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

Формулы в Microsoft Excel всегда начинаются со знака равенства (=). Чтобы сложить числа 12,99 и 16,99, введите в ячейку C6 формулу

=12,99+16,99

Знак сложения (+) — это математический оператор, обозначающий суммирование значений.

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

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

Для вычисления разности используйте знак «минус», для вычисления произведения — знак «звездочка» (*), для вычисления частного — знак «косая черта» (/). Обязательно начинайте формулу со знака равенства.

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

 

Суммирование значений по столбцу

 

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

 На вкладке Главная в группе Правка нажмите кнопку Сумма.

 Ячейки будут обведены цветовым индикатором, в ячейке B7 будет отображена формула.

 Нажмите клавишу ВВОД, чтобы отобразить результат вычислений по формуле.

 Щелкните в ячейке B7, чтобы отобразить формулу в строке формул.

 

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

Расходы за январь можно подсчитать, выбрав ячейку B7 и нажав кнопку Сумма в группе Правка на вкладке Главная. Таким образом будет введена функция «СУММ», складывающая значения по столбцу в интервале ячеек. Использование этой функции упрощает суммирование, если требуется сложить большое число значений, при этом нет необходимости набирать формулу.

По нажатии кнопки ВВОД в ячейке B7 будет отображен результат вычислений (95,94). Формула =CУММ(B3:B6) отображается в строке формул при каждом щелчке в ячейке B7.

Знаки «B3:B6» называются аргументом; аргументы сообщают функции «СУММ», какие значения требуется суммировать. Использование ссылки на ячейку (B3:B6) вместо самих значений в ячейках позволяет автоматически обновлять результат, если значение в ячейках изменится. Двоеточие (:) между B3 и B6 обозначает диапазон ячеек в столбце B, строки с третьей по шестую. Круглые скобки отделяют аргумент от функции.

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

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

 

Копирование формулы вместо создания новой

Иногда копирование формул существенно проще создания новых. 

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

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

 

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

В программе Excel используется тип ссылок A1, то есть строки указываются буквами, а столбцы — числами. Буквы и числа называются заголовками строк и столбцов. В таблице показано использование ссылок на ячейки с помощью буквы, соответствующей столбцу, и следующего за ней числа, соответствующего строке.


Типы ссылок

Относительные. Относительные ссылки в формуле автоматически изменяются при копировании в строке или столбце. Это сделало возможным копирование формулы, относящейся к январю, для вычисления февральских расходов в первом уроке. Как показывает приведенный здесь пример, при копировании формулы =C4*$D$9 из строки в строку относительная ссылка на ячейку изменяется с C4 на C5 и C6.
Абсолютные. Абсолютные ссылки на ячейки имеют постоянное значение. Абсолютные ссылки не изменяются при копировании формулы из ячейки в ячейку. Абсолютные ссылки обозначаются знаком доллара ($), например $D$9. На рисунке показано, что при копировании со строки на строку формулы =C4*$D$9 абсолютная ссылка на ячейку $D$9 не изменяется.

Смешанные. Смешанная ссылка является абсолютной по столбцу и относительной по строке или относительной по столбцу и абсолютной по строке. Например, ссылка $A1 является абсолютной по столбцу A и относительной по строке 1. При копировании смешанной ссылки из ячейки в ячейку ее относительная ссылка меняется, а абсолютная остается неизменной.

 

 Относительные ссылки на ячейки меняются от строки к строке.

 Абсолютная ссылка на ячейку всегда ссылается на ячейку D9.

 В ячейке D9 хранится значение скидки, равное 7 процентам.

 


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

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

Введите размер скидки 0,07 в свободную ячейку D9, в ячейку D4 введите формулу, начинающуюся с =C4*. Затем введите знак доллара ($) и букву D, чтобы указать абсолютную ссылку на столбец D, и $9, чтобы указать абсолютную ссылку на строку 9. Результатом вычислений по формуле будет произведение значений ячеек C4 и D9.

Затем с помощью маркера заполнения  скопируйте формулу из ячейки D4 в ячейку D5. При копировании формулы относительная ссылка изменится с C4 на C5, а абсолютная ссылка на размер скидки в ячейке D9 останется неизменной ($D$9).
 

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


Нахождение среднего значения

Функцию «СРЗНАЧ» предназначена для вычисления среднего значения.

Excel введет формулу за вас. Щелкните по ячейке в которой вы хотите видеть результат. На вкладке Главная в группе Правка щелкните стрелку на кнопке Сумма и выберите в списке значение Среднее. В строке формул  в верхней части листа отобразится формула =СРЗНАЧ(кординаты ячеек). после чего нажимаете Enter. 

Формулу также можно ввести непосредственно в ячейку.

Примечание. Кнопка Сумма также находится на вкладке Формулы в группе Библиотека функций.

 

 

Непонятные знаки на листе

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

#####. Столбец недостаточно широк для отображения содержимого ячейки. Увеличьте ширину столбца, уменьшите содержимое ячейки или используйте другой формат числа.

#ССЫЛКА! Ссылка на ячейку неверна. Ячейки могли быть удалены или вставлены поверх.

#ИМЯ? Опечатка в имени функции или использование имени, неизвестного программе Excel.


Использование других функций

 

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

Чтобы увидеть все другие функции, щелкните стрелку на кнопке Сумма в группе Правка на вкладке Главная и затем щелкните пункт Другие функции в списке. Откроется диалоговое окно Мастер функций — шаг 1 из 2, в котором можно искать функции. Это диалоговое окно является еще одним способом ввода формул в приложении Excel. Можно также отобразить другие функции, щелкнув вкладку Формулы.

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

10 причин, по которым формулы Excel не работают [и как их исправить]

«Боже мой» формулы Excel не работают в моем отчете.

Звучит знакомо, верно?

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

Просто подумай об этом.

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

[формулы «ЕСЛИ» с 64 условиями – шучу ]

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

По сути, здесь могут произойти две вещи:

Одна вы можете сказать что-то громко [ Я воздержусь от использования этого слова ].

Второй , вы приложите дополнительные усилия и исправьте все формулы.

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

И как их исправить.

Не работают формулы Excel: почему и как их исправить автоматическое преобразование имен генов в такие вещи, как календарные даты или случайные числа».

Первоначальное исследование, проведенное Journal Genome Biology, показало, что из года в год наблюдается тенденция к увеличению количества ошибок.

Вы можете скачать полные материалы исследования с веб-сайта SourceForge (с открытым исходным кодом) для дальнейшего чтения.

Вышеупомянутая исследовательская работа не может быть напрямую связана с формулами, не работающими в Excel, но здесь есть один момент:

Очень хорошо построенное и протестированное программное обеспечение, такое как MS Excel, может ошибаться с точки зрения понимания наших данных как насчет ты меня?

Итак, вот 10 основных причин, по которым формулы не работают.

#1. ВАРИАНТЫ РАСЧЕТА [НАИБОЛЕЕ ПРИЧИНА НЕОБНОВЛЕНИЯ ФОРМУЛ EXCEL]

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

По умолчанию для параметра Расчет будет установлено значение автоматический в Excel.

Но есть случаи, когда вы могли бы превратить это в ‘m anual ’, чтобы ускорить процесс.

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

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

Как решить эту проблему:

Перейдите в раздел «Формулы» → «Расчет» → нажмите «Рассчитать сейчас/Рассчитать лист».

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

Вот наше видео-руководство по вариантам расчета в Excel.

Еще несколько советов:

  • Заведите привычку нажимать Ctrl+S [это вызовет расчет]
  • Не используйте этот параметр для каждой книги, если только у вас нет большого файла с большим количеством формул
  • Этот параметр зависит от рабочей книги, поэтому его необходимо включать или выключать в каждой рабочей книге, которую вы хотите использовать

№2. ФОРМУЛА EXCEL НЕ РАСЧЕТАЕТ, ТОЛЬКО ПОКАЗЫВАЕТ ФОРМУЛУ [ПОКАЗАТЬ ФОРМУЛЫ]

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

Эта опция очень полезна, чтобы узнать все формулы на листе.

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

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

То же самое и с формулой СЛУЧМЕЖДУ.

Как решить эту проблему:

Это очень просто:

Перейдите к формулам → Под аудитом формул → Нажмите «Показать формулы» (при первом нажатии формулы отобразятся, а при втором нажатии они спрячутся за ячейками и начнут работать)

[Пожалуйста, обратитесь к скриншоту выше]

Также читайте: 51 лучший совет и хитрость Excel

Если вы извлекали данные из систем ERP в формате CSV (значения, разделенные запятыми), тогда ваши данные в основном подвергаются нежелательным пробелам.

Знаете ли вы, что лишние пробелы вызывают много проблем при использовании формулы ВПР?

Иногда бывает сложно идентифицировать эти пробелы.

Например, скажем, если вы хотите выполнить Vlookup, используя имя сотрудника в качестве основы, чтобы имя вашего сотрудника выглядело ниже

Теперь, если я возьму длину каждого имени, я увижу, что первая строка имеет 12, а у второго 13.

Хотя оба имени выглядят одинаково невооруженным глазом, во втором имени есть пробел.

Это не соответствует критериям точного соответствия в формуле ВПР, что приводит к ошибке #Н/Д .

Как решить эту проблему:

Проблему с лишними пробелами можно решить с помощью формулы Trim .
В общем, формула Trim обнаружит более одного пробела между словами и удалит.

Другие советы:

  • Всегда полезно использовать Обрезать формулу перед выполнением ВПР в обеих таблицах (таблица поиска и данных)
  • Функция обрезки игнорирует пробелы между буквами (работает только со словами)

№4. ИЗБАВЬТЕСЬ ОТ НЕПЕЧАТАЕМЫХ/СКРЫТЫХ СИМВОЛОВ

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

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

Как решить эту проблему:

Подобно обрезке, вы можете использовать формулу CLEAN для удаления этих символов.

Совет: Рекомендуется использовать обе формулы Trim& Clean вместе

#5. ФОРМАТИРОВАНИЕ EXCEL – НЕ ПЫТАЙТЕСЬ СРАВНИВАТЬ ЯБЛОКИ С АПЕЛЬСИНАМИ

Очень важно иметь правильное форматирование перед использованием таких формул, как Vlookup, Hlookup и Match& Index.

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

Хотя для невооруженного глаза они выглядят как числа, на самом деле это не так.

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

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

Это, в свою очередь, вызовет ошибку #Н/Д при добавлении формулы ВПР.

Хуже всего то, что его немного сложно идентифицировать.

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

Опять же, в этом случае ваша формула ВПР не будет работать.

Как решить эту проблему:

Убедитесь, что все ваши числа отформатированы как числа, а не как текст. Перед использованием формулы ВПР стоит переформатировать столбец поиска.

Еще несколько советов:

  • Как правило, числа всегда будут выравниваться по правому краю ячейки (но в нашем примере выше это не так)
  • Иногда в левом верхнем углу можно увидеть небольшой зеленый значок, указывающий на то, что с этой ячейкой что-то не так (см. второе изображение выше)
  • Просто нажмите на числовую ячейку -> Нажмите на раскрывающийся список от символа ошибки -> Нажмите «Преобразовать в число»

#6. ЦИКЛИЧЕСКИЕ ССЫЛКИ (ОШИБКИ ИЗ-ЗА БЕСКОНЕЧНОГО КРУГА)

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

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

Лично я много раз боролся с такими ошибками.

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

Итак, мы подсчитали сумму дифференциала в строке 13, и теперь нам просто нужно поместить их в строку 9.

Один простой способ — соединить ячейки строки 13 в строке 9.

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

[см. ниже]

В общем, должно работать.

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

Как решить эту проблему:

Во-первых, нам нужно идентифицировать ошибочные ячейки.

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

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

№7. НЕПРАВИЛЬНОЕ ИСПОЛЬЗОВАНИЕ ДВОЙНЫХ КАвычек – ЧЕЛОВЕЧЕСКАЯ ОШИБКА

Мы часто используем двойные кавычки при написании формул.

Но очень важно понимать, когда использовать, а когда нет.

Например, чтобы соединить два числа, мы можем использовать формулу, как показано ниже.

=1234&5678 = 12345678

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

=Джон&Смит: Неправильный способ использования [вы будете скорее всего см. ошибку #ИМЯ]

=”Джон”&”Смит” = Это правильный способ.

То же самое и с условными формулами, такими как ЕСЛИ, И, ИЛИ. Очень важно указывать текст в двойных кавычках.

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

#8. BODMAS — ОСНОВНОЕ ПРАВИЛО ДЛЯ КАЖДОГО РАСЧЕТА

Знаете ли вы, что каждый расчет в Excel основан на правиле «BODMAS»?

Ой, подождите. .. что такое правило БОДМАСА?

Это математическое правило для определения порядка операций.

Хорошо, давайте упростим дальше.

Можете ли вы угадать, что ответ на ниже?

10+10*2 =?

Это 40, поздравляю, вы ошиблись.

Можете ли вы попробовать тот же расчет в Excel?

Скорее всего, вы увидите 30, это действительно правильный ответ… но как?

В соответствии с правилом BODMAS расчет будет осуществляться на основе последовательности, т. е. → B-скобки, O-порядок, D-деление, M-умножение, A-сложение и S-вычитание.

Итак, последовательность вышеприведенной формулы: → 10*2 = 20 → 20+10 =30.

Ваши формулы могут ошибаться, если вы не учитываете БОДМАС.

Как решить эту проблему:

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

=(30+40)*2

Итак, все, что в скобках, будет сначала вычислено, а затем перейдет к следующей операции.

#9. НЕПРАВИЛЬНОЕ ИСПОЛЬЗОВАНИЕ «АБСОЛЮТНОЙ» ССЫЛКИ

Каждый, кто использует Excel, в основном знает, что такое абсолютная ссылка.

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

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

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

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

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

Но мы ошиблись.

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

Это действительно привело к неправильным результатам.

Таким образом, правильно было бы использовать абсолютную ссылку только для таких строк, как эта =СУММ(D$3:D$6).

Как решить эту проблему:

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

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

Пожалуйста, дайте мне знать ваши обходные пути на данный момент.

Другие советы:

  • Не используйте горячую клавишу F4 для частичной заморозки
  • Попробуйте вручную включить знак доллара ($) для частичного диапазона

№10. НЕПРАВИЛЬНЫЕ АРГУМЕНТЫ ФОРМУЛЫ

Неверные аргументы формулы могут отрицательно сказаться на результатах формулы.

Просто чтобы дать вам некоторое представление, давайте разберемся с приведенным ниже синтаксисом vlookup, , особенно с последним [просмотр диапазона].

Аргумент поиска диапазона имеет два варианта выбора.

  1. Верно – приблизительное совпадение
  2. Ложь — Точное совпадение

Лично я чувствую использование «Примерное совпадение» Вариант — это как покончить с собой.

Потому что Приблизительное совпадение никогда не даст точных результатов.

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

Если вы внимательно посмотрите, сумма дохода 25 000 для идентификатора клиента 6378993 неверна. Вместо этого должно было быть 65k.

Что же пошло не так?

Как я уже говорил, я использовал «Приблизительное совпадение» под аргумент поиска диапазона .

«Соответствие» — это еще одна формула, в которой у вас есть возможность выбрать похожие аргументы.

Как решить эту проблему:

Никогда не используйте « Приблизительное совпадение» в своих формулах ВПР, если вы не знаете, что делаете.

Теперь ваша очередь — проверьте, почему ваши формулы Excel не работают

Как вы думаете, у вас есть какие-то другие причины, по которым формулы Excel не работают?

Или, может быть, у вас есть вопрос.

В любом случае, пожалуйста, прокомментируйте ниже, я отвечу как можно скорее.

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

[Не волнуйтесь, мы ненавидим спам и уважаем вашу конфиденциальность] .

Как исправить формулы Excel, которые не вычисляют (обновить формулы)

Перейти к содержимому Как исправить формулы Excel, которые не вычисляют (обновить формулы)

Формулы — это жизнь и кровь Microsoft Excel.

Мы используем их для сложения чисел, вычитания дат и даже извлечения текстов.

При вводе формулы результат приходит практически сразу!

Но что произойдет, если это не так?

Очевидно, 2+2 = 4! Не 5!

Можем ли мы быть лучше в базовой математике, чем в Excel? Наверное, нет 🤣

Итак, как исправить формулу, которая не вычисляется автоматически?

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

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

СОДЕРЖАНИЕ