• Быстрый поиск надежных решений
    и практической информации

Uteka

Я ищу...

Где искать:

расширенный поиск
Получите доступ к более 2 миллионов готовых решений, публикаций и обзоров
Оформить
подписку

Суперотчет в Exсel

Малоизвестные возможности привычной программы


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


Павел Куратник,
заместитель главного редактора
журнала «Справочник экономиста»

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

Exсel, пожалуй, самая популярная офисная программа. Однако весь ее функционал часто остается невостребованным. Умелое пользование электронными таблицами поможет сэкономить не только время на обработку информации, но и средства предприятия. Ведь принять правильные управленческие решения можно только при условии получения полных и достоверных данных.

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

Условное форматирование

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

Начиная с версии Excel 2007 спектр настроек существенно расширился. В частности, появилась возможность использовать гистограммы, цветовые шкалы и значки (вкладка Главная → кнопка Условное форматирование). В результате пользователь может произвольно задавать цвет заливки и обрамления столбцов гистограммы. Причем заливка может быть как сплошным цветом, так и градиентом. Чтобы задать такие настройки, нужно перейти по вкладкам ГлавнаяУсловное форматированиеУправление правилами (рис. 1).

1 из 1

Например, можно автоматически одним нажатием выделять цветом:

  • значения больше, меньше, равно или не равно заданному числу;
  • значения, попадающие в определенный диапазон;
  • ячейки, содержащие заданный текст;
  • дубликаты;
  • первые (последние) элементы или процент элементов;
  • все значения, выше (ниже) среднего;
  • даты (вчера, сегодня, завтра, на прошлой неделе, в прошлом месяце и т. д.).

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

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

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

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

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

Смарт-теги

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

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

Вот некоторые из возможных вариантов «быстрого форматирования» вставляемого диапазона: формулы, значения, сохранить исходное форматирование, сохранить ширину столбцов оригинала, транспонировать, значения и форматы чисел, значения и исходное форматирование (рис. 2).

1 из 1

Спарклайны и диаграммы

Спарклайны, появившиеся в Microsoft Excel 2010, — это небольшие диаграммы в ячейках листа, визуализирующие данные. Другое название спарклайнов — инфокривые. С помощью спарклайнов можно показывать тенденции в рядах значений и выделять максимальные и минимальные значения. Чтобы достичь наилучшего эффекта, спарклайны следует располагать рядом с соответствующими данными. В отличие от диаграмм на листе Excel, спарклайны не являются объектами. Фактически спарклайн — это небольшая диаграмма, встроенная в ячейку. В эту ячейку можно вводить текст, а спарклайн при этом будет играть роль фона.

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

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

Инфокривые-графики позволяют иллюстрировать тенденции и временные изменения значений, инфокривые-столбики — сравнивать значения, а инфокривые выигрыша (проигрыша) — анализировать значения по отношению к стандарту.

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

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

1 из 1

Таблицы и сводные таблицы

Начиная с версии Excel 2007 появилась возможность управлять таблицей как одним целым, то есть создавать так называемые связанные таблицы. Для создания такой таблицы предназначена кнопка Форматировать как таблицу на панели Стили ленты Главная (или Вставка Таблица). Выберите стиль будущей таблицы и задайте диапазон ячеек, на основе которого будет создана связанная таблица. После настройки всех параметров получим связанную таблицу. Рядом с заголовками столбцов появятся кнопки со стрелками (аналогично фильтрации), а в окне программы — контекстный инструмент Работа с таблицами, содержащий ленту Конструктор.

Созданная таблица автоматически подстраивается по размерам при добавлении или удалении в ней данных. Если вписать в такую таблицу новые строки, она растянется ниже, если добавить новые столбцы — в сторону. В правом нижнем углу таблицы можно увидеть маркер границы, который перемещается автоматически, и при необходимости скорректировать его положение мышью. Если прокручивать такую таблицу вниз, заголовки столбцов (A, B, C...) меняются на названия полей с автофильтром. При добавлении в такую таблицу новых строк в них автоматически копируются все формулы. А если создать новый столбец с формулой, она автоматически копируется на весь столбец. К данным таблицы можно адресоваться, используя имена отдельных ее элементов (рис. 4).

1 из 1

Сводные таблицы и диаграммы — мощный инструмент, с помощью которого можно обобщать, анализировать, изучать данные, а также приводить их из таблицы или внешнего источника. Их применение может быть особенно полезным, когда необходимо проанализировать большой объем данных, а их группировки и промежуточные итоги позволяют быстро получать необходимые результаты.

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

