Формулы количества и суммы в Excel

СЧЁТ

Подсчитывает количество ячеек, содержащих числа.
Учитывает аргументы, являющиеся числами, датами или текстовым представлением чисел (например, число, заключенное в кавычки, такое как «1»).

Синтаксис: СЧЁТ(значение1;[значение2];…)
Пример формулы: =СЧЁТ(A1:A20)

СЧЁТЗ

Учитывает данные любого типа, включая значения ошибок и пустой текст («»). Например, если в диапазоне есть формула, которая возвращает пустую строку, функция СЧЁТЗ учитывает это значение. Функция СЧЁТЗ не учитывает пустые ячейки.

Синтаксис: СЧЁТЗ(значение1;[значение2];…)
Пример формулы: =СЧЁТЗ(A2:A7)

СЧИТАТЬПУСТОТЫ

Подсчитывает пустые ячейки в указанном выше диапазоне.

Синтаксис: СЧИТАТЬПУСТОТЫ (значение1;[значение2];…)
Пример формулы: =СЧИТАТЬПУСТОТЫ(A2:A7)

СЧЁТЕСЛИ

Подсчитывает количество ячеек, отвечающих определенному условию (например, число поставщиков из определенного города). Функция СЧЁТЕСЛИ не учитывает регистр символов.

Синтаксис: =СЧЁТЕСЛИ(где нужно искать; что нужно найти)
Пример формулы: =СЧЁТЕСЛИ(A2:A5;»Лондон»); =СЧЁТЕСЛИ(A2:A5;A4)

СЧЁТЕСЛИМН

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

Синтаксис: =СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)
Пример формулы: =СЧЁТЕСЛИМН(B2:B5,»=Да»,F2:F5,»>1″)

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

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

Excel COUNT функцияСамый большой COUNT Функция используется для подсчета количества ячеек, содержащих числа, или подсчета чисел в списке аргументов. Возьмите пример, COUNT(1,2,3,4,11,2,1,0) возвращает 8.

Как использовать функцию

Пример №1

Пример №2

Нам необходимо в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ или НЕ СДАЛ . Критерии у нас будут такими: кто набрал более 45 балов , тот считается сдавшим экзамен, ну а все остальные нет.

