Как сделать в экселе фильтрацию


Как сделать в экселе фильтрацию

Как сделать в экселе фильтрацию

Как сделать в экселе фильтрацию



Многие офисные пользователи зачастую сталкиваются с целым рядом проблем при попытке создания и редактирования каких-либо офисных документов. Зачастую связано это с тем, что в компаниях используется несколько офисных программ, принципы работы в которых могут сильно различаться. Особенно много затруднений может создать сводная таблица Excel.

сводная таблица excelК счастью, сравнительно недавно появился MS Office 2010-2013, который не только включает в себя ряд обновленных программ для обработки текстовых файлов, таблиц, баз данных и презентаций, но и позволяет работать с ними одновременно нескольким работникам, что в корпоративной среде просто бесценно.

Почему именно новые версии?

Чтобы разобраться в этом вопросе, нужно представлять себе все наиболее значимые изменения, которые произошли с этим офисным пакетом.

Как и раньше, именно Excel является второй по востребованности программой, которая позволяет не только создавать простые таблицы, но даже создавать довольно сложные базы данных. Как и в прочие компоненты, в него была добавлена кнопка «Офис», нажав на которую вы можете сохранить документ в требуемом вам формате, изменить его или указать требуемые меры по его защите. Был значительно обновлен модуль защиты документа.

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

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

Что это такое и для чего она нужна?

Наиболее значимым изменением в новых версиях «Офиса» является их полностью перекроенный интерфейс, который был назван создателями Ribbon (лента). В лентах все 1500 команд удобно сгруппированы по категориям, а потому вам не придется их долго искать. Чтобы полностью соответствовать этим параметрам, разработчики добавили в Excel еще и улучшенные сводные таблицы.

создание сводных таблиц в excelСегодня рассмотрим Excel «для чайников». Сводные таблицы – это специальный инструмент, в котором наглядно группируются результаты какого-нибудь процесса. Проще говоря, с их помощью можно увидеть, сколько тех или иных товаров продал каждый продавец за свою рабочую смену. Кроме того, их используют в тех случаях, когда необходимо:

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

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

Создаем нужный документ

Так как создать сводную таблицу в Excel? Сперва нужно составить простой документ, в который мы вносим данные для их последующего анализа. На один столбец должен приходиться один параметр. Возьмем простейший пример:

  • Время продажи.
  • Проданный товар.
  • Стоимость всего сбыта.

Таким образом, между всеми параметрами в каждом конкретном столбце образуется связь: предположим, что кроссовки были проданы в 9 часов утра, причем прибыль составила n-рублей.

Форматирование таблицы

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

  • Если вы сразу же нажмете на кнопку «ОК», то сводная таблица Excel сразу же будет выведена на отдельный лист.
  • Полностью настроить выведение.

В последнем случае разработчики дают нам возможность определить диапазон ячеек, в который будет выводиться искомая информация. После этого пользователь должен определить, где конкретно будет создана новая таблица: на уже существующем или же на вновь созданном листе. Нажав «ОК», вы тут же увидите перед собой готовую таблицу. На этом создание сводных таблиц в Excel практически закончено.

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

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

Создание и группировка временных рядов

Чтобы проанализировать продажи по конкретным временным отрезкам, необходимо вставить соответствующие пункты в саму таблицу. Для этого необходимо перейти в лист «Данные» и после этого вставить три новых столбика сразу после даты. Выделяем столбец с после чего жмем на кнопку «Вставить».

Очень важно, дабы все вновь создаваемые столбцы находились внутри уже существующей таблицы с исходными данными. В этом случае создание сводных таблиц в Excel заново не потребуется. Вы просто будете добавлять новые поля с требуемыми вам данными.

Используемые формулы

создать сводную таблицу в excelДля примера вновь созданные столбцы можно назвать «Год», «Месяц», «Месяцы-Годы». Чтобы получить интересующие нас данные, в каждый из них придется прописать отдельную формулу для расчетов:

  • В «Годовой» вставляем формулу вида: «=ГОД» (ссылаясь при этом на дату).
  • Месяц нужно дополнить выражением: «=МЕСЯЦ» (также со ссылкой на дату).
  • В третий столбик вставляем формулу вида: «=СЦЕПИТЬ» (ссылаясь при этом на год и на месяц).

Соответственно, получаем три столбика со всеми исходными данными. Сейчас необходимо перейти в меню «Сводная», щелкаем по любому свободному месту в таблице правой клавишей мыши, после чего в открывшемся выбираем пункт «Обновить». Обратите внимание! Внимательно пишите формулы в сводных таблицах Excel, так как любая ошибка приведет к неточности прогноза.

