Завантажте зразок робочої книги
Цей підручник покаже, як знайти суму текстових значень, де кожному такому текстовому значенню в Excel і Google Таблицях присвоєно унікальний код.
Номери SUM зберігаються як текст
Спочатку ми розглянемо, як підсумовувати числа, збережені або відформатовані як текст.
У наведеному нижче прикладі стовпець Зарплата зберігається у вигляді тексту. Якщо спробувати підсумувати значення, Excel відобразить нуль.
| 1 | = SUM (C3: C7) |
Натомість для виконання операції SUM над числами, збереженими у вигляді тексту, можна використати формулу масиву з функціями SUM та VALUE, як це:
| 1 | = SUM (ЗНАЧЕННЯ (C3: C7)) |
Функція VALUE перетворює текст, що представляє число, у число. Функція SUM підсумовує ці числа.
У Excel 365 та версії Excel, новішої за 2022 рік, ви можете просто ввести формулу, як звичайно. Однак під час використання Excel 2022 і раніших версій необхідно ввести формулу масиву, натиснувши CTRL + SHIFT + ENTER (замість ENTER), повідомляючи Excel, що формула є формулою масиву. Ви дізнаєтесь, що це формула масиву за фігурними дужками, що з’являються навколо формули (див. Верхнє зображення). У пізніших версіях Excel та Excel 365 можна просто натиснути ENTER замість цього.
Давайте розглянемо наступне пояснення, щоб краще зрозуміти формулу.
Функція VALUE
Використовується як формула масиву, функція VALUE перетворює весь діапазон чисел, збережених у вигляді тексту, в масив чисел і повертає його як вхідний сигнал для функції SUM.
| 1 | = SUM ({10000; 6500; 7500; 15000; 8000}) |
Щоб побачити, що повертає функція VALUE, виберіть потрібну функцію і натисніть F9.
Сума текстових значень
Для SUM діапазону текстових значень, де кожному такому текстовому значенню призначається унікальний код, можна використати формулу масиву.
У наведеній нижче таблиці записано, що люди обирають для трьох заданих питань. У таблиці праворуч наведено чотири можливі відповіді, кожна з яких отримала значення коду. Ми повинні підсумувати коди для кожної людини.
| 1 | = SUM (ІНДЕКС (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))) |
Ми пройдемо формулу нижче.
Функція MATCH
Функція MATCH шукає вказаний елемент у діапазоні і повертає його відносне положення в цьому діапазоні. Його синтаксис такий:
Будучи формулою масиву в цьому прикладі, подивимось, що повертає функція MATCH.
| 1 | = SUM (ІНДЕКС (I $ 3: I $ 6, N (IF (1, {1,3,2})))) |
Для певної особи функція MATCH знаходить взаємне положення кожної відповіді в діапазоні Н3: Н6. В результаті виходить масив позицій.
Примітка: У формулі масиву, щоб переглянути, що повертає функція, виберіть потрібну функцію і натисніть F9.
Функція IF & N
Функція IF та N, що використовуються разом, повертають наступний масив як вхід для функції INDEX.
| 1 | = SUM (ІНДЕКС (I $ 3: I $ 6, {1,3,2})) |
Тут дві функції повертають масив відносного положення відповідей у діапазоні Н3: Н6. Метою використання функцій IF та N є виконання процесу під назвою розмежування. Простими словами, дві функції змушують функцію INDEX передавати весь масив кодових значень функції SUM.
Ми пояснюємо це в наступному розділі.
Функція ІНДЕКС
Функція INDEX повертає значення, розташоване на перетині вказаного рядка та стовпця в діапазоні. Його синтаксис такий:
Давайте подивимось, як це працює як формула масиву:
| 1 | = SUM ({10,5,8}) |
Функція INDEX знаходить значення коду в діапазоні I3: I6 відповідно до зазначених номерів посад. Потім він повертає масив значень, тобто відповідний код для кожної відповіді, функції SUM для виконання операцій.
Переконайтеся, що кількість рядків і стовпців в обох Відповідь та Код стовпець однаковий.
Функція SUM
Функція SUM підсумовує значення коду, повернені функцією INDEX.
| 1 | = SUM ({10,5,8}) |
Усе це разом дає нашу вихідну формулу:
| 1 | {= SUM (ІНДЕКС (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))} |
Сума текстових значень - без функцій IF & N
У цьому розділі пояснюється, як реагує Excel, якщо ми не використовуємо IF та функцію N у вищезгаданій формулі.
Той самий приклад використовується з тими самими кодами та відповідями.
| 1 | {= SUM (ІНДЕКС (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))} |
Як бачите, функція INDEX передає лише код для першої відповіді функції SUM. Якщо ви уважно вивчите функцію ІНДЕКС, натиснувши F9 ви отримаєте наступне:
Значення! Помилка повертається, оскільки функція INDEX не може прочитати масив номерів рядків як масив. Отже, використання функції IF & N робить свою справу.
Примітка: В Excel 365 можна взагалі пропустити використання функцій IF та N.
Підсумковий текст- Таблиці Google
Ці формули працюють так само в Google Таблицях, як і в Excel.
