Полезные приемы для опытных пользователей MS Excel

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

ВПР

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


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

Построение диаграмм

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

Еще уроки по Excel на сайте автора:http://itstolytsa.ua/nashy-kursy/beginning/ms-office

Функции ЕСЛИ, ЕСЛИОШИБКА

В список самых популярных функций редактора Excel можно отнести также функции ЕСЛИ и ЕСЛИОШИБКА. Использование Функции ЕСЛИ позволяет пользователю определить условную формулу, вычисляющую разные варианты значений в зависимости от выполнения или невыполнения определенного условия.
Пример: студентам, которые набрали на экзамене 80 баллов и более (полученные баллы отображаются в столбце «C»), нужно присвоить признак «сдал», а всем, чьи оценки не превышают 79 баллов, — присвоить признак «не сдал».
Функция ЕСЛИОШИБКА — это частный случай обобщенной функции ЕСЛИ. С ее помощью можно возвратить заданное значение (или пустое поле) в том случае, если при выполнении формулы будет сделана ошибка.
Пример: при выполнении функции ВПР на другом листе или в другой таблице, функция ЕСЛИОШИБКА будет возвращать пустое значение тогда, когда ВПР не будет находить искомого параметра, который был задан первым аргументом.

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

Сводная таблица — это, по сути, итоговая таблица, которая позволяет подсчитать количество элементов и вычислить среднее значение, просуммировать и применить другие функции на основе заданных пользователем отправных точек. В Excel 2013 появился дополнительный раздел «Рекомендуемые сводные таблицы». Это упрощает процесс создания таблиц, в которых отображаются нужные пользователю данные.
Пример: чтобы подсчитать значение среднего балла студентов и увидеть зависимость этого значения от их возраста, нужно переместить поле «Возраст» в раздел Строк, поля с оценками внести в раздел «Значения». Далее в меню значений следует выбрать пункт «Параметры полей значений», в качестве операции указать «Среднее». Так же можно производить подсчеты итогов по другим категориям. Например, можно вычислить количество студентов, которые сдали или не сдали, и указать при этом зависимость значений от полового признака.
Сводная таблица — отличный инструмент для проведения самых разных итоговых расчетов относительно любых опорных точек.

Сводные диаграммы

Сводные диаграммы схожи со сводными таблицами и при этом имеют черты традиционных диаграмм MS Excel. Создание сводной диаграммы позволяет легко и без лишних временных затрат сформировать простое для визуального восприятия представление сложных и объемных наборов данных. Такие диаграммы поддерживают множество функций традиционных диаграмм. Они позволяют использовать ряды, категории и прочее. Есть возможность добавления различных интерактивных фильтров, что дает возможность манипулировать определенными подмножествами данных. В версии редактора 2013 года есть «Рекомендуемые сводные диаграммы». Чтобы произвести работу со сводными диаграммами, выберите вкладку «Вставка», после чего перейдите в раздел создания диаграмм, где выберите пункт меню «Рекомендуемые диаграммы». Переместите указатель мыши на нужный Вам вариант, чтобы увидеть наглядный пример внешнего вида этого типа диаграммы. Для создания сводной диаграммы вручную, выберите на вкладке «Вставка» раздел «Сводная диаграмма».
Сводные диаграммы очень функциональны, они дают возможность получить легкое для восприятия отображение сложных и многочисленных данных.

Мгновенное заполнение

Это, пожалуй, наиболее удачная новая функция, присутствующая в Excel 2013. Этот вариант заполнения позволяет максимально быстро и эффективно решать простые и не очень повседневные задачи, такие как быстрый перенос блоков информации между смежными ячейками. В предыдущих версиях редактора, работая со столбцом, имеющим формат «Фамилия, имя», пользователь должен был самостоятельно извлекать имена, заниматься поиском сложных отходных путей. Допустим, тот же столбец с фамилиями и именами имеется в Excel 2013. Нужно лишь ввести имя первого человека в ячейку справа, и на главной вкладке выбрать функции «Заполнить», «Мгновенное заполнение». Редактор сам извлечет все остальные имена, заполнив ими ячейки, располагающиеся с правой стороны от исходных.
Функция мгновенного заполнения позволяет извлекать блоки информации и помещать их в смежные ячейки.

Быстрый анализ

Это новый инструмент для быстрого анализа, добавленный в Excel 2013. Он помогает ускорить процесс создания диаграмм. После того, как пользователь выделяет данные для диаграммы, в правом нижнем углу области выделения виден характерный значок. Выбрав его, Вы перейдете в меню «Быстрый анализ». Здесь доступны инструменты «Форматирование», «Диаграмма», «Итоги», «Таблицы» и «Спарклайны». Нажимая на каждый из этих инструментов, Вы сможете видеть поддерживаемые возможности.

Power View

Этот интерактивный инструмент, позволяющий исследовать и визуализировать данные, нужен для извлечения больших массивов данных из сторонних источников и проведения их анализа. В новой версии Excel чтобы вызвать функцию Power View, необходимо перейти в раздел меню «Вставка» и нажать кнопку «Отчеты». Те отчеты, которые были созданы с помощью функции Power View, готовы к презентации. Они поддерживают режим чтения, полноэкранного отображения. Интерактивную версию таких отчетов можно экспортировать в программу PowerPoint. Чтобы освоить операции, доступные при использовании этой функции, ознакомьтесь с руководством по бизнес-анализу на официальном сайте компании Microsoft.

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

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

Транспонирование

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

Полезные комбинации клавиш

Ниже приведены восемь комбинаций клавиш, которые особенно полезны. Они позволяют быстро перемещаться по таблицам Excel и выполнять часто встречающиеся операции.
Ctrl + стрелка вниз или вверх — переместить курсор в верхнюю или нижнюю ячейку выбранного столбца
Ctrl + Стрелка влево или вправо — переместить курсор в крайнее положение текущей строки
Ctrl + Shift + Стрелка вниз или вверх — выделить все ячейки выше или ниже выбранной ячейки
Shift + F11 — Создать новый лист
F2 — режим редактирования ячейки
Ctrl + Home — Переместиться в ячейку A1
Ctrl — Переместиться в последнюю ячейку таблицы, содержащую данные
Alt + = — Автоматически суммировать все ячейки, расположенные выше выбранной

Автор статьи: Валерий Попов.