Кратко перечислим основные возможности сводных таблиц. С их помощью можно:

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

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

1 из 1

Если вы часто пользуетесь таблицами, особенно с большими массивами исходных данных (даже миллионы строк), имеет смысл загрузить надстройку PowerPivot для Excel (в Excel 2013 она уже встроена) (рис. 6). Надстройка PowerPivot сочетает исходные возможности Excel и встроенную подсистему памяти, позволяет пользователям свободно работать с большими объемами данных в режиме реального времени. Независимо от количества строк сводные таблицы будут работать быстро. С помощью надстройки PowerPivot для Excel можно быстро собирать и объединять данные из различных источников, включая базы данных Access, листы, отчеты и веб-каналы данных. Загрузив данные, можно изучать их в интерактивном режиме, производить над ними вычисления и формировать отчеты с помощью сводных таблиц, срезов и других инструментов Excel.

1 из 1

Срезы

Работая с большими таблицами, часто приходится их принудительно упрощать, фильтруя часть информации, чтобы не запутаться в цифрах. Для этого поместите некоторые поля в область фильтра (в версиях до 2007 года она называлась «область страниц») и выберите из выпадающих списков, только нужные значения.

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

Срез имеет вид отдельного графического объекта (диаграммы или картинки). Он не связан с ячейками и отражается над листом, поэтому его легко перемещать.

Чтобы создать срез для текущей сводной таблицы, выполните следующие команды: ПараметрыСортировка и фильтрВставить срез. Дополнительный приятный нюанс заключается в том, что срез разными цветами отражает не только выделенные, но и пустые элементы, для которых нет ни одного значения в исходной таблице. Если используете несколько срезов, можно быстро наглядно отразить взаимосвязи между элементами данных (рис. 7).

1 из 1

Быстрый анализ в Excel 2013

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

Чтобы попробовать, как работает Excel 2013, посетите официальный сайт Microsoft и скачайте preview-версию этой программы.

В Excel 2013 можно очень быстро и легко воспользоваться практически всеми рассмотренными выше возможностями с помощью кнопки Экспресс-анализ (Quick Analysis).

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

Перечислим возможности такого быстрого анализа (рис. 8):

  • форматирование (гистограммы, цветовая шкала, набор значков, больше, первые 10 %, очистить формат);
  • диаграммы (гистограмма с группировкой, линейчатая с группировкой, график, круговая, другие диаграммы);
  • итоги (сумма, среднее, счетчик, всего %, нарастающий итог);
  • таблицы (таблица, сводная таблица);
  • спарклайны (график, гистограмма, выигрыш/проигрыш).
1 из 1

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

К быстрому анализу условно можно отнести также два новых пункта, появившиеся на вкладке «Вставка», а именно:

  • рекомендуемые диаграммы;
  • рекомендуемые сводные таблицы.

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

Рекомендации

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

Достаточно выбрать необходимый пункт меню в привычной версии Excel 2003, и сразу появится анимация тех же действий, но в Excel 2010. Бесплатная анимация соответствия команд меню Excel 2003 и ленты Excel 2010 доступна по адресу: http://office.microsoft.com/ru-ru/excel-help/HA101794130.aspx.

Руководством можно пользоваться как через Интернет (загружается и работает в отдельном окне, см. рис. 9), так и установив на компьютер и работая через меню Пуск.

Менее удобный вариант — скачать с сайта Microsoft файл-шаблон Excel, в котором также перечислены по пунктам меню Excel 2003 и соответствие команд ленты Excel 2010.

1 из 1

Время не стоит на месте. Кто-то не хочет отказываться от Excel 2003, а кто-то стремится попробовать полный функционал Excel 2013. С одной стороны, многое зависит от задач, которые стоят перед пользователем. А с другой стороны, пользуясь старыми версиями, ту же задачу можно выполнять дольше, с меньшим удобством и с ограниченным функционалом. Однако любая версия Exсel имеет множество функций, которыми, к сожалению, часто пренебрегают рядовые пользователи.

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


Дата подготовки
16.06.2014

Комментарии к материалу

Оформить подписку на раздел «Коммерция»

Надежные решения по бухучету, налогам и праву

2268 грн. / год

Купить

Лучшие материалы

Суперотчет в Exсel