Один из способов обработки и анализа базы данных состоит в подведении различных итогов. Итоги – это быстрый способ вставки функций в определенные столбцы таблицы с группировкой данных в столбце, по которому подводятся итоги.
В любой версии Excel нет автоматического оформления итоговых строк. Заниматься ручным оформлением – это не эффективно. Решим эту задачу без применения макросов.
Для решения этой задачи будем использовать условное форматирование.
Выделим столбцы таблицы с A по F и на вкладке Главная [Home], в группе Стили [Styles], раскрыв кнопку Условное форматирование [Conditional Formatting], выберем команду Создать правило [New Rule] и в появившемся окне – Использовать формулу для определения форматируемых ячеек [Use a formula to determine which cells…]
Напишем формулу =ПРАВСИМВ($A1;4)=”Итог” или =RIGHT($A1;5)=”Total”, выберем оформление – кнопка Формат [Format], OK.
Привязка в формуле идет к ячейке A1, т.к. выделение таблицы было выполнено столбцами целиком и итог в таблице был подведен по данным столбца A. Это позволит в дальнейшем при изменении количества записей (строк) в таблице добавлять итоги и уже не думать об их внешнем виде. Результат:
При использовании в промежуточных итогах других функций необходимо будет по аналогии прописать формулы, например: =ПРАВСИМВ($A1;7)=”Среднее” или =RIGHT($A1;7)=”Average”
Однако, если итоги будут подводится не только по первому столбцу A, но и по второму B, то можно прописать формулу следующего вида: =ИЛИ(ПРАВСИМВ($A1;4)=”Итог”;ПРАВСИМВ($B1;4)=”Итог”) или =OR(RIGHT($A1;5)=”Total”; RIGHT($B1;5)=”Total”). При увеличении количества столбцов прописать формулы по аналогии.
Как быстро изменить значение в ячейке на или во сколько-то раз?
Например, есть исходные данные и, что самое не приятное, их может быть очень много. Понадобилось быстро увеличить цену по всем наименованиям на 20%.
Эту задачу можно решить двумя способами.
ПЕРВЫЙ СПОСОБ:
- Сделать вспомогательное вычисление в соседнем столбце, написав в ячейке E2 формулу: =D2*1,2, затем скопировать формулу на весь диапазон ячеек E2:E15
- Выделить ячейки E2:E15, выполнить команду Копировать [Copy], затем выделить ячейки D2:D25 и выполнить команду Специальная вставка [Paste Special], поставить переключатель в положение значения [Values].
- Удалить данные из вспомогательного столбца.
С одной стороны способ простой, но требует написания формулы, дважды копирования и последующего удаления данных.
ВТОРОЙ СПОСОБ:
- Ввести нужное значение в любую свободную ячейку на листе (если среди исходных данных на листе нет нужного значения), например в ячейку E1. Выполнить команду Копировать [Copy]
- Выделить ячейки D2:D25 и в контекстном меню выбрать команду Специальная вставка [Paste Special] или нажать клавиши Alt+Ctrl+V, поставить переключатель значения [Values] и умножить [Multiply], OK.
- Удалить введенный коэффициент в ячейку E1.
Результат:
Понятно, что конечные результаты в каждом способе будут абсолютно одинаковыми – здесь вопрос выбора пользователя. Я предпочитаю больше второй способ.
В результате обработки данных (например, консолидации со связью) может возникнуть необходимость быстро заполнить пустые ячейки значениями, которые по отношению к заполняемым могут находится как выше так и ниже.
Обычно, можно встретить решения с помощью специального выделения пустых ячеек, но можно поступить и проще – воспользоваться обычным автофильтром.
В исходной таблице установить Автофильтр и в столбце Наименование установить условии отбора данные – Пустые [Blanks].
Выделить первую пустую ячейку (или все пустые ячейки) и ввести с клавиатуры знак =. Так как подписи данных в примере находятся ниже по отношению к заполняемым ячейкам, то выбрать ОДНУ ячейку ниже (по отношению к знаку равно).
Если была выделена только одна ячейка, то завершить формулу, а затем скопировать на ниже расположенные. Если были выделены все пустые ячейки, то завершить ввод формулы нужно нажатием на клавиши Ctrl+Enter – это завершение ввода во все выделенные ячейки.
Остается только снять условие фильтрации по столбцу Наименование и заполнение пустых ячеек выполнено!
Для дальнейшей обработки данных (фильтрация, сортировка и т.д.), необходимо все формулы в столбце Наименования заменить на значения.