Формули EXCEL з прикладами — Інструкція по застосуванню

Для того щоб зрозуміти, як користуватися цією програмою, необхідно розглянути формули EXCEL з прикладами.

Програма Excel створена компанією Microsoft спеціально для того, щоб користувачі могли проводити будь-які розрахунки з допомогою формул.

Застосування формул дозволяє визначити значення однієї клітинки виходячи з внесених даних в інші.

Якщо в одній клітинці дані будуть змінені, то відбувається автоматичний перерахунок підсумкового значення.

Що дуже зручно для проведення різних розрахунків, у тому числі фінансових.

У програмі Excel можна виконувати найскладніші математичні обчислення.

У спеціальні комірки файлу потрібно вносити не тільки дані і числа, а й формули. При цьому писати їх потрібно правильно, інакше проміжні підсумки будуть некоректними.

За допомогою програми користувач може виконувати не тільки обчислення і розрахунки, а й логічні перевірки.

У програмі можна обчислити цілий комплекс показників, у числі яких:

• максимальні і мінімальні значення;
• середні показники;
• відсотки;
• критерій Стьюдента і багато іншого.

Крім того, в Excel відображаються різні текстові повідомлення, які залежать безпосередньо від результатів розрахунків.

Головною перевагою програми є здатність перетворювати числові значення і створювати альтернативні варіанти, сценарії, в яких здійснюється миттєвий розрахунок результатів.

При цьому необхідність вводити додаткові дані і параметри відпадає.

Читайте також: Ексель (Excel) для чайників: робота з таблицями, графіками, сортуванням даних і математичними розрахунками

Excel помилка при направленні команди додатком: 6 кращих способів вирішити проблему

Макроси в Excel — Інструкція по використанню

Як застосовувати прості формули в програмі?

Щоб зрозуміти, як працюють формули в програмі, можна спочатку розглянути легкі приклади. Одним з таких прикладів є сума двох значень.

Для цього необхідно ввести в одну комірку одне число, а в другу – інше.

Наприклад, У Комірку А1 – число 5, а в комірку В1 – 3. Для того щоб в комірці А3 з’явилося сумарне значення необхідно ввести формулу:

Обчислення сумарного значення двох чисел

Визначити суму чисел 5 і 3 може кожна людина, але вводити число в комірку С1 самостійно не потрібно, так як в цьому і задум розрахунку формул.

Після введення підсумок з’являється автоматично.

При цьому якщо вибрати комірку С1, то у верхньому рядку видно формула розрахунку.

Якщо одне із значень змінити, то перерахунок відбувається автоматично.

Наприклад, при заміні 5 числа в комірці В1 на число 8, то змінювати формулу не потрібно, програма сама прорахує остаточне значення.

На даному прикладі обчислення сумарних значень виглядає дуже просто, але набагато складніше знайти суму дрібних або великих чисел.

В Excel можна виконувати будь-які арифметичні операції: віднімання «-», ділення «/», множення «*» або додавання «+».

У формули задається вид операції, координати клітинок з вихідними значеннями, функція обчислення.

Формула повинна починатися знаком «=».

Якщо спочатку не поставити «одно», то програма не зможе видати необхідне значення, так як дані введені неправильно.

Створення формули в Excel

У наведеному прикладі формула =СУММ(A1;B1) дозволяє визначити суму двох чисел в комірках, які розташовані по горизонталі.

Формула починається зі знаку «=». Далі задана функція СУМ. Вона вказує, що необхідно зробити підсумовування заданих значень.

В дужках числяться координати клітинок. Вибираючи клітинки, слід не забувати розділяти знаком «;».

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

Формула суми трьох заданих чисел

Якщо потрібно скласти 10 і більше чисел, то використовується інший прийом, який дозволяє виключити виділення кожної клітинки. Для цього потрібно просто вказати їх діапазон.

=SUM(A1:A10). На малюнку арифметична операція буде виглядати наступним чином:

Визначення діапазону клітинок для формули додавання

