Функція CЧЕТЕСЛИМН в Excel приклади
Розглянемо функції: РАХУНКИ(діапазон;критерій) і РАХУНКОВІМ(діапазон_умови; умова; …). Функція рахунку якщо підраховує кількість непустих осередків у діапазоні, які задовольняють заданій умові. А функція счетслимн (рахунку множинні) підраховує кількість осередків у діапазоні, які задовольняють кільком умовам.
У функції рахунки всього 2 аргументи. Перший аргумент - це діапазон у якому вважається кількість осередків. Другий аргумент – це умова, яка перевіряється у цьому діапазоні. Ця функція може розглядатися як окремий випадок РАХУНОК з однією умовою. Подивимося як працює функція РАХУНОК на прикладі.
Приклад завдання
Візьмемо завдання з ОДЕ з інформатики.
Калорійність 1000 продуктів занесли до електронної таблиці. Назва продукту вказано у стовпці A. Зміст жирів у ньому - у стовпці В. Третій стовпець C - це вміст білків. Кількість вуглеводів зазначено в стовпці D, а в останньому Е - калорійність цього продукту. Скільки записів з таблиці містять менше 50 г вуглеводів і менше 50 г білків?
У клітинку H2 електронної таблиці запишіть кількість таких продуктів у вигляді числа.
Рішення:
Оскільки вихідний файл містить велику кількість записів, то вручну обробити цей файл, наприклад, послідовним переглядом всіх записів практично неможливо. Для вирішення такого завдання потрібно використовувати спеціальні засоби електронних таблиць – формули, відносні посилання, функції, сортування даних тощо.
Це завдання можна вирішити різними способами, використовуючи різні засоби електронних таблиць. Але ми її вирішимо з використанням функції РАХУНОК().
Клікаємо в комірку H2 і викликаємо Майстер функцій зі стрічки інструментів (Вкладка Формули — Вставити функцію або натискаємо значок
Fx
поряд із рядком для ручного введення формул). Відкриється вікно пошуку функції.
Вводимо її назву та клацаємо кнопку Знайти . Далі вибираємо знайдену функцію і тиснемо ОК . У наступному вікні задаємо параметри (дивіться малюнок нижче)
Після введення першого діапазону умови і умови, з'являться поля для другого діапазону і т.д. буд.
Діапазон умови 1: у цій таблиці це стовпець З (білки). Його можна прописати вручну або використовувати виділення діапазону за допомогою миші. Але виділити одну тисячу рядків з прокручуванням екрана досить проблематично, використовуємо прийом, описаний нижче.
Виділення від поточного осередку до кінця стовпця
Клікаємо в першу комірку діапазону. Далі натискаємо одночасно клавіші Ctrl і Shift . Не відпускаючи їх, натискаємо кнопку на клавіатурі "стрілка вниз".
Аналогічно виконуються дії наступних умов.
Формулу можна записати одразу в рядок ручного введення без використання майстра функцій. Вона буде такою: = РАХУНОК (C2:C1001;"<50";D2:D1001;"<50")
Якщо ви не припустилися помилок у записі, то в осередку H2 буде виведено результат розрахунків – число 864.
Відеоприклад використання функції Рахуноклімн()
Відеоприклад використання функції Рахуноклімн() та Суммеслімн()
Додаткові статті на тему:
Функція РАХУНОК() в EXCEL
Як вихідна таблиця візьмемо таблицю з двома стовпцями: текстовим « Фрукти і числовим « Кількість на складі » (Див. файл прикладу).
Синтаксис функції
СУМІСЛИМН( діапазон_умови1; умова1; [діапазон_умови2; умова2]… )
- Діапазон_условія1 . Перший діапазон, в якому необхідно перевірити відповідність заданій умові1;
- Умова1 . Умова у формі числа, виразу, посилання на комірку або тексту, які визначають, які комірки потрібно враховувати. Наприклад, умова може бути виражена в такий спосіб: 32, ">32", B4 або "яблука";
- Діапазон_условія2, умова2. Необов'язкові аргументи. Додаткові діапазони та умови для них. Дозволяється використовувати до 127 пар діапазонів та умов.
Кожен додатковий діапазон повинен складатися з такої кількості рядків і стовпців, що і аргумент діапазон_условия1 . Ці діапазони можуть бути поруч.
В умовах можна використовувати знаки підстановки : знак питання (?) і зірочку (*). Знак питання відповідає кожному одиночному символу; зірочка – будь-якої послідовності символів. Якщо потрібно знайти сам знак питання або зірочку, поставте перед ними знак тильди ( ~ ).
Задача1 (2 числових критерії)
Розглянемо завдання, коли 2 числові критерії застосовуються до значень одного зі стовпців (див. файл прикладу, лист 2числових ).
Знайдемо кількість партій товару з Кількість ящиків на складі не менше 10 і не більше 50 (рядок таблиці відповідає критерію, коли її поле Кількість ящиків на складі задовольняє обом критеріям одночасно).
Для наочності рядки в таблиці, що задовольняють критеріям, виділяються Умовним форматуванням з правилом =І($B2>=$D$2;$B2
Задача2 (2 критерії у форматі Дат)
Розглянемо завдання, коли 2 критерії задані у формі дат і застосовуються до значень одного зі стовпців.
Знайдемо кількість партій товару з Датою надходження на склад не раніше 25.10.2012 і не пізніше 24.12.2012 (рядок таблиці відповідає критерію, коли її поле Дата надходження задовольняє обом критеріям одночасно).
Як вихідна таблиця візьмемо таблицю з двома стовпцями: текстовим « Фрукти » та "Дата надходження" (див. файл прикладу, лист 2Дати ).
Оскільки дати зберігаються в EXCEL у числовому форматі , формули для підрахунку не зміняться (див. задачу 1).
Задача3 (1 текстовий критерій, інший числовий)
Розглянемо завдання, коли 1 текстовий критерій застосовуються до значень текстового стовпця, а інший (числовий) - до значень стовпця з числами (див. файл прикладу, лист 1текст 1числовий ).
Знайдемо кількість партій товару Яблука з Кількість ящиків на складі не менше 10 (рядок таблиці відповідає критерію, коли її поле Фрукт збігається з критерієм Яблука , і коли інше поле Кількість ящиків на складі >=10).
Для наочності рядки в таблиці, що задовольняють критеріям, виділяються Умовним форматуванням з правилом =І($A2=$D$2;$B2>=$E$2)
Задача4 (1 текстовий критерій з підстановним знаком, інший числовий)
Розглянемо завдання, коли 1 текстовий критерій з підстановним знаком застосовуються до значень текстового стовпця, а інший (числовий) - до значень стовпця з числами (див. файл прикладу, лист 1текст (з підстановок) 1числовий ).
Знайдемо кількість партій товару починаються зі слова Яблука і з Кількість ящиків на складі не менше 10 .
На відміну від задачі 3, у вихідній таблиці присутні фрукти з більш складними назвами: яблука свіжі , персики сорт2 . Щоб одночасно підрахувати партії товару Яблука і Яблука свіжі потрібно використовувати знаки підстановки.
Як критерій у осередку
D2
вкажемо Яблука* , де знак * замінює послідовність будь-яких символів, що йдуть правіше.
Хоча формула з функцією РАХУНОК() порівняно з попереднім завданням не зміниться, частина альтернативних рішень працювати не буде (докладніше див. тут).
Задача5 (1 текстовий критерій, 2 числових)
Розглянемо завдання, коли 1 текстовий критерій застосовуються до значень текстового стовпця, а 2 інших (числових) - до значень стовпця з числами (див. файл прикладу, лист 1текст 2числових ).
Знайдемо кількість партій товару Яблука з Кількість ящиків на складі не менше 10 і трохи більше 90 (рядок таблиці відповідає критерію, коли її поле Фрукт збігається з критерієм Яблука , і коли інше поле Кількість ящиків на складі одночасно >=10 і =І($A2=$D$2;$B2>=$E$2;$B2
Формулу запишемо в наступному вигляді: = РАХУНОК (A2:A13; D2;
Альтернативними рішеннями завдання є такі формули:
- = СУМПРОВИЗВ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13
- формула масиву = СУМ((A2:A13=D2)*(B2:B13 =E2))
- формула масиву = РАХУНОК(ЯКЩО((A2:A13=D2)*(B2:B13>=E2)*(B2:B13)
- =БРАХУНКИ(A1:B13;A1;D14:F15) або РАХУНОК(A1:B13;B1;D14:F15) , які вимагають наявності окремої таблички з критеріями.
Про підрахунок з багатьма критеріями можна почитати в цьому розділі.