Фильтр и Сводная таблица
Поводом к написанию этой статьи послужил один курьёзный
случай. На днях показал знакомому свои видео-уроки по Microsoft Excel 2010.
Случайно попал на раздел, в котором описывается работа с фильтром. Его
восхищению не было предела. Оказывается, он уже 3 года активно работает с этой
программой и не знал о такой уникальной возможности. Он просто чертил таблицы.
Давайте
рассмотрим ряд инструментов, без которых
мощнейший Excel становится просто мощным калькулятором с возможностью
быстро рисовать таблицы. Речь идёт о Фильтре и Сводных таблицах. Лучше всего
рассмотреть конкретный пример.
Рис. 1 Таблица учёта
товара
Какие данные мы можем получить из таблицы, представленной на
рисунке 1?. Всего закуплено товара на 1100 единиц. Реализовано на 750. Торговая
наценка – 250 (цена продажи минус цена закупки). И всё? Печально…
Сейчас мы совершим несколько магических щелчков мышкой и
наша таблица раскроет нам много чего интересного. Один раз щёлкните левой
кнопкой мыши на любой ячейке внутри таблицы. Мы воспользуемся Фильтром для
анализа данных.
Рис. 2 Включение
Фильтра
На рисунке 2 показан процесс включения фильтра. На Ленте
установите указатель на надпись «Данные»
и один раз щёлкните левой кнопкой мыши. Установите указатель на надпись «Фильтр» и один раз щёлкните левой
кнопкой мыши. Обратите внимание. Для включения фильтра можно воспользоваться
комбинацией клавиш
Ctrl + Shift + L
Это очень удобно. Главное запомнить эту комбинацию.
После включения Фильтра таблица будет иметь следующий вид.
Рис. 3 Включённый Фильтр
В шапке таблицы в каждой ячейке в правом нижнем углу
появилась раскрывающая кнопка в виде квадратика со стрелочкой, указывающей
вниз. Это и есть Фильтр. С его помощью вы можете делать выборки из массива
данных по заданным критериям. Например, давайте посмотрим, сколько всего
закуплено Товара А.
Установите
указатель на раскрывающую кнопку, указанную стрелочкой 1 и один раз щёлкните
левой кнопкой мыши. И вот здесь нас ждут чудеса. В версии программы 2003-го
года можно было просто установить указатель на пункт «Товар А» и один раз щёлкнуть левой кнопкой мыши. В последующих
версиях нам предлагают выполнить лишнюю работу. Установите указатель на надпись
«Выделить все» и один раз щёлкните
левой кнопкой мыши. Все галочки будут убраны. Теперь установите указатель на
надпись «Товар А» (стрелочка 3) и
один раз щёлкните левой кнопкой мыши.
Слева от надписи появится галочка. Установите указатель на
кнопку «ОК» и один раз щёлкните
левой кнопкой мыши.
В нашем примере таблица принимает следующий вид
Рис. Результат применения Фильтра
Отображаются данные
только по товару А. Для того чтобы узнать сколько всего закуплено Товара А,
выделите все значения в столбце «Цена
закупки». В нижней части в строке состояния указанна сумма – 400,
количество – 4 и Среднее – 100,00. Т.е. всего Товара А закуплено на 400. В
столбце «Дата закупки» вы можете
посмотреть, когда закупался товар. В столбце «Дата реализации» мы можем узнать, когда реализовывался товар. Для
того чтобы узнать на какую сумму уже реализовано товара, выделите все значения
в столбце «Цена реализации» и
посмотрите значение в строке состояния. Аналогичным образом можно узнать
реализованную торговую наценку.
Для того чтобы вернуться к исходной таблице, нажмите
комбинацию клавиш
Ctrl + Shift + L
Таблица вернётся к исходному виду. Повторное нажатие этой
комбинации клавиш включит Фильтр. Вы можете выбирать не только конкретные
значения, но и задавать условия поиска.
Рис. Возможности сортировки по наименованию товара
На рисунке представлены варианты фильтрации по наименованию
товар. Во-первых, вы можете сортировать значения не только по возрастанию и убыванию, но и по цвету. Во-вторых, вы
можете воспользоваться текстовыми фильтрами. На рисунке представлены варианты
текстовых фильтров. Например, вы можете сделать выборку товаров, название
которых начинается на какую-то букву, заканчивается на какое-то значение,
содержит или не содержит какие-то значения. Чтобы было понятно, давайте
рассмотрим пример поиска товара, название которого заканчивается на букву «В».
Установите указатель на надпись «заканчивается на» и один раз щёлкните левой кнопкой мыши.
Рис. Окно пользовательского автофильтра.
На экране появится окно пользовательского автофильтра. В
версии программы 2003-го года фильтр назывался автофильтром. В поле ввода,
указанном на рисунке стрелочкой, введите значение «В». Таким образом автофильтр найдёт все товары, наименование
которых заканчивается на букву «В».
В нашем примере этому критерию соответствует только «Товар В». Установите указатель на кнопку «ОК» и один раз щёлкните левой кнопкой мыши. В таблице будут
представлены данные по Товару В.
Для возврата к исходной таблице нажмите уже знакомую вам
комбинацию клавиш.
Огромный выбор представляет фильтр по дате. Можно выбирать
значения в каком-то диапазоне дат. Думаю, вы сможете разобраться, как задавать
нужные условия поиска.
Мы же рассмотрим очень важный момент при создании таблиц. Мы
использовали Фильтр для выборки по Товару А. Выделив значения, мы получили
сумму закупки. А представляете, если бы у нас было несколько сотен наименований
товаров.
И вот здесь может очень помочь сводная таблица. В ней уже
дана чёткая раскладка по каждому товару.
Создание сводных таблиц требует определённых навыков.
Давайте пошагово рассмотрим пример создания сводной таблицы, представленной на
рисунке.
Перед созданием сводной
таблицы нам необходимо выделить весь лист. Установите указатель на
прямоугольник, указанный на рисунке стрелочкой и один раз щёлкните левой
кнопкой мыши. В ходе создания сводной таблицы указывается диапазон, по которому
строится таблица. Если перед созданием сводной таблицы выделить весь лист, мы
сможем в случае необходимости добавлять в исходную таблицу строки и столбцы, и
они будут подсчитываться в сводной таблице.
На Ленте установите
указатель на надпись «Вставка» и
один раз щёлкните левой кнопкой мыши.
Установите указатель на изображение прямо над
надписью «Сводная таблица» и один
раз щёлкните левой кнопкой мыши.
Появится окно «Создание
сводной таблицы». Так как мы предварительно выделили весь лист в поле «Таблица и диапазон:» указано
нужное значение. Мы будем строить таблицу на отдельном (новом) листе, поэтому в
данном окне ничего не нужно изменять. Установите указатель на кнопку «ОК» и один раз щёлкните левой кнопкой
мыши.
На новом листе появится такой вид. Установите указатель на
надпись «Наименование товара» в
правой части листа (стрелочка 1), нажмите левую кнопку мыши и, удерживая её
нажатой, переместите в поле «Названия
строк». Отпустите левую кнопку мыши. Аналогичным образом переместите
значения «Ценазакупки» и «Ценареализации» в поле «∑
Значения».
Таблица будет иметь такой вид.
Прежде всего, давайте уберём строку «(пусто)». Она искажает расчёт.
1. Установите
указатель на раскрывающую кнопку в правой части от надписи «Названия строк» и один раз щёлкните
левой кнопкой мыши.
2. В
моём случае понадобилось воспользоваться полосой прокрутки (стрелочка 2), чтобы
в окошке появилось значение «(пусто)».
3. Установите
на него указатель и один раз щёлкните левой кнопкой мыши. Галочка слева от
пункта исчезнет.
Установите указатель на кнопку «ОК» и один раз щёлкните левой кнопкой мыши. Пустое значение
исчезнет из сводной таблицы.
Установите указатель в ячейку со значением «Количество по полю Ценазакупки» и один
раз щёлкните левой кнопкой мыши. Наберите на клавиатуре «Сумма закупки».
Аналогичным образом в ячейке «Количество по полю Цена реализации» введите «Сумма реализации». В ячейке «Названия
строк» введите «Наименование товара».
Сейчас наша таблица выглядит следующим образом. 
В столбцах пока подсчитывается количество, а не сумма.
Сейчас мы это исправим.
В столбце «Сумма
закупки» установите указатель на любое значение и один раз щёлкните ПРАВОЙ
кнопкой мыши.
В появившемся списке установите указатель на пункт «Параметры полей значений» и один раз
щёлкните левой кнопкой мыши.
В появившемся окне «Параметры
поля значений» установите указатель на надпись «Сумма» (стрелочка 1) и один раз щёлкните левой кнопкой мыши.
После щелчка на надписи «Сумма» в моём случае в поле, указанном на рисунке стрелочкой 2
исчезло слово «закупки» и осталось
просто «Сумма». Введите в этом поле
исчезнувшее слово «закупки».
Установите указатель на кнопку «ОК»
и один раз щёлкните левой кнопкой мыши.
Теперь таблица имеет такой вид
Аналогичным образом установите значение «Сумма» для столбца «Сумма реализации».
Теперь запомните очень важный нюанс. Если сейчас вы внесёте
изменения в исходную таблицу, они не будут отображаться в сводной таблице
автоматически. Её нужно обновить. Установите указатель в сводной таблице, один
раз щёлкните ПРАВОЙ кнопкой мыши и в появившемся списке выберите пункт «Обновить». И вот теперь данные будут
точными.
Конечно же, создание сводных таблиц довольно кропотливый
процесс. Но, согласитесь, оно того стоит.
Теперь у вас на вооружении есть два очень мощных
инструмента. Фильтр и Сводные таблицы. Осталось только как следует разобраться,
как ими пользоваться.