Також можна визначити добуток цих чисел. У формулі замість функції СУМ необхідно вибрати функцію PRODUCT і вказати діапазон клітинок.

Формула твори десяти чисел

Рада! Застосовуючи формулу «PRODUCT» для визначення значення діапазону чисел можна задати кілька колонок і рядків. При виборі діапазону =PRODUCT(А1:С10), програма виконає множення всіх значень комірок в обраному прямокутнику. Позначення діапазону – (А1-А10, В1-В10, С1-С10).

Об’єднані формули

Діапазон клітинок у програмі вказується з допомогою заданих координат першого і останнього значення. У формулі вони розділяються символом «:».

Крім того, Excel має широкі можливості, тому функції тут можна комбінувати будь-яким способом.

Якщо потрібно знайти суму трьох чисел і помножити суму на коефіцієнти 1,4 або 1,5, виходячи з того, менше підсумок кількості 90 або більше.

Завдання вирішується в програмі за допомогою однієї формули, яка з’єднує кілька функцій і виглядає наступним чином:

Рішення задачі за допомогою комбінованої формули

У прикладі задіяні дві функції – ЯКЩО і СУМ. Перша володіє трьома аргументами:

По-перше, сума клітинок у діапазоні А1:С1 менше 90.

У випадку виконання умови, що сума діапазону клітинок буде становити 88, то програма виконає вказане дію у 2-ому аргументі функції «ЯКЩО», а саме в СУМ(А1:С3)*1,4.

Якщо в даному випадку відбувається перевищення числа 90, то програма обчислить третю функцію – СУМ(А1:С1)*1,5.

Об’єднані формули активно застосовуються для обчислення складних функцій. При цьому кількість їх в одній формулі може досягати 10 і більше.

Щоб навчитися виконувати різноманітні розрахунки і використовувати програму Excel з усіма її можливостями, можна використовувати самовчитель, який можна придбати або знайти на інтернет-ресурсах.

Вбудовані функції програми

В Excel є функції на всі випадки життя. Їх використання необхідно для вирішення різних завдань на роботі, навчанні.

Деякими з них можна скористатися лише один раз, а інші можуть і не знадобитися. Але є ряд функцій, які використовуються регулярно.

Якщо вибрати в головному меню розділ «формули», то тут зосереджені всі відомі функції, в тому числі фінансові, інженерні, аналітичні.

Для того щоб вибрати, чи слід вибрати пункт «вставити функцію».

Вибір функції із запропонованого списку

Цю ж операцію можна виконати за допомогою комбінації на клавіатурі Shift+F3 (раніше ми писали про гарячих клавішах Excel).

Якщо поставити курсор мишки на будь-яку клітинку та натиснути пункт «вибрати функцію», то з’являється майстер функцій.

З його допомогою можна знайти необхідну формулу максимально швидко. Для цього можна ввести її назву, скористатися категорією.

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

У разі якщо функція невідома користувачеві, то він може встановити категорію «повний алфавітний перелік».

Наприклад, дано завдання, знайти функцію SUMIFS. Для цього потрібно зайти до категорії математичних функцій і там знайти потрібну.

Вибір функції та заповнення полів

Далі потрібно заповнити поля чисел і вибрати умову. Таким же способом можна знайти різні функції, в тому числі «SUMIF», «COUNTIF».

Як побудувати графік Excel — Інструкція

Зведені таблиці Excel — Приклади створення

Як закріпити рядок в Excel — Докладні інструкції

Округлення в Excel — Покрокова інструкція

Функція VLOOKUP

З допомогою функції ВВР можна отримати необхідну інформацію з таблиць. Сутність вертикального перегляду полягає в пошуку значення в крайньому лівому стовпці заданого діапазону.

Після чого здійснюється повернення підсумкового значення з комірки, яка розташовується на перетині обраної рядки і стовпці.

Обчислення ВВР можна простежити на прикладі, в якому наведено список прізвищ. Завдання – за запропонованим номером знайти прізвище.

Формула показує, що першим аргументом функції є осередок С1.

Другий аргумент А1:В10 – це діапазон, в якому здійснюється пошук.

