Microsoft Excel

трюкиприёмырешения

Основы Excel
18.10.2014 3357

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

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

ВПР

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

Функция ВПР помогает находить информацию в больших таблицах, содержащих, например, перечень имеющегося ассортимента

Функция ВПР помогает находить информацию в больших таблицах, содержащих, например, перечень имеющегося ассортимента

Вставьте в формулу функцию ВПР, указав в первом ее аргументе искомое значение, по которому осуществляется связь [ 1 ]. Во втором аргументе задайте диапазон ячеек, в которых следует производить выборку [ 2 ], в третьем [ 3 ] — номер столбца, из которого будут подставляться данные, а в четвертом введите значение ЛОЖЬ, если хотите найти точное соответствие, или ИСТИНА, если нужен ближайший приблизительный вариант [ 4 ].

Создание диаграмм

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

Путешествия и туризм на kherson-news.net

В версии Excel 2013 есть вкладка «Рекомендуемые диаграммы», на которой отображаются типы диаграмм, соответствующие введенным вами данным

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

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

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

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

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

Сводная таблица — это инструмент для проведения над таблицей различных итоговых расчетов в соответствии с выбранными опорными точками

Сводная таблица — это инструмент для проведения над таблицей различных итоговых расчетов в соответствии с выбранными опорными точками

Сводная диаграмма

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

Сводные диаграммы помогают получать простое для восприятия представление сложных данных

Сводные диаграммы помогают получать простое для восприятия представление сложных данных

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

Лучшая, пожалуй, новая функция Excel 2013 — «Мгновенное заполнение» — позволяет эффективно решать повседневные задачи, связанные с быстрым переносом нужных блоков информации из смежных ячеек. В прошлом, при работе со столбцом, представленным в формате «Фамилия, Имя», пользователю приходилось вручную извлекать из него имена или искать какие-то очень сложные обходные пути. Предположим, что тот же самый столбец с фамилиями и именами есть и в Excel 2013. Достаточно ввести имя первого человека в ближайшую справа ячейку [ 1 ] и на вкладке «Главная» выбрать «Заполнить» и «Мгновенное заполнение» [ 2 ]. Excel автоматически извлечет все прочие имена и заполнит ими ячейки справа от исходных.

«Мгновенное заполнение» позволяет извлекать нужные блоки информации и заполнять ими смежные ячейки

«Мгновенное заполнение» позволяет извлекать нужные блоки информации и заполнять ими смежные ячейки

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

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

Power View

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

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

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

Транспонирование столбцов в строки и наоборот

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

Важнейшие комбинации клавиш

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

<Ctrl>-<Стрелка вниз> или <Стрелка вверх> Перемещение курсора в верхнюю или нижнюю ячейку текущего столбца
<Ctrl>-<Стрелка влево> или <Стрелка вправо> Перемещение курсора в крайнюю левую или крайнюю правую ячейку текущей строки
<Ctrl>--<Стрелка вниз> или <Стрелка вверх> Выделение всех ячеек выше или ниже текущей ячейки
<Shift>-<F11> Создание нового листа в рабочей книге
<F2> Переключение в режим редактирования ячейки
<Ctrl>-<Home> Перемещение в ячейку A1
<Ctrl>- Перемещение в последнюю ячейку таблицы, в которой содержатся данные
<Alt>-= Автоматическое суммирование всех ячеек, расположенных выше текущей

Top