Функция счет если в excel

русском английском
СЧЁТЕСЛИ COUNTIF

Описание

Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют одному указанному пользователем критерию. Например, можно подсчитать количество всех ячеек, которые начинаются с определенной буквы или содержат числа, большие или меньшие указанного значения. Предположим, например, что есть лист со списком задач в столбце A, а имя пользователя, ответственного за каждую из задач, указано в столбце B. Для подсчета количества повторений имени ответственного в столбце B можно использовать функцию СЧЁТЕСЛИ. Это позволяет определить количество задач, назначенных этому пользователю. Пример:

=СЧЁТЕСЛИ(B2:B25;"Валентин")

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

Дополнительная информация (источник)

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

Синтаксис и особенности функции

Сначала рассмотрим аргументы функции:

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

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

В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.

Рекомендации для правильной работы функции:

  • Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
  • Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
  • Функция не учитывает регистр текстовых значений.
  • При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
  • Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.

Функция СЧЕТЕСЛИ в Excel: примеры

Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.

У нас есть такая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:

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

Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.

Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:

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

Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).

Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:

Формула посчитала «кровати» и «банкетки».

Используем в функции СЧЕТЕСЛИ условие поиска «не равно».

Формула: =СЧЁТЕСЛИ(A1:A11;»<>»&»стулья»). Оператор «<>» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».

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

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

  1. Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».
  2. Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» — на базе критерия в ячейке А2.
  3. Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).
  4. Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.
  5. Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

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

  1. Сначала отсортируем таблицу так, чтобы одинаковые значения оказались рядом.
  2. Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» — «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежуточного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).

Скачать примеры функции СЧЕТЕСЛИ в Excel

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

  • Гайдамакина Вероника Ивановна, учитель информатики

Разделы: Информатика

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

Упражнение 1. «Магазин»

Приложение 1

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

Комментарии:

I. Создайте две таблицы: с исходными данными и результатами.

  A B C D E F
1 Наименование Поставщик Количество, л Цена Стоимость
2 1 Молоко Поставщик № 1 76 43,00р.  
3 2 Кефир Поставщик № 2 40 26,00р.  
4 3 Варенец Поставщик № 3 86 34,00р.  
5 4 Молоко Поставщик № 3 45 26,00р.  
6 5 Молоко Поставщик № 4 87 24,00р.  
7 6 Варенец Поставщик № 4 34 41,00р.  
8 7 Кефир Поставщик № 4 67 32,00р.  
9 8 Кефир Поставщик № 3 45 39,00р.  
10 9 Молоко Поставщик № 2 43 35,00р.  
11 10 Молоко Поставщик № 5 32 33,00р.  

Таблица 1

  A B C D E

14

Наименование Кол-во поставок Суммарная стоимость Средняя цена

15

1 Молоко      

16

2 Кефир      

17

3 Варенец      

Таблица 2

II. Введите формулы в таблицы.

1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке

F2: =D2*E2.

Результат:

  A B C D E F
1 Наименование Поставщик Количество, л Цена Стоимость
2 1 Молоко Поставщик № 1 76 43,00р. 3 268,00р.
3 2 Кефир Поставщик № 2 40 26,00р. 1 040,00р.
4 3 Варенец Поставщик № 3 86 34,00р. 2 924,00р.
5 4 Молоко Поставщик № 3 45 26,00р. 1 170,00р.
6 5 Молоко Поставщик № 4 87 24,00р. 2 088,00р.
7 6 Варенец Поставщик № 4 34 41,00р. 1 394,00р.
8 7 Кефир Поставщик № 4 67 32,00р. 2 144,00р.
9 8 Кефир Поставщик № 3 45 39,00р. 1 755,00р.
10 9 Молоко Поставщик № 2 43 35,00р. 1 505,00р.
11 10 Молоко Поставщик № 5 32 33,00р. 1 056,00р.

Таблица 1.1

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

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

