Как посчитать количество заполненных ячеек в excel

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

Для начала создадим небольшую таблицу с оценками учеников за экзамены.

Применим функцию СЧЕТ к диапазону значений от В4 до Е8. Для этого в свободном месте таблицы записываете формулу, где аргументом будет указанный выше массив.

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

Когда диапазон заполняется руками довольно долго, бывает, что могут быть пропущены ячейки. Чтобы определить есть ли такие ячейки в Excel диапазоне, есть специальная функция СЧИТАТЬПУСТОТЫ (COUNTBLANK). В статье так же рассмотрены другие способы как посчитать количество пустых ячеек

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

Считаем количество пустых ячеек функцией СЧИТАТЬПУСТОТЫ

количество пустых ячеек

В формуле необходимо задать диапазон, как показано на картинке

Такая формула выдаст значение = 3.

Считаем количество пустых ячеек функцией СЧЁТЕСЛИ

В версиях Excel ниже 2007 не было функции СЧИТАТЬПУСТОТЫ, поэтому мы часто пользовались СЧЁТЕСЛИ.

Запишем формулу для того же диапазона

Как определить пустая ли конкретная ячейка? ЕПУСТО

Важное замечание по пустым ячейкам

Если вы использовали в формулах замену на пустое значение обозначенное как «» (например, =ЕСЛИОШИБКА(A:A;»»)), то после копирования значения такой ячейки она не будет считаться пустой. Хотя на вид она выглядит как незаполненная.

Для определения только пустых ячеек применяется функция

Как выделить цветом пустые ячейки в Excel?

Конечно же условным форматированием . Выделяем необходимый диапазон, в верхней панели во вкладке Главная находим Условное форматирование — Создать правило…

Пустые ячейки 1

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

Пустые ячейки 2

Ячейки выбранного диапазона окрасились цветом

Пустые ячейки 3

Если в ячейке стоит пробел, то такая ячейка уже не будет считаться пустой функцией СЧИТАТЬПУСТОТЫ. При этом и условное форматирование не будет выделять эту ячейку как пустую. Поиск и замену подобных ячеек можно осуществить при помощи функции Найти и заменить

Функции подсчета количества в DAX: COUNT, COUNTA, COUNTX, COUNTAX, COUNTBLANK, DISTINCTCOUNT и COUNTROWS (для Power BI и Power Pivot)

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы рассмотрим функции DAX из так называемой группы COUNT, отвечающей за подсчет количества значений, ячеек или строк при составлении формул в Power BI и Excel (Powerpivot).

И это функции COUNT, COUNTA, COUNTX, COUNTAX, COUNTBLANK, DISTINCTCOUNT и COUNTROWS, входящие в категорию статистических функций агрегирования DAX.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функции COUNT, COUNTA, COUNTX и COUNTAX в Power BI и Power Pivot

Итак, все эти функции COUNT, COUNTA, COUNTX и COUNTAX — отвечают за подсчет количества ячеек в Power BI и Power Pivot, но содержание ячеек в каждом из этих вариантов различается.

  1. COUNT () — подсчитывает в столбце количество ячеек, которые содержат в себе числовое значение. В качестве числового значения признаются числа, даты и число, записанное в текстовом типе данных. Если в строке учитываемых значений нет, то функция выдаст 0. Если в таблице отсутствуют строки, то COUNT выдаст пустое значение.Синтаксис: COUNT ([Столбец])
  2. COUNTA () — подсчитывает непустые ячейки в столбце. То есть, количество тех ячеек, которые в себе содержат хоть какое-то значение: числа, даты, любой текст или значения логического типа.Синтаксис: COUNTA ([Столбец])
  3. COUNTX () — подсчитывает количество строк, содержащие в себе числовое значение, получившееся в результате построчного выполнения выражения. В качестве числового значения признаются числа, даты и число, записанное в текстовом типе данных.Синтаксис: COUNTX (‘Таблица’; Выражение), где:
    • ‘Таблица’ — исходная таблица или табличное выражение, по строкам которой будет вычисляться выражение из второго параметра функции
    • Выражение — любое выражение, которое необходимо выполнить по строкам таблицы, входящей в первый параметр функции

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