Формулы строятся аналогично задачке 1: =СУММЕСЛИМН(B2:B13;B2:B13;»>=»&D2;B2:B13;» Примечание : для удобства, строчки, участвующие в суммировании, выделены Условным форматированием с правилом =И($B2>=$D1500;$B2

12 наиболее распространённых проблем с Excel и способы их решения — Лайфхакер

• Логическое_условие – значение, которое будем проверять
• Значение_в_случае_ИСТИНА – действие, когда требование выполняется
• Значение в случае_ЛОЖЬ – действие, когда Логическое_условие не выполняется

Пример 1. В таблице содержатся данные о успеваемости студентов по некому предмету в институте. Найти итоговую оценку для студентов с фамилией, начинающейся на буковку «А» при условии, что малый балл должен быть не наименее 5 (успеваемость студентов оценивается по 10-бальной шкале).

Примеры

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

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

Именно этот текст будет выводиться в случае выполнения этого условия.

Данный текст мы увидим в случае ложного выполнения условия.

1. Для вставки нашей формулы нажимаем на кнопку «OK».

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

1. Введите любой текст в ячейку C.

1. После нажатия на кнопку Enter вы увидите следующий результат.

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

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

Excel функция суммеслимн — Учим Эксель

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

1. В конце нажмите на кнопку «OK».

1. В результате этого, мы видим сообщение о том, что введенное число больше 1000.

1. Удалите содержимое ячейки C. Результатом станет следующее.

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

Разнесение информации по разным листам книги «для удобства»

Часто пользователи добавляют важную информацию, которая может им понадобиться, в комментарий к ячейке. Имейте в виду, то, что находится в комментариях, вы можете только посмотреть (если найдёте). Вытащить это в ячейку затруднительно. Рекомендую лучше выделить отдельный столбец для комментариев.

Определённо не добавит вашей таблице ничего хорошего. Это выглядит отталкивающе для людей, которые пользуются вашими таблицами. В лучшем случае этому не придадут значения, в худшем — подумают, что вы не организованы и неряшливы в делах. Стремитесь к следующему:

1. Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».
2. Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
3. Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.

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

Функция счётеслимн в excel с несколькими условиями

Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ. Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.

Функция СУММЕСЛИМН() Сложение с несколькими аспектами в EXCEL (Часть 2.Условие И)

Произведем сложение значений находящихся в строчках, поля которых удовлетворяют сходу двум аспектам (Условие И). Разглядим Текстовые аспекты, Числовые и аспекты в формате Дат. Разберем функцию СУММЕСЛИМН( ) , британская версия SUMIFS().

В качестве начальной таблицы возьмем таблицу с 2-мя столбцами (полями): текстовым « Фрукты » и числовым « Количество на складе » (См. файл примера ).

Подсчитайте, начинаются ли ячейки или заканчиваются определенным текстом в Excel Предположим, у вас есть диапазон данных, и вы хотите подсчитать количество ячеек, которые начинаются с «kte» или заканчиваются «kte» на листе. Эта статья знакомит вас с некоторыми хитростями вместо ручного подсчета.Нажмите, чтобы узнать больше …

Относительные статьи:

• диапазон_суммирования – неотклонимый аргумент, принимающий ссылку на ячейки с числами, для которых будет вычислено суммарное значение с учетом установленных критериев;
• диапазон_условия1 – неотклонимый аргумент, принимающий ссылку на ячейки, в каких содержатся данные для проверки на соответствие данному аспекту;
• условие1 – неотклонимый аргумент, принимающий ссылку на ячейку с условием поиска, текстовую запись логического выражения (к примеру, «>=100» либо «0»), проверяемый текст (к примеру, «Продавец_1», «Товар_1») либо числовое значение (К примеру, 20), определяющие ячейки в спектре условия1, для которых будут отобраны и просуммированы данные из спектра суммирования.

Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

Оформление и примеры использования

Алгоритм написания логических формул в Эксель следующий:

ИСТИНА, ЛОЖЬ

В качестве примера приведем решение задачи с логическими операторами ИСТИНА и ЛОЖЬ. Они обычно не используются отдельно, а только в составе других операторов. Понять принцип работы можно на примере. В таблице телефонных номеров определяются платные и бесплатные вызовы.

После применения формулы «=ЕСЛИ(ЛЕВСИМВ(В3;4)=”8800”;ИСТИНА();ЛОЖЬ())», получается:

Сравнение происходит по первым четырем цифрам номера (оператор ЛЕВСИМВ(В3;4)). Если номер начинается с 8800, то звонок бесплатный, в противном случае — нет.

Отрицание — НЕ

Функция ссылается на ячейку или аргумент, где есть логический ответ, и меняет его на противоположный. Чаще всего применяется в составе формул. Пример:

Здесь оператор «=НЕ(F2)» инвертирует аргумент в столбце F.

Применение ЕСЛИ

«ЕСЛИ» всегда включает знаки сравнения и применяется в формулах с условием. Логика при его использовании такова:

1. Задается вопрос, содержащий элемент сравнения.
2. Далее вписываются 2 значения. Первая величина отобразится в ячейке в случае ответа «TRUE», вторая — если ответ «FALSE».
3. Возможно создание многоуровневых вложений «ЕСЛИ».

Требуется разделить сотрудников в таблице по критерию исполнения плана. Для этого в программе создается таблица с дополнительными колонками E (Выполнение плана) и F (Зарплата за месяц).

Для выделения сотрудников применяется формула =ЕСЛИ(D4>=1000000;»Молодец!»;»План не выполнен:(«). Расшифровывается так:

Результатом будет таблица, в которой указано, выполнил менеджер план или нет.

ЕСЛИМН или УСЛОВИЯ

В предыдущем примере было одно условие. Но в большинстве случаев при составлении отчетов учитывается много факторов. Приходится составлять многоуровневые вложенные «ЕСЛИ».

Запись упростилась, указываются условия и соответствующие им значения. Последним аргументом можно указать оператор ИСТИНА и задать нужную величину. В случае если ни одно из условий не выполнилось, будет возвращен параметр функции ИСТИНА. В данном случае, если В2 >=1, то награда 30%.

Работа с ПЕРЕКЛЮЧ

Сравнивает указанную величину в ячейке или формулу со списком данных и вписывает в ячейку первое совпавшее значение. Если совпадений не будет, и не проставлена величина по умолчанию, оператор выдаст ошибку «#Н/Д». Функция схожа с ЕСЛИМН, но в отличие от нее условие ставится точно, без сравнительных знаков.

Работа оператора иллюстрируется на рисунке.

Здесь вместо чисел 1, 2, 7 — нужно проставить прописью дни недели им соответствующие. Если будут другие цифры, то возвратится значение по умолчанию «Нет совпадений (No match)».

Использование ЕСЛИОШИБКА

Оператор используется для нахождения ошибки в таблице. Найдя ее, функция не пишет в ячейке какую-либо из ошибок, а возвращает указанный ответ, который может быть текстом, пустой строкой: =ЕСЛИОШИБКА(Что_проверять;Что_выводить_вместо_ошибки).

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

Применение оператора «=ЕСЛИОШИБКА(A2/B2;»»)» скрывает ошибки.

Здесь сравнивается выражение A2/B2. В случае обнаружения ошибки в ячейку ставится пустая строка, указанная пробелом в кавычках ““.

ЕСЛИОШИБКА появилась в Excel 2007. До этого использовалась функция ЕОШИБКА, которая самостоятельно не могла обработать ошибку, так как имела только один аргумент, проверяющий указанную ячейку. Для ввода ответа в случае обнаружения ошибки, нужно было использовать оператор ЕСЛИ: «ЕСЛИ(ЕОШИБКА(А2/В2);”“;А2/В2)».

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

На рисунке показан принцип действия функции И.

Пример использования: «=И(A1>B1; A225)». Здесь созданы два условия:

При исполнении обоих получается ИСТИНА.

Если одно из заданий нарушено, получается ЛОЖЬ. В данном случае число в А1 меньше чем в В1.

Ниже представлен алгоритм функционирования оператора ИЛИ.

Пусть даны 3 выражения: A1>B1; A2>B2; A3>B3. Требуется применить к ним действие ИЛИ: «=ИЛИ(A1>B1; A2>B2; A3>B3)». Возможные варианты показаны на рисунках:

Здесь конечный результат ИСТИНА, так как из трех выражений одно верно: A3>B3. На следующем изображении функция выдала ответ «ЛОЖЬ», так как на все вопросы получены аналогичные ответы.

Действие ИСКИЛИ

В программировании функция соответствует операция «сложение по модулю 2» или XOR. Если имеется больше двух аргументов, то действуют следующие правила:

Даны 4 условия A1>B1; A2>B2; A3>B3; A4>B4. В зависимости от данных ячеек результат действия функции может быть различным.

На рисунке ниже получен результат «ИСТИНА», так есть 3 условия с аналогичным результатом: A1>B1 (100 ); А2 В2 (100>80); А3>В3 (100>70). Число условий с ответом ИСТИНА нечетно.

В следующем варианте решением будет «ЛОЖЬ», так как есть 4 ответа «ИСТИНА» — четное количество.

На последнем рисунке функция также обретет значение ЛОЖЬ, так как не выполнено ни одно условие.

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

Способ 4. Функция баз данных БДСУММ

1. C2>=4, что контролирует оценку по русскому языку.
2. СУММ(C2:E2)>=$C$8. Складываются полученные баллы. Их сумма должна быть равна или больше значения в ячейке С8, то есть 12.
3. Если оба условия выполняются, то И принимает значение «TRUE», в противном случае — «FALSE».

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

Функция СЧЁТЕСЛИМН в Excel

​ от проданной продукции​ 2-м критериям (Условие​) =5, которое удовлетворяет​

Синтаксис

​=БСЧЁТА(A1:B13;A1;D14:F15) или БСЧЁТ(A1:B13;B1;D14:F15), которые требуют наличия​ критериями.​

​формула массива =СУММ((B2:B13=D2))​​ ячейку или текста,​ и Климов превысили​В условии можно использовать​Функция​

​ еженедельно и даже​​Прописываем аргументы.​ зарплаты.​Критерий – определяет, какие​ на пустую ячейку​ клавиш Ctrl+Shift+Enter:​ составляет техника фирмы​ И) можно без​ критерию >=5, поэтому​ возьмем таблицу с​ отдельной таблички с​

​Рассмотрим задачу, когда 1​​формула массива =СЧЁТ(ЕСЛИ((B2:B13>=D2)*(B2:B13​ которые определяют, какие​ квоту для периодов​ подстановочные знаки: вопросительный​СЧЁТЕСЛИМН​

​ чаще. Пользуйтесь поиском​​диапазон суммирования – ячейки​Аналогично можно подсчитать зарплаты​ ячейки из диапазона​ или пустая строка​Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив​​ Samsung.​​ применения формул с​ второе значение в​ двумя столбцами: текстовым​

Замечания

​ критериями.​ текстовый критерий с​=БСЧЁТА(A1:B13;A1;D14:E15) или =БСЧЁТ(A1:B13;B1;D14:E15), которые требуют​ ячейки требуется учитывать.​ Q1, Q2 и​ знак (?) и​применяет критерии к​ по форуму.​ с зарплатой;​ всех менеджеров, продавцов-кассиров​ будут выбраны (записывается​ «», результат вычисления​ дробных чисел 1/количество_вхождений,​Вид исходной таблицы данных:​ помощью стандартного Автофильтра.​

​ массиве =1 и​ «Фрукты» и числовым​О подсчете с множественными​ подстановочным знаком применяются​​ наличия отдельной таблички​​ Например, условие может​

​ Q3 (только в​ звездочку (*). Вопросительный​ ячейкам в нескольких​r_kot​диапазон условия 1 –​ и охранников. Когда​ в кавычках).​ для любого диапазона​ например, для пользователя​Для расчета используем формулу:​Установите автофильтр к столбцу​ т.д.​​ «Количество на складе»​​ критериями можно почитать​

Пример 1

​ к значениям текстового​ с критериями.​ быть выражено следующим​ Q2).​ знак соответствует любому одиночному​ диапазонах и вычисляет​: Поправил формулу. Вместо​ ячейки с указанием​ табличка небольшая, кажется,​Диапазон суммирования – фактические​ ячеек будет числовое​ с ником sam​=СЧЁТЕСЛИ(C3:C17;»Samsung»)/A17​

В качестве примера приведем решение задачи с логическими операторами ИСТИНА и ЛОЖЬ. Они обычно не используются отдельно, а только в составе других операторов. Понять принцип работы можно на примере. В таблице телефонных номеров определяются платные и бесплатные вызовы.

Аргументы

• В формуле B5: B10 — это диапазон ячеек, который нужно подсчитать. D5 — это ссылка на ячейку, содержащую то, что вы хотите найти. Вы можете изменить ссылочную ячейку и критерии в формуле по своему усмотрению.
• Если вы хотите напрямую вводить текст в формуле для подсчета, примените следующую формулу:=COUNTIF(B5:B10,»*Apple*»)
• В этой формуле регистр не учитывается.

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, . Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.