=СЧЁТЕСЛИ(B$2:B$11;B15),

где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).

Результат:

  A B C D E
14 Наименование Кол-во поставок Суммарная стоимость Средняя цена
15 1 Молоко 5    
16 2 Кефир 3    
17 3 Варенец 2    

Таблица 2.1

b) Для определения суммарной стоимости всех продуктов одного названия нужно выбрать из первой таблицы и сложить стоимость всего, например, молока в магазине. Для этого используйте функцию СУММЕСЛИ, которая суммирует данные, отобранные по заданному критерию в данном диапазоне. Примерный вид формулы в ячейке D15:

=СУММЕСЛИ(B$2:B$11;B15;F$2:F$11),

где F2:F11 — диапазон стоимости продукта из первой таблицы.

Результат:

  A B C D E
14 Наименование Кол-во поставок Суммарная стоимость Средняя цена
15 1 Молоко 5 9 087,00р.  
16 2 Кефир 3 4 939,00р.  
17 3 Варенец 2 4 318,00р.  

Таблица 2.2

c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:

=СУММЕСЛИ(B$2:B$11;B15;E$2:E$11)/C15,

где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.

Результат:

  A B C D E
14 Наименование Кол-во поставок Суммарная стоимость Средняя цена
15 1 Молоко 5 9 087,00р. 32,20р.
16 2 Кефир 3 4 939,00р. 32,33р.
17 3 Варенец 2 4 318,00р. 37,50р.

Таблица 2.3

Замечания:

Данное упражнение может быть дополнено следующими заданиями (и не только ими):

Упражнение 2. «Студенческие стипендии» [1]

Приложение 1

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

  • стипендия не назначается, если среди оценок есть хотя бы одна двойка;
  • 3,0 <</u> средний балл < 3,5 — 1000р.
  • 3,5 <</u> средний балл < 4,0 — 1200р.
  • 4,0 <</u> средний балл < 4,5 — 1500р.
  • 4,5 <</u> средний балл < 5,0 — 1800р.
  • средний балл = 5,0 — 2000р.

Определить общую сумму назначенных стипендий.

Замечания:

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

Комментарии:

  • Фамилий в таблице должно быть не менее 10, предметов не менее 5, например:
Фамилия Предметы Средний балл Стипендия
Матем. Физика Информ. Химия История
1 Алексеев 5 3 2 3 3    
2 Гаврилов 4 4 3 3 3    
3 Зайцев 3 3 3 3 3    
4 Иванов 4 3 5 3 3    
5 Краснов 5 4 4 4 4    
6 Кузнецов 4 5 5 5 5    
7 Петров 5 5 5 5 5    
8 Сидоров 4 3 3 3 5    
9 Смирнов 4 4 4 4 4    
10 Солдатов 5 4 5 2 3    
  • Для определения количества двоек использовать функцию СЧЁТЕСЛИ. Можно вставить еще один столбец для определения количества двоек, а можно вложить функцию СЧЁТЕСЛИ в функцию ЕСЛИ, которая будет использована для определения размера стипендии. Примерный вид формулы:

=ЕСЛИ(СЧЁТЕСЛИ(C3:G3;2)>0;0;ЕСЛИ(H3<3,5;1000;ЕСЛИ(H3<4;1200; ЕСЛИ(H3<4,5;1500;ЕСЛИ(H3<5;1800;2000))))).</em>

Результат:

Фамилия Предметы Средний балл Стипендия
Матем. Физика Информ. Химия История
1 Алексеев 5 3 2 3 3 3,2 0р.
2 Гаврилов 4 4 3 3 3 3,4 1 000р.
3 Зайцев 3 3 3 3 3 3 1 000р.
4 Иванов 4 3 5 3 3 3,6 1 200р.
5 Краснов 5 4 4 4 4 4,2 1 500р.
6 Кузнецов 4 5 5 5 5 4,8 1 800р.
7 Петров 5 5 5 5 5 5 2 000р.
8 Сидоров 4 3 3 3 5 3,6 1 200р.
9 Смирнов 4 4 4 4 4 4 1 500р.
10 Солдатов 5 4 5 2 3 3,8 0р.
              Итого: 11 200р.