Исходная таблица

Записав следующую DAX формулу с участием COUNTAX ():

То COUNTBLANK вернет ответ: количество пустых ячеек = 2

Результат выполнения функции COUNTBLANK

DAX функция DISTINCTCOUNT или DISTINCT COUNT в Power BI и Power Pivot

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

Итак, DISTINCTCOUNT () — подсчитывает количество уникальных значений ячеек в столбце

Синтаксис: DISTINCTCOUNT ([Столбец])

Пример: имеется таблица, где в одном из столбцов перечисляются менеджеры:

Исходная таблица менеджеров

Если мы подсчитаем количество уникальных фамилий менеджеров при помощи следующей формулы:

То ответ будет таким: количество уникальных фамилий менеджеров = 3

Результат выполнения функции DISTINCTCOUNT

DAX функция COUNTROWS в Power BI и Power Pivot

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

Синтаксис: COUNTROWS (‘Таблица’)

Пример: если мы подсчитаем количество строк в таблице «Менеджеры» из примера выше, то COUNTROWS выдаст ответ 4 строки.

Результат выполнения функции COUNTROWS

COUNT и другие функции (CALCULATE, FILTER, IF)

Функции группы COUNT (подсчет количества) сами по себе используются довольно редко, обычно они нужны для каких-либо промежуточных вычислений в партнерстве с другими функциями. Например, функции группы COUNT используются совместно с CALCULATE, FILTER, с условиями «если» IF и другими функциями DAX.

Ну, как пример, можно подсчитать количество строк в таблице после применения фильтра функции FILTER:

Или после изменения фильтра при использовании CALCULATE:

На этом, по функциям группы COUNT в Power BI и Power Pivot в данной статье, все.

Подробное ВИДЕО «DAX функции COUNT (A, X, AX), COUNTBLANK, DISTINCTCOUNT, COUNTROWS для Power BI (Pivot)»

Ссылки из видео:
1) [Регистрируйтесь в бесплатном экспресс курсе] Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot: зарегистрироваться
2) [Скачивайте PDF] Справочник DAX функций для Power BI и Power Pivot на русском языке: скачать

Пожалуйста, оцените статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

[Экспресс-видеокурс] Быстрый старт в языке DAX

Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

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

Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Что еще посмотреть / почитать?

DAX функции CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER, CLOSINGBALANCEMONTH и OPENINGBALANCEYEAR, OPENINGBALANCEQUARTER, OPENINGBALANCEMONTH

Вычисление выражений на последнюю / первую даты периода в Power BI и Power Pivot: DAX функции группы CLOSINGBALANCE и OPENINGBALANCE (YEAR, QUARTER, MONTH)

DAX функции ADDCOLUMNS И DATATABLE

Функции создания вычисляемых столбцов и таблиц в DAX: ADDCOLUMNS И DATATABLE в Power BI и Power Pivot

Функции суммирования в DAX: SUM и SUMX

Функции суммирования в DAX: SUMX и SUM в Power BI и Power Pivot

СЧЕТЕСЛИ()

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

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

СЧЕТЕСЛИ позволяет подсчитывать ячейки, содержащие текстовые значения. Например, следующая формула возвращает количество ячеек со словом “текст”, причем регистр не имеет значения.

Логическое условие функции СЧЕТЕСЛИ может содержать групповые символы: * (звездочку) и ? (вопросительный знак). Звездочка обозначает любое количество произвольных символов, а вопросительный знак – один произвольный символ.

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

Если одного условия Вам будет недостаточно, Вы всегда можете воспользоваться статистической функцией СЧЕТЕСЛИМН . Данная функция позволяет подсчитывать ячейки в Excel, которые удовлетворяют сразу двум и более условиям.

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

Функция СЧЕТЕСЛИМН позволяет подсчитывать ячейки, используя условие И . Если же требуется подсчитать количество с условием ИЛИ , необходимо задействовать несколько функций СЧЕТЕСЛИ . Например, следующая формула подсчитывает ячейки, значения в которых начинаются с буквы А или с буквы К :

Функции Excel для подсчета данных очень полезны и могут пригодиться практически в любой ситуации. Надеюсь, что данный урок открыл для Вас все тайны функций СЧЕТ и СЧЕТЕСЛИ , а также их ближайших соратников – СЧЕТЗ , СЧИТАТЬПУСТОТЫ и СЧЕТЕСЛИМН . Возвращайтесь к нам почаще. Всего Вам доброго и успехов в изучении Excel.

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

Если диапазон (например, a2: D20) имеет числовые значения 5, 6, 7 и 6, то число 6 встречается два значения.

Если столбец имеет значения «Батурин», «Белов», «Белов» и «Белов», то «Белова» выполняется три значения.

Подсчет количества вхождений отдельного значения с помощью функции СЧЁТЕСЛИ

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

Дополнительные сведения см. в статье Функция СЧЁТЕСЛИ .

Подсчет количества вхождений на основе нескольких критериев с помощью функции СЧЁТЕСЛИМН

Функция СЧЁТЕСЛИМН аналогична функции СЧЁТЕСЛИ с одним важным исключением: СЧЁТЕСЛИМН позволяет применить критерии к ячейкам в нескольких диапазонах и подсчитывает число соответствий каждому критерию. С функцией СЧЁТЕСЛИМН можно использовать до 127 пар диапазонов и критериев.

Синтаксис функции СЧЁТЕСЛИМН имеет следующий вид:

СЧЁТЕСЛИМН (диапазон_условия1;условие1;[диапазон_условия2;условие2];…)

Дополнительные сведения об использовании этой функции для подсчета вхождений в нескольких диапазонах и с несколькими условиями см. в статье Функция СЧЁТЕСЛИМН .

Подсчет количества вхождений на основе условий с помощью функций СЧЁТ и ЕСЛИ

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

Дополнительные сведения об этих функциях см. в статьях Функция СЧЁТ и Функция ЕСЛИ .

Подсчет количества вхождений нескольких текстовых и числовых значений с помощью функций СУММ и ЕСЛИ

В следующих примерах функции ЕСЛИ и СУММ используются вместе. Функция ЕСЛИ сначала проверяет значения в определенных ячейках, а затем, если возвращается значение ИСТИНА, функция СУММ складывает значения, удовлетворяющие условию.

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

Функция выше означает, что если диапазон C2:C7 содержит значения Шашков и Туманов , то функция СУММ должна отобразить сумму записей, в которых выполняется условие. Формула найдет в данном диапазоне три записи для «Шашков» и одну для «Туманов» и отобразит 4 .

Функция выше означает, что если ячейка D2:D7 содержит значения меньше 9 000 ₽ или больше 19 000 ₽, то функция СУММ должна отобразить сумму всех записей, в которых выполняется условие. Формула найдет две записи D3 и D5 со значениями меньше 9 000 ₽, а затем D4 и D6 со значениями больше 19 000 ₽ и отобразит 4 .

Приведенная выше функция говорит о том, что D2: D7 содержит счета для Батурина менее чем на $9000, а сумма должна отобразить сумму записей, в которых оно соблюдается. Формула найдет ячейку C6, которая соответствует условию, и отобразит 1 .

Подсчет количества вхождений нескольких значений с помощью сводной таблицы