Третій аргумент – це порядковий номер стовпця, з якого слід повернути результат.

Обчислення заданої прізвища з допомогою функції ВВР

Крім того, виконати пошук прізвища можна навіть в тому випадку, якщо деякі порядкові номери пропущені.

Якщо спробувати знайти прізвище з неіснуючого номера, то формула не видасть помилку, а дасть правильний результат.

Пошук прізвища з пропущеними номерами

Пояснюється таке явище тим, що функція ВВР володіє четвертим аргументом, за допомогою якого можна задати інтервальний перегляд.

Він має тільки два значення – «хибність» або «істина». Якщо аргумент не задано, то він встановлюється за умовчанням в позиції «істина».

Округлення чисел з допомогою функцій

Функції програми дозволяють провести точне округлення будь-якого дробового числа в більшу або меншу сторону.

А отримане значення можна використовувати при розрахунках в інших формулах.

Округлення здійснюється з допомогою формули «ОКРУГЛВВЕРХ». Для цього потрібно заповнити клітинку.

Перший аргумент – 76,375, а другий – 0.

Округлення числа з допомогою формули

В даному випадку округлення числа сталося в більшу сторону. Щоб округлити в меншу сторону, слід вибрати функцію «ROUNDDOWN».

Округлення до цілого числа. У нашому випадку до 77 або 76.

Функції і формули в Excel допомагають спростити будь-які обчислення. З допомогою електронної таблиці, можна виконати завдання з вищої математики.

Найбільш активно програму використовують проектувальники, підприємці, а також студенти.

Вся правда про формулах програми Microsoft Excel 2007

Формули EXCEL з прикладами — Інструкція по застосуванню

Спосіб підрахунку значень на аркуші

Підрахунок – це невід’ємна складова аналізу будь-яких даних, наприклад кількості працівників організації або одиниць товару, проданого за кожен квартал. У програмі Excel є кілька способів обчислити кількість клітинок, рядків або стовпців із даними. У цій статті наведено вичерпні зведення методів, книгу, доступну для завантаження, інтерактивні приклади та посилання на пов’язані теми для подальшого розуміння.

Примітка.: Підрахунок не слід плутати з підсумовуванням. Докладні відомості про підсумовування значень у клітинках, стовпцях або рядках див. в статті Підсумування способів додавання та підрахунку даних Excel.

Завантаження прикладів

Ви можете завантажити зразок книги, у якій наведено приклади доповнення до інформації в цій статті. Більшість розділів у цій статті стосуються відповідного аркуша в книзі з прикладами та додатковими відомостями.

У цій статті