Упражнение 3. «Завод железобетонных изделий»

Приложение 1

Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.

I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.

II. Ввод формул.

1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны. 2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:              a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.              b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функции ОКРУГЛВВЕРХ. 3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.

Упражнение 4. «Прайс-лист»

Приложение 1

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

Литература

  1. Информатика: Практикум по технологии работы на компьютере/ Под ред. Н.В. Макаровой. – 3-е изд., переработ. – М.: Финансы и статистика, 2003. – 256 с.: ил.

7.07.2011

11 апреля 2018 192236 1

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

СУММ

Формула:

=СУММ(число1; число2)

=СУММ(адрес_ячейки1; адрес_ячейки2)

=СУММ(адрес_ячейки1:адрес_ячейки6)

Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)

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

С помощью формулы вы можете:

  • посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
  • посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
  • посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)

СЧЁТ

Формула: =СЧЁТ(адрес_ячейки1:адрес_ячейки2)

Англоязычный вариант: =COUNT(A1:A10)

Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).

СЧЁТЗ

Формула: =СЧЁТЗ(адрес_ячейки1:адрес_ячейки2)

Англоязычный вариант: =COUNTA(A1:A10)

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

ДЛСТР

Формула: =ДЛСТР(адрес_ячейки)

Англоязычный вариант: =LEN(A1)

Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак.

СЖПРОБЕЛЫ

Формула: =СЖПРОБЕЛЫ(адрес_ячейки)

Англоязычный вариант: =TRIM(A1)

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

Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.

ЛЕВСИМВ, ПСТР и ПРАВСИМВ

Формула:

=ЛЕВСИМВ(адрес_ячейки; количество знаков)

=ПРАВСИМВ(адрес_ячейки; количество знаков)

=ПСТР(адрес_ячейки; начальное число; число знаков)

Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).

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

Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».

Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».

Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».

ВПР

Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)

Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)

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

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

  1. В первом списке данные записаны с А1 по В13, во втором – с D1 по Е13.
  2. В ячейке B17 поставим формулу: =ВПР(B16; A1:B13; 2; ЛОЖЬ)
  • B16 = искомое значение, то есть паспортные данные. Они имеются в обоих списках.
  • A1:B13 = таблица, в которой находится искомое значение.
  • 2 – номер столбца, где находится искомое значение.
  • ЛОЖЬ – логическое значение, которое означает то, что вам требуется точное совпадение возвращаемого значения. Если вам достаточно приблизительного совпадения, указываете ИСТИНА, оно также является значением по умолчанию.

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

ЕСЛИ

Формула: =ЕСЛИ(логическое_выражение; «текст, если логическое выражение истинно; «текст, если логическое выражение ложно»)

Англоязычный вариант: =IF(логическое_выражение; «текст, если логическое выражение истинно; «текст, если логическое выражение ложно»)

Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.

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

В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:

=ЕСЛИ(B1>C1; «Норма выполнена»; «Норма не выполнена»)

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

СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ

Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)

=СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)

Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)

Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.

Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.

Используя функции на предыдущем примере, мы можем узнать:

Формула «СУММЕСЛИ»

СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.

Формула «СРЗНАЧЕСЛИ»

СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.

Формула «СЧЁТЕСЛИ»

СЧЁТЕСЛИ – количество продавцов, выполнивших норму.

Конкатенация

Формула: =(ячейка1&» «&ячейка2)

=ОБЪЕДИНИТЬ(ячейка1;» «;ячейка2)

За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&» «&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между » «, чтобы между объединёнными данными появился пробел.

Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;» «; B1) или в англоязычном варианте =concatenate(A1;» «; B1).