Снова зажимаем левой клавишей мыши пункт «Год», после чего тащим его в «Название столбцов», после чего сводная таблица начинает отображать результаты продажи конкретного вида товара по всем годам. А если необходимо так же проанализировать месячный сбыт? Точно так же зажимаем ЛКМ «Месяц», перетаскивая его под годовой столбец.

Чтобы получить наглядную динамику месячных изменений продаж по годам, поле «Месяц» нужно перетащить прямо в годовой столбец, ранее уже перемещенный в «Название столбцов». Выполнив эту операцию, вы увидите таблицу в следующем представлении:

  • Сумма продаж конкретного товара за весь год.
  • Динамику продаж каждого из них по годам.

excel работа со сводными таблицами

Убираем данные из выдачи

Предположим, что из выдачи нам нужно убрать данные по октябрю, так как у нас нет полной статистики по нему. В сводной таблице ищем «Фильтр отчета», после чего перетаскиваем на него «Год – месяц».

Над таблицей появляется фильтр, в котором нужно поставить флажок напротив пункта «Выделить несколько элементов». После этого в появившемся списке снимаем флажок с октября, после чего нажимаем на «ОК».

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

Рассчитываем прогнозы

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

Чтобы сделать это, ставим курсор в поле «Общий итог», после чего кликаем по кнопке «Удалить общий итог». После этого выполняем сводной таблицы Excel способом, описанным нами выше (жмем на правую клавишу мыши, выбираем «Обновить»).

В результате эти данные пропадают из нашей сводной таблицы. Чтобы построить наглядное отображение прогнозов по продажам, ставим курсор на тот год, от которого планируем отталкиваться, после чего кликаем левой клавишей мыши по кнопке «График Модель прогноза».

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

Используем прочие источники данных для формирования сводной таблицы

Пора рассмотреть более сложные уроки Excel. Сводная таблица может быть создана на основе документов, которые были отредактированы другими пользователями.

В этом нам поможет функция «Слияние». Эта программа позволяет автоматически объединить текст документа и необходимые адреса, имена или другую необходимую информацию для создания персонализированных прогнозов, что в нашем случае и требуется.

Функция слияния в поздних версиях «Офиса» практически не изменилась в сравнении с Office 2003. Тем не менее запросы к базам данных для выбора источника данных могут выполняться с применением Microsoft Query. Чтобы получить данные сводной таблицы Excel, необходимо наличие двух объектов:

  • Основной документ, содержащий нужную вам информацию, а также поля, указывающие программе Excel, какие данные следует вставлять в документ. Основная таблица является стандартным документом табличного процессора, однако необходимо указать в окне диалога Слияние (Mail Merge Helper), что именно он должен использоваться как основной.
  • Источник данных, содержащий ту информацию, которая необходима для составления прогноза. Можно создать новую таблицу тем способом, который мы указывали выше.

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

Стоит только кликнуть по нужному вам полю левой клавишей мыши, и вы сможете посмотреть данные источника вместо имен полей, которые отображаются в основном документе. Поля можно вставлять в любое место документа.

Используемые категории

уроки excel сводная таблица Также здесь можно использовать категорию ASK, что значит «запросить», и FILLIN, что значит «заполнить», NEXT и NEXTIF, ELSE. Также можно произвести настройку параметров тех пунктов прогноза, которые будут отображены в готовой таблице. Найти необходимую запись можно при помощи специального фильтра или же путем простой сортировки данных, о чем мы уже говорили выше.

Когда все готово для создания составного документа, окно диалога Merge предоставляет пользователю возможность выявить и устранить отдельные ошибки до их появления. Когда все проверено, то можно смело жать на кнопку «Объединить». Если же слияние должно производиться через электронную почту, то выйдет запрос на установку почтовой программы (если у вас она не установлена).

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

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


Источник: http://fb.ru/article/137691/svodnaya-tablitsa-excel-kak-sozdat-i-rabotat-rabota-so-svodnyimi-tablitsami-excel


Как сделать в экселе фильтрацию фото


Как сделать в экселе фильтрацию

Как сделать в экселе фильтрацию

Как сделать в экселе фильтрацию

Как сделать в экселе фильтрацию

Как сделать в экселе фильтрацию

Как сделать в экселе фильтрацию

Как сделать в экселе фильтрацию

Далее: