Фильтр и Сводная таблица

Фильтр и Сводная таблица

Поводом к написанию этой статьи послужил один курьёзный случай. На днях показал знакомому свои видео-уроки по 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 исчезло слово «закупки» и осталось просто «Сумма». Введите в этом поле исчезнувшее слово «закупки». Установите указатель на кнопку «ОК» и один раз щёлкните левой кнопкой мыши.

Теперь таблица имеет такой вид

Аналогичным образом установите значение «Сумма» для столбца «Сумма реализации».

Теперь запомните очень важный нюанс. Если сейчас вы внесёте изменения в исходную таблицу, они не будут отображаться в сводной таблице автоматически. Её нужно обновить. Установите указатель в сводной таблице, один раз щёлкните ПРАВОЙ кнопкой мыши и в появившемся списке выберите пункт «Обновить». И вот теперь данные будут точными.

Конечно же, создание сводных таблиц довольно кропотливый процесс. Но, согласитесь, оно того стоит.

Теперь у вас на вооружении есть два очень мощных инструмента. Фильтр и Сводные таблицы. Осталось только как следует разобраться, как ими пользоваться.

 

Практическое задание «Создание таблицы учёта товаров» Часть 4

20. Заполните таблицу значениями. Вы можете использовать произвольные данные. 

 

В заголовке таблицы в правом нижнем углу каждой ячейки расположен квадратик со стрелкой, указывающей вниз. Это результат выбора автофильтра

Автофильтр – очень полезная возможность, предоставляемая программой для поиска и выбора нужных данных.

 

Установите указатель мыши на любую ячейку внутри таблицы и один раз щёлкните левой кнопкой. Самый простой вариант воспользоваться комбинацией клавиш

Ctrl + Shift + L

Нажмите на клавиатуре клавишу <Ctrl>, удерживая её нажатой, нажмите клавишу <Shift> и удерживайте её нажатой. Нажмите клавишу с английской буквой  <L> (русская «Д»). Отпустите клавиши <Ctrl> и <Shift>.

МЫ настоятельно рекомендуем вам запомнить эту комбинацию, потому что возможности автофильтра поражают. На случай если вы случайно забудете комбинацию клавиш на клавиатуре, давайте рассмотрим, как установить автофильтр с помощью Ленты.

 

На Ленте установите указатель на надпись «Данные» и один раз щёлкните левой кнопкой мыши. Установите указатель на надпись «Фильтр».

Обратите внимание. Программа подсказывает нам нужную комбинацию клавиш. Один раз щёлкните левой кнопкой мыши.

Из таблицы мы можем получить следующие данные:

1. Всего закуплено товаров на 3477,40 (ячейка Е3).
2. Всего продано товаров на 1523 (ячейка F3).
3. Суммарная торговая наценка составила 598,70 (ячейка Н3).

Фактически это были бы все данные, которые мы могли бы взять из таблицы, если бы не было автофильтра. С его помощью можно найти любую интересующую нас информацию. Важно изучить его возможности и понять логику поиска нужной информации.

Как пользоваться автофильтром?

Признаюсь честно. В примерах в данном разделе показаны варианты автофильтра, сделанные ещё в программе версии 2003-го года. Просто материал хорошо разобран и не хочется переделывать.

После выбора автофильтра в правом нижнем углу каждой ячейки заголовка таблицы расположен квадратик со стрелкой, указывающей вниз. Это значок раскрывающегося списка. 

 

Установите указатель мыши на квадратик в ячейке С2

(Наименование товара) и один раз щёлкните левой кнопкой.

 

Вот так автофильтр будет представлен в версии 2010 года. Что-то здесь перемудрили.

Давайте посмотрим сколько всего товара В мы закупили и продали.

Раньше достаточно было установить указатель на пункт «Товар В» и один раз щёлкнуть левой кнопкой мыши (смотри рисунок выше). В новой версии нам необходимо убрать галочки с ненужных пунктов.

Установите указатель на пункт «(Выделить все)» и один раз щёлкните левой кнопкой мыши. Все галочки будут убраны. Теперь установите указатель на нужный пункт и один раз щёлкните левой кнопкой мыши. Он будет выделен галочкой. Установите указатель на кнопку «ОК» и один раз щёлкните левой кнопкой мыши.

Видимо при создании программы программисту платили за лишние щелчки при работе с автофильтром. По-другому я эти лишние движения объяснить не могу.

На рисунке представлен результат выборки по наименованию товара В. Для простоты, мы рассматриваем абстрактный пример. Но вы уже можете использовать данные вашей таблицы.

 

Какие данные мы можем получить из этой выборки?

1. В столбце «Наименование товара» мы видим, что товара В у нас было закуплено всего 7 единиц. Из них продано 2 единицы.
2. В столбце «Цена закупки» выделите все ячейки, содержащие цифры. В строке состояния в нижней части окна программы автоматически подсчитывается сумма выделенных значений. 

  

Всего было закуплено на общую сумму 1409,10.

 