Кстати, все перечисленные формулы можно применять и в Google‑таблицах.

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

24.02.2017 imageimageБлог Дмитрия а. Дмитрий Рубрика: Microsoft Office image

Здравствуйте, дорогие читатели.

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

Ну а теперь вернёмся к теме.

Однако не все, кто работает с этим приложением, знает его полную функциональность и умеет ее применять на практике. Вы один из них? Тогда вы обратились по адресу. В частности, сегодня мы разберем, как в excel подсчитать количество ячеек со значением. Есть несколько способов, как это сделать. Они зависят от того, какое именно содержимое вам нужно посчитать. Разберем самые популярные из них.

Самый быстрый способ

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

image

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

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

Подсчет ячеек в строках и столбцах

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

Второй вариант — =ЧИСЛСТОЛБ(массив) — работает по аналогии с предыдущей, но считает сумму секций в столбце.

image

Считаем числа и значения

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

  1. Сколько чисел находится в массиве, можно рассчитать с помощью формулы СЧЁТ(значение1;значение2;…). Она учитывает только те элементы, которые включают в себя цифры.То есть если в некоторых из них будет прописан текст, они будут пропущены, в то время как даты и время берутся во внимание. В данной ситуации не обязательно задавать параметры по порядку: можно написать, к примеру, =СЧЁТ(А1:С3;В4:С7;…).
  2. Другая статистическая функция — СЧЕТЗ — подсчитает вам непустые клетки в диапазоне, то есть те, которые содержат буквы, числа, даты, время и даже логические значения ЛОЖЬ и ИСТИНА.image
  3. Обратное действие выполняет формула, показывающая численность незаполненных секций — СЧИТАТЬПУСТОТЫ(массив). Она применяется только к непрерывным выделенным областям.

Ставим экселю условия

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

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

Разбираемся в критериях

Примеры критериев:

  • «>0» — считаются ячейки с числами от нуля и выше;
  • «Товар» — подсчитываются секции, содержащие это слово;
  • 15 — вы получаете сумму элементов с данной цифрой.

imageДля большей ясности приведу развернутый пример.

Чтобы посчитать ячейки в зоне от А1 до С2, величина которых больше прописанной в А5, в строке формул необходимо написать =СЧЕТЕСЛИ(А1:С2;«>»&А5).

Задачи на логику

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

К примеру, вам нужно знать, сколько имеет электронная таблица клеток с буквой Т без учета регистра. Задаем комбинацию =СЧЕТЕСЛИ(А1:D6;«Т*»). Другой пример: хотите знать численность ячеек, содержащих только 3 символа (любых) в том же диапазоне. Тогда пишем =СЧЕТЕСЛИ(А1:D6;«???»).

Средние значения и множественные формулы

В качестве условия может быть задана даже формула. Желаете узнать, сколько у вас секций, содержимое которых превышают среднее в определенном диапазоне? Тогда вам следует записать в строке формул следующую комбинацию =СЧЕТЕСЛИ(А1:Е4;«>»&СРЗНАЧ(А1:Е4)).

Если вам нужно сосчитать количество заполненных ячеек по двум и более параметрам, воспользуйтесь функцией СЧЕТЕСЛИМН. К примеру, вы ищите секций с данными больше 10, но меньше 70. Вы пишете =СЧЕТЕСЛИМН(А1:Е4;«>10»;А1:Е4;«<70»).</p>

image

Помимо этого, у вас есть возможность задать условия И/ИЛИ. Только во втором случае придется использовать сразу несколько правил. Смотрим: вам необходимо найти клетки, в которых слова начинаются с буквы В или Р — пишете =СЧЕТЕСЛИ(А1:Е4;«В*»)+ СЧЕТЕСЛИ(А1:Е4;«Р*»).

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

До новых встреч!

Этой статьей стоит поделиться

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий