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

Формулы в эксель шпаргалка

Содержание статьи (кликните для открытия/закрытия)

1. Как поставить плюс, равно в Excel без формулы
2. Почему в экселе формула не считает
3. Неверный формат ячеек или неправильные настройки диапазонов ячеек
4. Ошибки в формулах
5. Включен режим показа формул
6. Отключен автоматический расчет по формулам
7. Формула сложения в Excel
8. Формула округления в Excel до целого числа
9. Как считать проценты от числа
10. Шпаргалка с формулами Excel

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

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

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

Пример использования знаков «умножение» и «равно»

Почему в экселе формула не считает

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

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

В Excel возникают различные ошибки с хештегом (#), такие как #ЗНАЧ!, #ССЫЛКА!, #ЧИСЛО!, #Н/Д, #ДЕЛ/0!, #ИМЯ? и #ПУСТО!. Они указывают на то, что что-то в формуле работает неправильно. Причин может быть несколько.

Вместо результата выдается #ЗНАЧ! (в версии 2010) или отображается формула в текстовом формате (в версии 2016).

Примеры ошибок в формулах

В данном примере видно, что перемножается содержимое ячеек с разным типом данных =C4*D4.

Исправление ошибки: указание правильного адреса =C4*E4 и копирование формулы на весь диапазон.

• Ошибка #ССЫЛКА! возникает, когда формула ссылается на ячейки, которые были удалены или заменены другими данными.
• Ошибка #ЧИСЛО! возникает тогда, когда формула или функция содержит недопустимое числовое значение.
• Ошибка #Н/Д обычно означает, что формула не находит запрашиваемое значение.
• Ошибка #ДЕЛ/0! возникает, когда число делится на ноль (0).
• Ошибка #ИМЯ? возникает из-за опечатки в имени формулы,  то есть формула содержит ссылку на имя, которое не определено в Excel.
• Ошибка #ПУСТО! возникает, если задано пересечение двух областей, которые в действительности не пересекаются или использован неправильный разделитель между ссылками при указании диапазона.

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

Ошибки в формулах

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

Исправление: Выделите ячейку или диапазон ячеек. Нажмите знак «Ошибка» (смотри рисунок) и выберите нужное действие.

Пример исправления ошибок в Excel

Включен режим показа формул

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

Отключен автоматический расчет по формулам

Такое возможно в файлах с большим объемом вычислений. Для того чтобы слабый компьютер не тормозил, автор файла может отключить автоматический расчет в свойствах файла.

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

Формула сложения в Excel

Выполнить сложение в электронных таблицах достаточно просто. Нужно написать формулу, в которой будут указаны все ячейки, содержащие данные для сложения. Конечно же, между адресами ячеек ставим плюс. Например, =C6+C7+C8+C9+C10+C11.

Пример вычисления суммы в Excel

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

Пример использования функции Автосумма

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

Формула округления в Excel до целого числа

Начинающие пользователи используют форматирование, с помощью которого некоторые пытаются округлить число. Однако, это никак не влияет на содержимое ячейки, о чем и указывается во всплывающей подсказке. При нажатии на кнопочку (см. рисунок) произойдет изменение формата числа, то есть изменение его видимой части, а содержимое ячейки останется неизменным. Это видно в строке формул.

Уменьшение разрядности не округляет число

Для округления числа по математическим правилам необходимо использовать встроенную функцию =ОКРУГЛ(число;число_разрядов).

Математическое округление числа с помощью встроенной функции

Написать её можно вручную или воспользоваться мастером функций на вкладке Формулы в группе Математические (смотрите рисунок).

Мастер функций Excel

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

Как считать проценты от числа

Для подсчета процентов в электронной таблице выберите ячейку для ввода расчетной формулы. Поставьте знак «равно», затем напишите адрес ячейки (используйте английскую раскладку), в которой находится число, процент от которого будете вычислять. Можно просто кликнуть мышкой в эту ячейку и адрес вставится автоматически. Далее ставим знак умножения и вводим число процентов, которое необходимо вычислить. Посмотрите на пример вычисления скидки при покупке товара.
Формула =C4*(1-D4)

Вычисление стоимости товара с учетом скидки

В C4 записана цена пылесоса, а в D4 – скидка в %. Необходимо вычислить стоимость товара с вычетом скидки, для этого в нашей формуле используется конструкция (1-D4). Здесь вычисляется значение процента, на которое умножается цена товара. Для Excel запись вида 15% означает число 0.15, поэтому оно вычитается из единицы. В итоге получаем остаточную стоимость товара в 85% от первоначальной.

Вот таким нехитрым способом с помощью электронных таблиц можно быстро вычислить проценты от любого числа.

Шпаргалка с формулами Excel

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

Ваша ссылка для скачивания шпаргалки с яндекс диска

Microsoft excel. Работа с формулами

Похожие презентации:

3D печать и 3D принтер

Системы менеджмента качества требования. Развитие стандарта ISO 9001

Операционная система. Назначение и основные функции

Adobe Photoshop

AutoCAD история и возможности

Microsoft Excel

Облачные технологии

Корпорация Microsoft и ее особенности

Веб-дизайн

Тема 2. Пакеты прикладных программ

1. РАБОТА С ФОРМУЛАМИ

MICROSOFT EXCEL
РАБОТА С ФОРМУЛАМИ

2. ОПРЕДЕЛЕНИЕ ФОРМУЛЫ

Формула – это выражение,
которое состоит из
последовательности операндов,
разделенных операторами
Пример формулы:
=F7*С14+B12

3. СОСТАВ ФОРМУЛЫ

Формула может состоять из математических
операторов, значений (констант), ссылок
на ячейку и имен функций
=15+2
=15+2*0,2-10
=А1:А6
=А1:А6+СУММ(А1;А5)
=15/А1
=15/А1
=А1*12
=А15*3+2*6

4. РЕЗУЛЬТАТ ВЫПОЛНЕНИЯ ФОРМУЛЫ

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

5. ОПЕРАНДЫ

6. КОНСТАНТЫ

Константы – текстовые или числовые
значения, которые вводятся в ячейку и не
могут изменяться во время вычислений
=5+2
=12*2
=15*8/3
=5+5+10

7. ССЫЛКИ НА ЯЧЕЙКИ

Ссылка на ячейку или группу ячеек – способ, которым
указывается конкретная ячейка или несколько ячеек.
Ссылка на отдельную ячейку – ее координаты.
Значение пустой ячейки равно нулю
Ссылки на ячейки бывают двух типов:
относительные – ячейки обозначаются
относительным смещением от ячейки с формулой
(например: F7).
абсолютные – ячейки обозначаются координатами
ячеек в сочетании со знаком $ (например: $F$7).
Комбинация предыдущих типов (например: F$7).
При копировании формул относительные ссылки
изменяются на размер перемещения

8. ОПЕРАТОРЫ

9. ВИДЫ ОПЕРАТОРОВ

Операторами обозначаются операции, которые
следует выполнить над операндами формулы
В Microsoft Excel включено четыре вида операторов:
арифметические
текстовые
операторы сравнения
операторы ссылок

10. АРИФМЕТИЧЕСКИЕ ОПЕРАТОРЫ

Служат для выполнения арифметических
операций, таких как сложение, вычитание,
умножение
Операции выполняются над числами

11. АРИФМЕТИЧЕСКИЕ ОПЕРАТОРЫ

Используются следующие арифметические операторы:
Арифметический оператор
Значение (пример)
+ (знак плюс)
Сложение (3+3)
– (знак минус)
Вычитание (3–1)
Отрицание (–1)
* (звездочка)
Умножение (3*3)
/ (косая черта)
Деление (3/3)
% (знак процента)
Процент (20%)
^ (крышка)
Возведение в степень (3^2)

12.

ОПЕРАТОРЫ СРАВНЕНИЯИспользуются для сравнения двух значений.
Результатом сравнения является логическое
значение: либо ИСТИНА, либо ЛОЖЬ.
Оператор сравнения
Значение (пример)
= (знак равенства)
Равно (A1=B1)
> (знак больше)
Больше (A1>B1)
< (знак меньше)
Меньше (A1<B1)
>= (знак больше или равно)
Больше или равно (A1>=B1)
<= (знак меньше или равно)
Меньше или равно (A1<=B1)
<> (знак не равно)
Не равно (A1<>B1)

13. ОПЕРАТОРЫ ССЫЛКИ

Для описания ссылок на диапазоны ячеек используются
следующие операторы
Оператор ссылки
Значение (пример)
: (двоеточие)
Ставится между ссылками на первую и последнюю
ячейки диапазона.
Такое сочетание является ссылкой на диапазон
(B5:B15)
; (точка с запятой)
Оператор объединения. Объединяет несколько
ссылок в одну ссылку (СУММ(B5:B15;D5:D15))
(пробел)
Оператор пересечения множеств, служит для ссылки
на общие ячейки двух диапазонов (B7:D7 C6:C8)

14.

ОПЕРАТОР КОНКАТЕНАЦИИАмперсанд (&) используется для объединения
нескольких текстовых строк в одну строку
Текстовый
оператор
& (амперсанд)
Значение (пример)
Объединение последовательностей знаков в одну
последовательность («Северный»&»ветер»)
Амперсанд (&) является текстовым оператором

15. ПОРЯДОК ВЫЧИСЛЕНИЙ

Формула в Microsoft Excel всегда
начинается со знака равенства (=)
Знак равенства свидетельствует о
том, что последующие знаки
составляют формулу
Элементы, следующие за знаком
равенства, являются операндами,
разделяемыми операторами
вычислений

16. ПОРЯДОК ВЫЧИСЛЕНИЙ

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

17. ПРИОРИТЕТ ОПЕРАТОРА

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

18.

Возведение в степень
*и/
Умножение и деление
+и–
Сложение и вычитание
&
Объединение двух текстовых строк в одну
= < > <= >= <>
Сравнение

19. СООБЩЕНИЯ ОБ ОШИБКАХ

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

20. ЗНАЧЕНИЕ СООБЩЕНИЙ ОБ ОШИБКЕ

#### – ширина ячейки не позволяет отобразить число в
заданном формате;
#ИМЯ? – Microsoft Excel не смог распознать имя,
использованное в формуле;
#ДЕЛ/0! – в формуле делается попытка деления на нуль;
#ЧИСЛО! – нарушены правила задания операторов,
принятые в математике;
#Н/Д – такое сообщение может появиться, если в качестве
аргумента задана ссылка на пустую ячейку;
#ПУСТО! – неверно указано пересечение двух областей,
которые не имеют общих ячеек;
#ССЫЛКА! – в формуле задана ссылка на несуществующую
ячейку;
#ЗНАЧ! – использован недопустимый тип аргумента.

21. ВОПРОСЫ

Что такое формула?
Из чего может состоять формула?
Что является результатом выполнения формулы?
Что может использоваться в качестве операнда в
формуле?
Что может быть представлено в качестве оператора в
формуле?
Перечислите виды операторов?
Какой порядок вычисления формул?
Что такое приоритет оператора?
Перечислите приоритет оператора в порядке
возрастания?
Если формула не может быть правильно вычислена, что
появится в итоговой ячейке?
Какие виды ошибок вам известны?
Что означает ошибка #ИМЯ??
Значение ошибки #ССЫЛКА!?
Значение ошибки ####?

English     Русский Правила

Формулы и функции в Excel (простой учебник)

Ввод формулы | Редактировать формулу | Приоритет оператора | Копировать/вставить формулу | Функция вставки

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

Например, ячейка A3 ниже содержит формулу, которая суммирует значение ячейки A2 со значением ячейки A1.

Например, ячейка A3 ниже содержит функцию СУММ, которая вычисляет сумму диапазона A1:A2.

Введите формулу

Чтобы ввести формулу, выполните следующие шаги.

1. Выберите ячейку.

2. Чтобы Excel знал, что вы хотите ввести формулу, введите знак равенства (=).

3. Например, введите формулу A1+A2.

Совет: вместо ввода A1 и A2 просто выберите ячейку A1 и ячейку A2.

4. Измените значение ячейки A1 на 3.

Excel автоматически пересчитает значение ячейки A3. Это одна из самых мощных функций Excel!

Редактирование формулы

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

1. Чтобы изменить формулу, щелкните в строке формул и измените формулу.

2. Нажмите Enter.

Приоритет оператора

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

Сначала Excel выполняет умножение (A1 * A2). Затем Excel добавляет к этому результату значение ячейки A3.

Другой пример:

Сначала Excel вычисляет часть в скобках (A2+A3). Затем он умножает этот результат на значение ячейки A1.

Копирование/вставка формулы

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

1. Введите приведенную ниже формулу в ячейку A4.

2а. Выберите ячейку A4, щелкните правой кнопкой мыши и выберите «Копировать» (или нажмите CTRL + c)…

… затем выберите ячейку B4, щелкните правой кнопкой мыши и выберите «Вставить» в разделе «Параметры вставки» (или нажмите CTRL + v).

2б. Вы также можете перетащить формулу в ячейку B4. Выберите ячейку A4, щелкните в правом нижнем углу ячейки A4 и перетащите ее в ячейку B4. Это намного проще и дает точно такой же результат!

Результат. Формула в ячейке B4 ссылается на значения в столбце B.

Вставить функцию

Все функции имеют одинаковую структуру. Например, СУММ(A1:A4). Имя этой функции SUM. Часть в скобках (аргументы) означает, что мы даем Excel диапазон A1: A4 в качестве входных данных. Эта функция складывает значения в ячейках A1, A2, A3 и A4. Нелегко запомнить, какую функцию и какие аргументы использовать для каждой задачи. К счастью, функция «Вставить функцию» в Excel поможет вам в этом.

Чтобы вставить функцию, выполните следующие шаги.

1. Выберите ячейку.

2. Нажмите кнопку «Вставить функцию».

Появится диалоговое окно «Вставить функцию».

3. Найдите функцию или выберите функцию из категории. Например, выберите СЧЁТЕСЛИ из категории Статистические.

4. Нажмите OK.

Появится диалоговое окно «Аргументы функции».

5. Щелкните в поле Диапазон и выберите диапазон A1:C2.

6. Щелкните в поле Критерии и введите >5.

7. Нажмите OK.

Результат. Функция СЧЁТЕСЛИ подсчитывает количество ячеек, превышающих 5.

Примечание. Вместо использования функции вставки просто введите =СЧЁТЕСЛИ(A1:C2,»>5″). Когда вы дойдете до: =COUNTIF( вместо ввода A1:C2, просто выберите диапазон A1:C2.

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

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

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

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

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

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

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

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

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

В Excel существует более 400 функций, и их число увеличивается от версии к версии. Конечно, запомнить их все практически невозможно, да это и не нужно. Мастер функций поможет вам найти функцию, наиболее подходящую для конкретной задачи, а Формула Excel 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 на вкладке Главная в группе Форматы . Excel автоматически вставит формулу СУММ.

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

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

СРЗНАЧ

Функция Excel СРЗНАЧ делает именно то, что следует из ее названия, т. е. находит среднее или среднее арифметическое чисел. Его синтаксис похож на 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

МАКС И МИН

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

=MAX(B2:B6)

=MIN(B2:B6)

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

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

COUNT & COUNTA

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

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

В то время как функция СЧЁТ работает только с теми ячейками, которые содержат числа, функция СЧЕТЧИК подсчитывает все ячейки, которые не являются . пустой , независимо от того, содержат ли они числа, даты, время, текст, логические значения ИСТИНА и ЛОЖЬ, ошибки или пустые текстовые строки («»):

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

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

=СЧЕТ(B:B)

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

=COUNTA(B:B)

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

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

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

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

IF

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

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

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

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

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

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

TRIM

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

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

TRIM(текст)

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

=TRIM(A1)

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

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

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

ДЛСТР

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

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

Хотите узнать, сколько символов в ячейке 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 — различные способы ввода текущей даты и времени в Excel: как неизменяемая отметка времени или автоматически обновляемая дата и время.
• Функции даты Excel с примерами формул — формулы для преобразования даты в текст и наоборот, извлечения дня, месяца или года из даты, вычисления разницы между двумя датами и многого другого.

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

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

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

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

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

=IF(B2="pass", 1, 0)

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

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

=IF(B2="pass", "1", "0")

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

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

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

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

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

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

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

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

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

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

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

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

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

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