Формулы в итогах

Переделка значения суммы в формулу

Разбираем различные задачи из реальной практики в Microsoft Excel и находим успешные решения. К примеру вложен файл с образцом, его можно скачать по кнопке. Excel-II: Большие таблицы

В этом примере поговорим о ситуации, когда необходимо все значения заменить формулами. Такая таблица может быть выгружена из базы данных и когда Вам необходимо поменять пару-тройку значений в наборе данных, то итоги автоматически не пересчитываются – потому что они стоят значениями. Сейчас разберемся, как выполнить замену таких значений на формулы, минимизируя временные затраты и исключая возможные ошибки. Базовая таблица приведена на рисунке ниже:

Как показывает опыт, такие таблицы выгружаются из баз данных в некотором цветовом решении и как минимум итоговые строки будут выделены цветной заливкой
Алгоритм дальнейших действий расписан ниже.
Файл: СКАЧАТЬ

 

Лист ШАГ 1 прикрепленного файла

Лист ШАГ 2 прикрепленного файла

Лист ШАГ 3 прикрепленного файла – нужно продублировать название раздела в каждую пустую ячейку

Лист ШАГ 4 прикрепленного файла

Лист ШАГ 5 прикрепленного файла
Теперь можно подводить автоматические итоги – выбираем любую ячейку таблицы и выполняем команду Дата – Структура – Промежуточный итог и в открывшемся окне выполняем настройки: при каждом изменении – Столбец А (наш дополнительный), операцияСумма , Итоги поСумма (отметьте названия всех столбцов, в которых должны быть формулы) и нажмите ОК

Лист ШАГ 6 прикрепленного файла
Дальше нужно затащить названия итогов обратно в основной столбец – фильтруем таблицу по столбцу B (пустые строки), выделяем из все и в первую вводим формулу =ЛЕВСИМВ(A9;ДЛСТР(A9)-6) , завершаем ввод нажатием Ctrl+Enter

Функция отберет знаки из ячейки в указанном количестве, которое мы считаем как разницу между общим числом знаков (функция ДЛСТР ) и количеством ненужных букв в конце (у меня 6 – пробел + количество букв слова Total , в русскоязычной версии нужно будет отнять 5 – это пробел + количество букв слова Итог )
Узнать больше о возможностях функций для работы с текстом можно на курсе "PRO ФУНКЦИИ"


Лист ШАГ 7 прикрепленного файла
Теперь снимаем фильтр, сохраняем значениями столбец B , удаляем ставший ненужным столбец A и оформляем новые итоговые строки.