Для отображения итоговых значений и подсчета числа повторений в сводной таблице можно использовать сводную таблицу. Сводная таблица — это интерактивный способ быстрого суммирования больших объемов данных. Вы можете использовать ее для развертывания и свертывания уровней представления данных, чтобы получить точные сведения о результатах и детализировать итоговые данные по интересующим вопросам. Кроме того, можно перемещать строки в столбцы или столбцы в строки («сводить» их) для просмотра количества вхождений значения в сводной таблице. Рассмотрим пример электронной таблицы «Продажи», в которой можно подсчитать количество значений продаж для разделов «Гольф» и «Теннис» за конкретные кварталы.

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

Мы будем использовать функцию СЧЁТЕСЛИ, чтобы отсортировать количество. Это не ограничивается «Да» или «Нет», но любое повторяющееся значение в столбце.

Подсчитайте количество записей «Да» или «Нет» в Excel

Синтаксис функции СЧЁТЕСЛИ для поиска количества повторяющихся записей в столбце следующий:

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

Например. Если вам нужно подсчитать количество записей «Да» и «Нет» для списка из 10 человек, так что записи «Да» и «Нет» помещаются в столбец C от C4 до C13, формула для подсчета записей «Да» будет выглядеть следующим образом:

= СЧЁТЕСЛИ (C4: C13; «Да»)

Подсчитать количество записей Да в Excel

А для формулы для подсчета записей нет будет:

= СЧЁТЕСЛИ (C4: C13; «Нет»)

Подсчитать количество записей в Excel

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

Подсчитайте количество записей, кроме «Да» или «Нет» в Excel

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

  • – это первая ячейка в столбце записей, из которой нужно рассчитать количество повторяющихся записей.
  • – это первая ячейка в столбце записей, из которой нужно рассчитать количество повторяющихся записей.
  • – первое повторяющееся слово, а – второе повторяющееся слово.

В случае, упомянутом выше, если предположить, что первая ячейка в столбце C – это C4, последняя ячейка – C13, первая запись – Да, а вторая запись – Нет, формула будет иметь следующий вид:

Подсчитайте количество записей, отличных от Да или Нет в Excel

Теперь прочтите: Как удалить пустые ячейки из электронной таблицы Microsoft Excel.

Подсчитайте количество записей Да в Excel.

Дофамин

Что это?

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

Роль дофамина в организме

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

Как нейромедиатор оказывает влияние на:

  • формирование мотивации;
  • чувство удовольствия;
  • ощущение награды и желания;
  • эмоциональные реакции, сопровождающие двигательную активность.

Выделению дофамина способствует:

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

  • Употребление любимой пищи. У потребление всеми любимого шоколада повышает уровень дофамина. Однако своеобразными «кирпичиками», предшественниками дофамина, служат молекулы аминокислоты тирозина. Чем больше его содержание в продуктах, тем выше шанс получить удовольствие. Тирозин содержится, к примеру, в мясе, бобовых (соя, чечевица, фасоль), орехах, сыре и твороге.
  • Отдых и физическая активность. Отличный помощник в выработке дофамина — полноценный сон. Второй вариант противоположен — это любая активность, будь то занятие определенныи видом спорта или обычная пробежка. Дофамин призван мотивировать человека, который ждет какого-то вознаграждения в конце тренировки: похудения, отдыха и т.д. А за это предвкушение тоже отвечает дофамин.

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

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

Что тормозит выработку дофамина?

— Выработку и высвобождение дофамина в мозге в 5-10 раз увеличивают наркотические вещества. А затем — уменьшают, заставляя повышать дозу. Чувство удовольствия вырабатывается искусственным образом.

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

Блокирует выработку нейромедиатора также и злоупотребление жирной и сладкой пищей. По той же схеме, что и с наркотиками: организм требует еще и еще, что в итоге приводит к перееданию и пищевой зависимости.

Подсчет ячеек, не содержащих определенного текста

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

Например, если у меня есть набор данных, как показано ниже, и я хочу подсчитать все ячейки, имя которых не является «Маркой», я могу легко сделать это с помощью функции СЧЁТЕСЛИ.

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

= СЧЁТЕСЛИ (A2: A13; «<> Отметить»)

