Як порахувати кількість відпрацьованих годин в Excel

0 Comments

Зміст:

Додавання або віднімання значень часу

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

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

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

Припустімо, що потрібно дізнатися, скільки годин і хвилин займе виконання двох завдань. Проаналізувавши, ви зрозуміли, що на виконання першого завдання потрібно 6 годин 45 хвилин, а на виконання другого – 9 годин 30 хвилин.

Це можна зробити на аркуші одним зі способів.

  1. Введіть 6:45 у клітинку B2 та введіть 9:30 у клітинку B3.
  2. У клітинці B4 введіть =B2+B3 , а потім натисніть клавішу Enter.

Результат – 16:15–16 годин і 15 хвилин – для виконання двох завдань.

Порада.: Проміжки часу можна також додавати за допомогою функції Автосума для підсумовування чисел. Виділіть клітинку B4, а потім на вкладці Основне натисніть кнопку Автосума. Формула матиме такий вигляд: =SUM(B2:B3). Натисніть клавішу Enter, щоб отримати такий самий результат: 16 годин 15 хвилин.

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

Щоб додати більше 24 годин:

  1. У клітинку B2 введіть 12:45, а в клітинку B3 введіть 15:30.
  2. У клітинку B4 введіть =B2+B3 й натисніть клавішу Enter.

Результат становить 28 годин 15 хвилин. Наступного разу цей формат уже буде у списку Тип.

Віднімання часу

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

Виконайте ці кроки, щоб отримати проміжок часу, який є різницею між двома значеннями часу.

  1. У клітинці B2 введіть час початку та додайте “a” для AM або p для pm, а потім натисніть клавішу Enter.
  2. У клітинці C2 введіть час завершення, зокрема “a” або “p“, а потім натисніть клавішу Enter.
  3. Введіть інший час початку й завершення для своїх подруг, Олени та Наталі.
  4. У клітинці D2 відніміть час завершення від часу початку, ввівши формулу =C2-B2, а потім натисніть клавішу Enter.

Щоб відняти час, більший за 24 години, виконайте наведені нижче дії.

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

Виконайте наведені нижче дії.

  1. Повертаючись до попереднього прикладу, виділіть клітинку B1 і перетягніть виділення на клітинку B2, щоб формат можна було застосувати до обох клітинок одночасно.
  2. У полі Формат клітинок у списку Числові формати виберіть пункт Настроювані.
  3. У полі Тип угорі списку форматів введіть dd.mm.yyyy hh:mm. Зверніть увагу на вільне місце в кінці рррр і в кінці мм. Новий формат за потреби буде доступний у списку Тип.
  4. У клітинці B1 введіть дату початку, зокрема день, місяць, рік і час.

У результаті отримаємо 31,5 годин.

Примітка.: У Інтернет-версія Excel можна додавати та віднімати більше 24 годин, але застосувати настроюваний числовий формат не можна.

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

Припустімо, потрібно дізнатися, скільки годин і хвилин триватиме виконання двох завдань. Ви вважаєте, що для першого завдання знадобиться 6 годин 45 хвилин, а для другого – 9 годин 30 хвилин.

  1. У клітинку B2 введіть 6:45, а в клітинку B3 введіть 9:30.
  2. У клітинку B4 введіть =B2+B3 й натисніть клавішу Enter.

Для виконання двох завдань потрібно 16 годин 15 хвилин.

Порада.: Щоб додавати проміжки часу, можна також використовувати функцію “Автосума”. Клацніть клітинку B4. Потім на вкладці Основне натисніть кнопку Автосума. Формула виглядатиме так: =SUM(B2:B3). Натисніть клавішу Enter, щоб отримати результат (16 годин 15 хвилин).

Віднімання часу

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

  1. У клітинці B2 введіть час початку, пробіл, потім введіть a для часу до опівдня або p для часу після опівдня та натисніть клавішу Enter. У клітинці C2 введіть час завершення, включивши, відповідно, літери a або p, і натисніть клавішу Enter. Введіть інший час початку й завершення для своїх друзів.
  2. У клітинці D2 відніміть час завершення від часу початку, ввівши формулу =C2-B2 та натиснувши клавішу Enter. Тепер можна побачити, що Роман працював 3 години 45 хвилин.

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

Підрахунок – це невід’ємна складова аналізу будь-яких даних, наприклад кількості працівників організації або одиниць товару, проданого за кожен квартал. У програмі 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. Введіть наведені нижче дані в електронну таблицю 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 чи отримати підтримку в спільнотах.