Сума цифр вмісту комірки в Excel

Це глава з книги Білла Джелу Гуру Excel розширюють горизонти: робіть неможливе з Microsoft Excel .

Завдання: потрібно побудувати формулу, яка повертає суму всіх цифр в рядку тексту.Наприклад, застосовуючи формулу до текстовому рядку «мені 24 роки, а моєму батькові — 43», хотілося б отримати 13 (2 + 4 + 4 + 3).

Рішення. Ви знаєте, що символи, які беруть участь в операції додавання, повинні бути цифрами від 1 до 9. Тому алгоритм підсумовування може бути наступним:

1 * кількість одиниць в рядку +

2 * кількість двійок в рядку +

… +

9 * кількість дев’яток в рядку = результат

Щоб дізнатися кількість входжень цифри (скажімо, 4), можна скористатися функцією ПІДСТАВИТИ з заміною цифри 4 на порожній рядок. Наприклад, ПІДСТАВИТИ ( текст; 4; " " ) поверне текст без усіх четвірок. У нашому прикладі — «мені 2 роки, а моєму батькові — 3». Якщо підрахувати різницю довжин вихідної і вийшла рядків, то це і буде кількість четвірок в рядку: = ДЛСТР (A1) -ДЛСТР (ПІДСТАВИТИ (A1; 4; " " )). Відповідь = 2.

Завантажити замітку в форматі Word або pdf , приклади в форматі Excel

Щоб обробити всі цифри в текстовому рядку, можна застосувати формулу масиву:
= ДЛСТР (A1) -ДЛСТР (ПІДСТАВИТИ (A1; {1; 2; 3; 4; 5; 6; 7; 8; 9}; " " )). Цей фрагмент формули поверне масив з дев’яти чисел. У нашому прикладі: {0; 1; 1; 2; 0; 0; 0; 0; 0}.Тобто, одна двійка, одна трійка і дві четвірки.

Таким чином, поверне суму творів двох масивів: = СУММ ({0; 1; 1; 2; 0; 0; 0; 0; 0} * {1; 2; 3; 4; 5; 6; 7; 8; 9}) = СУМ ({0; 2; 3; 8; 0; 0; 0; 0; 0}) = 13

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

Вивчіть наступні приклади:

приклади

Два останні приклади, мабуть, вимагають пояснень. В осередку А15 набрана формула = ПІ (). Сума цифр в осередку В15 — 77 — відображає суму цифр числа π з максимально можливою точністю. Excel розраховує для числа π «лише» 14 знаків після коми. В осередку А16 набрано час — 18:00. Якщо врахувати, що Excel зберігає дату і час, як порядкове число, то 18:00 відповідає ¾ доби. В десяткового запису — це 0,75. Ось звідки взявся результат в В16 — 12 (7 + 5)

Резюме: ви можете використовувати функції СУММ або СУММПРОИЗВ з елементами масивів, щоб підрахувати суму всіх цифр в рядку тексту.

Джерело: http://baguzin.ru/wp/?p=13135

Прокоментуйте першими

Залишити коментар

Ваш e-mail не буде публікуватись.

*