• Простий підрахунок
  • Використання функції “Автосума”
  • Додавання рядка проміжних підсумків
  • Підрахунок клітинок у списку або стовпці таблиці Excel за допомогою функції SUBTOTAL
  • Відео “Використання функцій COUNT, COUNTIF і COUNTA”
  • Підрахунок клітинок у діапазоні за допомогою функції COUNT
  • Підрахунок клітинок у діапазоні на основі однієї умови за допомогою функції COUNTIF
  • Підрахунок клітинок у стовпці на основі однієї або кількох умов за допомогою функції DCOUNT
  • Підрахунок клітинок у діапазоні на основі кількох умов за допомогою функції COUNTIFS
  • Підрахування частоти появи за певної умови з одночасним використанням функцій COUNT та IF
  • Підрахування частоти появи кількох текстових або числових значень з одночасним використанням функцій SUM та IF
  • Підрахунок клітинок у стовпці або рядку зведеної таблиці
  • Підрахунок непустих клітинок у діапазоні за допомогою функції COUNTA
  • Підрахунок непустих клітинок у списку з певними умовами за допомогою функції DCOUNTA
  • Підрахунок пустих клітинок у суцільному діапазоні за допомогою функції COUNTBLANK
  • Підрахунок пустих клітинок в окремому діапазоні за допомогою поєднання функцій SUM та IF
  • Підрахунок унікальних значень у стовпці списку за допомогою розширеного фільтра
  • Підрахунок кількості унікальних значень у діапазоні, які відповідають одній або кільком умовам, за допомогою функцій IF, SUM, FREQUENCY, MATCH і LEN
  • Підрахунок усіх клітинок у діапазоні за допомогою функцій ROWS і COLUMNS
  • Підрахунок слів у діапазоні за допомогою комбінації функцій SUM, IF, LEN, TRIM і SUBSTITUTE

  Простий підрахунок

  Ви можете обчислити кількість значень у діапазоні або таблиці, застосувавши просту формулу, натиснувши кнопку або скориставшись функцією аркуша.

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

  Відео “Підрахунок клітинок за допомогою рядка стану в програмі Excel”

  Перегляньте наведене нижче відео, щоб дізнатися, як переглянути кількість у рядку стану.

  Використання функції “Автосума”

  Скористайтеся функцією “Автосума “, вибравши діапазон клітинок, який містить принаймні одне числове значення. Потім на вкладці Формули натисніть кнопку Автосума > Кількість чисел.

  Програма Excel повертає кількість числових значень у діапазоні клітинки, суміжної з вибраним діапазоном. Зазвичай цей результат відображається в клітинці праворуч від горизонтального діапазону або в клітинці нижче для вертикального діапазону.

  Додавання рядка проміжних підсумків

  До даних Excel можна додати рядок проміжних підсумків. Клацніть будь-де в даних і виберіть пункт Дані > проміжні підсумки.

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

  Крім того, див. такі статті:

  Підрахунок клітинок у списку або стовпці таблиці Excel за допомогою функції SUBTOTAL

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

  Синтаксис SUBTOTAL має такий вигляд:

  Щоб включити приховані значення в діапазон, потрібно встановити для аргументу function_numзначення 2.

  Щоб виключити приховані значення в діапазоні, установіть для аргументу function_numзначення 102.

  Підрахунок на основі однієї або кількох умов

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

  Відео “Використання функцій COUNT, COUNTIF і COUNTA”

  Перегляньте відео нижче, щоб дізнатися, як за допомогою функції COUNT і функцій COUNTIF та COUNTA обчислити тільки кількість клітинок, які відповідають указаним умовам.

  Підрахунок клітинок у діапазоні за допомогою функції COUNT

  Щоб підрахувати числові значення в діапазоні, скористайтеся функцією COUNT у формулі.

  У наведеному вище прикладі клітинки A2, A3 та A6 – це єдині клітинки, які містять числові значення в діапазоні, тому результат становить 3.

  Примітка.: A7 – це значення часу, але воно містить текст (a.m.), тому функція COUNT не вважає його числовим значенням. Якщо ви повинні були видалити a.m. з клітинки функція COUNT вважатиме клітинку A7 числовим значенням, а результат буде змінено на 4.

  Підрахунок клітинок у діапазоні на основі однієї умови за допомогою функції COUNTIF

  Скористайтеся функцією COUNTIF , щоб обчислити, скільки разів певне значення відображається в діапазоні клітинок.

  Підрахунок клітинок у стовпці на основі однієї або кількох умов за допомогою функції DCOUNT

  Функція DCOUNT обчислює кількість клітинок, які містять числа в полі (стовпці) записів у списку або базі даних, які відповідають указаним умовам.

  У наведеному нижче прикладі потрібно знайти кількість місяців, включно з або пізніше березня 2016 року, на яких було продано понад 400 одиниць. У першій таблиці аркуша від A1 до B7 містяться дані про збут.

  Функція DCOUNT використовує умови для визначення місця повернення значень. Умови зазвичай вводяться в клітинки самого аркуша, а потім посилаються на ці клітинки в аргументі умови . У цьому прикладі клітинки A10 і B10 містять дві умови: одна, яка вказує на те, що повернуте значення має бути більше 400, а інше – до 31 березня 2016 року або дорівнювати 31 березня 2016 р.

  Слід використовувати такий синтаксис:

  =DCOUNT(A1:B7;”Завершення місяця”;A9:B10)

  Функція DCOUNT перевіряє дані в діапазоні від A1 до B7, застосовує умови, указані в клітинках A10 і B10, і повертає 2 – загальну кількість рядків, які відповідають обом умовам (рядки 5 і 7).

  Підрахунок клітинок у діапазоні на основі кількох умов за допомогою функції COUNTIFS

  Функція COUNTIFS подібна до функції COUNTIF з одним важливим винятком: функція COUNTIFS дає змогу застосовувати умови до клітинок у кількох діапазонах і підраховувати кількість разів, коли виконуються всі умови. З функцією COUNTIFS можна використовувати до 127 пар діапазонів і умов.

  Синтаксис функції COUNTIFS:

  COUNTIFS(діапазон_умови1; умова1; [діапазон_умови2; умова2];…)

  Див. наведений нижче приклад.

  Підрахування частоти появи за певної умови з одночасним використанням функцій COUNT та IF

  Припустімо, потрібно визначити, скільки продавців продавали конкретний товар у певному регіоні, або скільки товару більше певного обсягу продав конкретний продавець. Для цього можна скористатися функціями IF і COUNT одночасно; тобто спочатку використовується функція IF для перевірки умови, і потім, якщо результат функції IF – позитивний, функція COUNT використовується для підрахунку клітинок.

  Формули в цьому прикладі необхідно вводити як формули масивів. Якщо ви відкрили цю книгу в Програмі Excel для Windows або Excel 2016 для Mac і хочете змінити формулу або створити подібну формулу, натисніть клавішу F2, а потім натисніть клавіші Ctrl+Shift+Enter, щоб формула повернула потрібні результати. У попередніх версіях Excel для Mac використовуйте

  Підрахування частоти появи кількох текстових або числових значень з одночасним використанням функцій SUM та IF

  У прикладах нижче функції IF і SUM використовуються разом. Функція IF спочатку перевіряє значення в деяких клітинках, а потім функція SUM підсумовує значення, які пройшли перевірку з істинним результатом.

  У наведеній вище функції зазначено, якщо C2:C7 містить значення Пустовіт і Додсворт, то функція SUM має відобразити суму записів, у яких виконується умова. Формула знаходить три записи для Пустовіту та один для Додсворта в цьому діапазоні та відображає 4.

  У наведеній вище функції зазначено, якщо D2:D7 містить значення, менші за 9000 $, або більші за 19 000 грн. тоді функція SUM має відобразити суму всіх записів, у яких виконується умова. Формула знаходить два записи D3 та D5 зі значеннями, меншими за 9000 $, а потім D4 та D6 зі значеннями, більшими за 19 000 грн, і відображає результат 4.

  У наведеній вище функції зазначено, якщо D2:D7 має рахунки Пустовіт менш ніж за 9000 грн., тоді функція SUM має відобразити суму записів, у яких виконується умова. Формула знаходить, що C6 відповідає умові, і відображає 1.

  Увага!: Формули в цьому прикладі необхідно вводити як формули масивів. Це означає, що ви натискаєте клавішу F2 , а потім натискаєте клавіші Ctrl+Shift+Enter. У попередніх версіях Excel для Mac використовуйте клавіші

  Додаткові поради див. в таких статтях бази знань:

  Підрахунок клітинок у стовпці або рядку зведеної таблиці

  Зведена таблиця підсумовує дані та дає змогу аналізувати й деталізувати дані, даючи змогу вибрати категорії, за якими потрібно переглянути дані.

  Щоб швидко створити зведену таблицю, виберіть клітинку в діапазоні даних або таблиці Excel, а потім на вкладці Вставлення в групі Таблиці натисніть кнопку Зведена таблиця.

  Розгляньмо зразок сценарію роботи з електронною таблицею “Продажі”, де можна підрахувати кількість значень продажів у полях “Гольф” і “Теніс” для певних кварталів.

  Примітка.: Для інтерактивної роботи можна виконати ці кроки зі зразка даних, наведених на аркуші зведеної таблиці в книзі, доступній для завантаження.

  1. Перетягніть поле Sport до області Рядки .
  2. Перетягніть елемент Квартал до області Стовпці .
  3. Перетягніть елемент Збут до області Значення .
  4. Повторіть крок c. Ім’я поля відображається як SumofSales2 як у зведеній таблиці, так і в області значень. На цьому етапі область полів зведеної таблиці має такий вигляд:

  1. У розділі Summarize value field by (Підсумувати значення за ) виберіть Count (Кількість).
  2. У полі Настроюване ім’я змініть ім’я на Кількість.

  Зведена таблиця відображає кількість записів для гольфу та тенісу в кварталі 3 та 4 кварталі разом із показниками продажів.

  Підрахунок даних, серед яких є пусті клітинки

  Ви можете обчислити кількість клітинок, які містять або не місять дані, за допомогою функцій аркуша.

  Підрахунок непустих клітинок у діапазоні за допомогою функції COUNTA

  Використовуйте функцію COUNTA , щоб підрахувати лише клітинки в діапазоні, який містить значення.

  Іноді під час підрахунку потрібно ігнорувати пусті клітинки, якщо важливі тільки їхні значення. Наприклад, потрібно підрахувати загальну кількість продавців, які здійснили продаж (стовпець D).

  Функція COUNTA ігнорує пусті значення в клітинках D3, D4, D8 і D11 і обчислює кількість лише клітинок зі значеннями в стовпці D. Функція знаходить шість клітинок у стовпці D, що містить значення, і відображає результат 6 .

  Підрахунок непустих клітинок у списку з певними умовами за допомогою функції DCOUNTA

  Щоб підрахувати кількість непустих клітинок у стовпці записів у списку або базі даних, які відповідають указаним умовам, скористайтеся функцію DCOUNTA.

  У наведеному нижче прикладі використано функцію DCOUNTA , щоб підрахувати кількість записів у базі даних, яка міститься в діапазоні A1:B7, які відповідають умовам, указаним у діапазоні умов A9:B10. Ці умови поповідають тому, що значення “Ідентифікатор товару” має бути більшим або дорівнювати 2000, а значення “Оцінки” – більше або дорівнювати 50.

  Функція DCOUNTA знаходить два рядки, які відповідають умовам, – рядки 2 та 4, і відображає значення 2 як результат.

  Підрахунок пустих клітинок у суцільному діапазоні за допомогою функції COUNTBLANK

  Скористайтеся функцією COUNTBLANK , щоб повернути кількість пустих клітинок у суцільному діапазоні (клітинки суцільні, якщо всі вони з’єднані в незв’язаній послідовності). Якщо клітинка містить формулу, яка повертає пустий текст (“”), вона включається до підрахунку.

  Іноді під час підрахунку потрібно враховувати пусті клітинки, якщо вони важливі. У наведеному нижче прикладі електронної таблиці з продажу продуктів. припустімо, вам потрібно з’ясувати, скільки клітинок не має згаданих показників збуту.

  Примітка.: Функція аркуша COUNTBLANK забезпечує найзручніший спосіб визначення кількості пустих клітинок у діапазоні, але вона не працює дуже добре, коли клітинки, які представляють інтерес, знаходяться в закритій книзі або коли вони не утворюють суцільний діапазон. У статті бази знань XL: використання функції SUM(IF()) замість CountBlank() показано, як у таких випадках використовувати формулу масиву SUM(IF()).

  Підрахунок пустих клітинок в окремому діапазоні за допомогою поєднання функцій SUM та IF

  Використовуйте комбінацію функції SUM і IF . Загалом це можна зробити за допомогою функції IF у формулі масиву, щоб визначити, чи містить кожна клітинка, на яку посилається значення, а потім підсумувати кількість значень FALSE, повернутих формулою.

  Перегляньте кілька прикладів комбінацій функцій SUM і IF у попередньому розділі Підрахунок частоти виникнення кількох текстових або числових значень за допомогою функцій SUM і IF у цій статті.

  Підрахунок унікальних значень

  Унікальні значення в діапазоні можна підрахувати за допомогою зведеної таблиці, функції COUNTIF, функції SUM і IF або діалогового вікна Розширений фільтр .

  Підрахунок унікальних значень у стовпці списку за допомогою розширеного фільтра

  За допомогою діалогового вікна Розширений фільтр можна знаходити унікальні значення в стовпці даних. Ви можете відфільтрувати значення на місці або видобути та вставити їх у нове місце. Після цього можна обчислити кількість елементів у новому діапазоні за допомогою функції ROWS.

  Щоб скористатися розширеним фільтром, перейдіть на вкладку Дані та в групі Сортування & Фільтр натисніть кнопку Додатково.

  На знімку екрана нижче показано, як за допомогою розширеного фільтра скопіювати тільки унікальні записи в нове місце на аркуші.

  На рисунку нижче стовпець E містить значення, скопійовані з діапазону в стовпці D.

  • Якщо фільтрувати дані на місці, значення не видаляються з аркуша – може бути приховано один або кілька рядків. Натисніть кнопку Очистити в групі Сортування й фільтр на вкладці Дані, щоб знову відобразити ці значення.
  • Якщо потрібно тільки швидко переглянути кількість унікальних значень, виділіть дані, застосувавши розширений фільтр (відфільтровані або скопійовані дані), і погляньте на рядок стану. Значення Кількість у рядку стану має дорівнювати кількості унікальних значень.

  Підрахунок кількості унікальних значень у діапазоні, які відповідають одній або кільком умовам, за допомогою функцій IF, SUM, FREQUENCY, MATCH і LEN

  Скористайтеся різними поєднаннями функцій IF, SUM, FREQUENCY, MATCH і LEN.

  Докладні відомості та приклади див. в розділі “Підрахунок кількості унікальних значень за допомогою функцій” статті Підрахунок унікальних значень серед повторень.

  Особливі випадки (підрахунок усіх клітинок, кількості слів)

  За допомогою різних поєднань функцій аркуша можна обчислити кількість клітинок або слів у діапазоні.

  Підрахунок усіх клітинок у діапазоні за допомогою функцій ROWS і COLUMNS

  Припустімо, що потрібно визначити розмір великого аркуша, щоб вирішити, як здійснювати підрахунки в книзі: уручну або автоматично. Щоб обчислити кількість усіх клітинок у діапазоні, використовуйте формулу, яка перемножує значення, що повертаються, за допомогою функцій ROWS і COLUMNS . Перегляньте наведене нижче зображення, щоб отримати приклад:

  Підрахунок слів у діапазоні за допомогою комбінації функцій SUM, IF, LEN, TRIM і SUBSTITUTE

  У формулі масиву можна використовувати комбінацію функцій SUM, IF, LEN, TRIM і SUBSTITUTE . У наведеному нижче прикладі показано результат використання вкладеної формули для пошуку кількості слів у діапазоні 7 клітинок (3 з яких пусті). Деякі клітинки містять пробіли на початку або в кінці– функції TRIM і SUBSTITUTE видаляють ці зайві пробіли перед підрахунком. Див. наведений нижче приклад.

  Тепер, щоб наведена вище формула працювала правильно, потрібно зробити цю формулу масиву, інакше формула поверне #VALUE! помилку #REF!. Для цього клацніть клітинку з формулою, а потім у рядку формул натисніть клавіші Ctrl+Shift+Enter. Excel додає фігурну дужку на початку та в кінці формули, що робить її формулою масиву.

  Відображення підрахунків і кількостей у рядку стану

  Якщо виділити одну або кілька клітинок, відомості про дані в них відобразяться в рядку стану програми Excel. Наприклад, якщо на аркуші виділено чотири клітинки зі значеннями 2 й 3, текстовий рядок (наприклад, “хмара”) і значення 4, усі наведені нижче значення можуть відображатися в рядку стану одночасно: середнє значення, кількість, сума, мінімальне й максимальне значення. Клацніть правою кнопкою миші рядок стану, щоб відобразити або приховати всі чи деякі з цих значень. Ці значення показано на знімку екрана нижче.

  Потрібна додаткова довідка?

  Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.