1. Чтобы узнать на какую сумму продано товара В, выделите ячейки, содержащие цифры в столбце «Цена продажи». Результат будет в строке состояния как в предыдущем пункте.
2. Чтобы узнать торговую наценку по проданному товару В, выделите ячейки в столбце «Торговая наценка». Результат будет в строке состояния
3. В столбце «Дата закупки» мы видим все даты, когда закупался товар и можем проверить правильность учёта поступления товара по накладным.
4. В столбце «Дата продажи» можно отследить даты реализации и сверить с данными первичного учёта на торговом месте.
5. Давайте посмотрим сколько товара В закуплено 11.08.08. Мы специально взяли прошлые данные, чтобы к нам не было претензий со стороны налоговой службы. Ну, чтобы вас не шокировать, будем считать, что это ещё дореволюционные данные за 11.08.1908 г. В уже отсортированном списке по товару В

  

Установите указатель мыши на квадратик автофильтра в ячейке В2 (Дата закупки) и один раз щёлкните левой кнопкой мыши. 

В раскрывшемся списке установите указатель мыши на пункт 11.08.08 и один раз щёлкните левой кнопкой.

 

На рисунке представлен вариант поиска по условию сколько Товара В было продано 11.08.08 г.

Ячейки, в которых использовался автофильтр, содержат квадратики автофильтра со стрелочкой синего цвета. Это позволяет легко узнать, в каких столбцах содержатся условия поиска. 

Чтобы вернуться к изначальной таблице можно просто убрать автофильтр с помощью комбинации клавиш

Ctrl + Shift + L

А затем при необходимости повторно нажать эту комбинацию.

В моих учётных таблицах автофильтр присутствует всегда. Это позволяет очень быстро находить нужную информацию.

Давайте ещё рассмотрим возможности использования автофильтра.

Как можно вывести на экран остаток товара, используя автофильтр? Установите указатель мыши на квадратик автофильтра в ячейке Е2 (Цена продажи) и один раз щёлкните левой кнопкой. 

В появившемся списке выберите пункт «Пустые». В таблице появятся все ячейки, в которых нет цены продажи, т.е. остаток товара.

Для того чтобы вывести весь проданный товар, необходимо в списке автофильтра выбрать пункт «Непустые».

Запомните этот приём использования автофильтра.

Мы уже рассмотрели очень большие возможности автофильтра. Однако это ещё не всё.

Программа предлагает нам уникальную возможность поиска данных по условию.  

Установите указатель мыши на квадратик автофильтра в ячейке С2 (Наименование товара) и один раз щёлкните левой кнопкой. 

 

 

В появившемся списке установите указатель мыши на пункт «Текстовые фильтры». На экране появится список.

Вы можете выбрать один из предлагаемых вариантов.

Например, установите указатель на пункт «содержит…» и один раз щёлкните левой кнопкой мыши. Рассмотрим пример поиска товара, в названии которого присутствует буква «Б». Вы можете использовать любой другой пример.

 

Установите указатель в поле, указанном на рисунке стрелочкой и один раз щёлкните левой кнопкой мыши. Введите нужное значение. В этом окне мы можем задавать два условия.

Установите указатель на кнопку «ОК» и один раз щёлкните левой кнопкой мыши.

В окне программы будут представлены только те строки, которые соответствуют условию.

Кроме этого программа предлагает воспользоваться возможностями пользовательского автофильтра.

В списке текстовых фильтров установите указатель на пункт «Настраиваемый фильтр» и один раз щёлкните левой кнопкой мыши.

На экране появится окно "Пользовательский автофильтр".

 

В этом окне всего 4 поля с раскрывающимися списками. Вам уже знаком квадратик со стрелкой в правой части поля. Щелчок левой кнопкой мыши на квадратике открывает список, в котором мы можем выбрать нужный вариант.

В левом поле выбирается условие поиска, в правом выбираются значения из предложенного списка или вводятся вручную. После завершения выбора установите указатель мыши на кнопку "ОК" и один раз щёлкните левой кнопкой мыши.

Скорее всего, вы будете пользоваться только верхними полями (одно условие). Нижние поля используются для задания дополнительного условия поиска (два условия). Между полями расположены переключатели И и ИЛИ. Возможен только один из предложенных вариантов. В примере, указанном на рисунке, слева от надписи И в кружочке стоит точка, это означает что выбрана функция И. Будут показаны только те строки, которые соответствуют обоим заданным условиям одновременно. В случае использования функции ИЛИ достаточно выполнения одного из условий.

На рисунке представлены возможные варианты условий.

Всё очень понятно.

Допустим, вы продаёте джинсы, спортивные костюмы и куртки. Давайте рассмотрим, как задать условие поиска всех джинсов. 

 

Внимательно изучите варианты возможных условий и подумайте, как вы можете их использовать для организации собственного учёта.

Давайте рассмотрим, как можно выбрать из таблицы продажи за январь 2007 г.

 

Программа предоставляет нам очень мощные инструменты для организации учёта и анализа данных и принятия правильных управленческих решений. Но программа только инструмент. Эффективность вашего учёта будет зависеть от разработанной вами системы учёта и степени её детализации. От того, как умело вы будете использовать предоставленные инструменты, знать их возможности и предназначение.