В приведенной выше формуле в качестве первого аргумента используется диапазон ячеек, а критерием является «<> Отметить» . Здесь знак «не равно» также должен быть заключен в двойные кавычки.

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.

Вид исходной таблицы:

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

Поиск по части слова или букве

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

  1. Найдем количество позиций, которые начинаются на букву «Т». Для этого вновь выбираем отдельную ячейку и начинаем писать «СЧЕТЕСЛИ».

СЧЕТЕСЛИ

  1. Выбираем диапазон с названиями продукции.

Диапазон названий

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

Формула

  1. Жмем [knopka]Enter[/knopka] и смотрим на результат.

Второй результат

Как осуществлять расчеты в программе Excel

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

В этой небольшой заметке речь пойдет об одном из аспектов использования таблиц Excel – для создания форм математических расчетов.

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

предлагаю решить в Excel следующую задачу.

Три человека (Иван, Петр и Василий) ведут общую предпринимательскую деятельность, заключающуюся в оптовом приобретении овощей в одних регионах страны, их транспортировке в регионы с повышенным спросом и реализации товара по более высокой цене. Иван занимается закупкой, Василий — реализацией и оба они получают по 35 % от чистой прибыли.

Петр – транспортирует товар, его доля – 30 %. По этой схеме партнеры работают постоянно и ежемесячно (или даже чаще) им приходится подсчитывать, сколько денег кому положено.

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

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

  • Перед началом работы в Excel
    хочу обратить внимание на то, что
    каждая ячейка в таблице Excel имеет свои координаты
    , состоящие из буквы вертикального столбца и номера горизонтальной строки.
  • Работа в Excel. Создание расчетной таблицы
    :
  • Открываем таблицу и подписываем ячейки, в которые будем вводить исходные данные (закупочная стоимость товара, его количество, цена реализации товара, расстояние транспортировки, стоимость топлива).
  • Затем подписываем еще несколько промежуточных полей, которые будут использоваться таблицей для вывода промежуточных результатов расчета (потрачено на закупку товара, выручено от реализации товара, стоимость транспортировки, прибыль).

Подсчет уникальных и отличных строк в Excel

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

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

Формула для подсчета уникальных строк:

Формула для подсчета различных строк:

Подсчет уникальных и отчетливых строк в Excel

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

Метод 4: применение функции СЧЁТЕСЛИ

Функция СЧЕТЕСЛИ используется в ситуациях, когда необходимо не просто определить количество ячеек с данными, но и выбрать среди них те, для которых выполняется заданное условие, в качестве которого могут выступать операции сравнения как цифровых, так и прочих форматов данных, например:

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

    Как и в предыдущих методах, встаем в ячейку для расчета результата запускаем Мастер функций.

      выбираем в перечне категорий значение “Полный алфавитный перечень“;

    в списке операторов щелкаем по функции “СЧЁТЕСЛИ” и затем – OK.

    Как и в предыдущих методах в качестве значения аргумента “Диапазон” нужно указать координаты области ячеек (вручную или с помощью выделения левой кнопкой мыши в самой таблице).


Примечание: Если есть желание, работу в Мастере функций можно упустить и сразу ввести нужную формулу функции в выбранной ячейке. Сама формула выглядит так: =СЧЁТЕСЛИ(диапазон;критерий).

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

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

  1. ЛКМ выделяем ячейку, в которой хотим посчитать сумму. Затем, либо в самой ячейке, либо перейдя в строку формул, пишем знак “=”, ЛКМ нажимаем на первую ячейку, которая будет участвовать в расчетах, после нее пишем знак “+”, далее выбираем вторую, третью и все требуемые ячейки, не забывая между ними проставлять знак “+”.
  2. После того, как формула готова, нажимаем “Enter” и получаем результат в выбранной нами ячейке.
    Основным минусом данного способа является то, что сразу отобрать несколько ячеек невозможно, и необходимо указывать каждую по отдельности.

Добавление критериев поиска

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

Ссылка на основную публикацию