Excel для продвинутых пользователей. Что должен знать продвинутый пользователь Excel

Поверьте, чем больше вы знаете об Excel, чем лучше разбираетесь в её функциях, тем эффективнее будет ваша работа. А мы пока расскажем вам о пяти наиболее важных инструментах Excel.

1. Запрет на изменения задним числом

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

1) Наведите курсор на ячейку «Дата» и выберите пункт «Данные».

2) Щёлкните на пункт «Проверка данных». Выскочит табличка.

3) В пункте «Тип данных» щёлкните на «Другой».

4) Найдите графу «Формула» и впишите туда: =А2=СЕГОДНЯ().

5) Найдите пункт «Игнорировать пустые ячейки» и уберите с него галочку.

6) Нажмите «ОК».

Ту же самую процедуру можно повторить со столбцом «Количество», да и в принципе - с любым другим столбцом.

2. Создание абсолютно скрытого листа

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

1) Наберите ALT+F11, слева выскочит вытянутое окошко.

2) В верхней части окошка укажите, который из листов вы хотите скрыть.

3) В нижней части окошка отыщите свойство Visible и переведите его в режим xlSheetVeryHidden.

Всё! Теперь об этом листе будете знать только вы.

3. Запрет на ввод дублей

Допустим, вы загружаете список продуктов в прайс-лист. Как избежать повторов? Приводим пример организации прайс-листа на десять ячеек; при желании вы можете создать любое их количество.

1) Выделите ячейки, на которые будет распространяться запрет дублей. Например, так: А1:А10.

2) Нажмите на вкладку «Данные» и выберите пункт «Проверка данных».

3) В выпадающем списке «Тип данных» из вкладки «Параметры» выберите пункт «Другой».

4) Введите =СЧЁТЕСЛИ($A$1:$A$10;A1)<=1 в графу «Формула».

5) Перейдите на вкладку «Сообщение об ошибке» и впишите туда текст, который будет выпадать при каждом дубле.

6) Нажмите «ОК».

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

Допустим, у вас принято делать таблицы, в которых расписывается, сколько всего продуктов продал определённый менеджер. Нередко бывает так, что таблица разрастается, и работать с ней становится сложно. Именно для облегчения работы с разросшимся документом и существует сервис «сводные таблицы». Как их создать?

1) Нажмите на «Вставку». Там есть пункт «Сводная таблица», нажмите на него.

2) Появится окошко. Нажмите «ОК».

3) Появится окошко, которое позволит вам сформировать сводную таблицу с теми данными, которые вас интересуют: например, самый продуктивный менеджер или самый востребованный товар.

5. Выборочное суммирование

Предположим, у вас есть таблица, в которой указано, что разные заказчики покупали у вас разные товары на разные суммы. Вы хотите узнать, на какую сумму в общей сложности клиент по имени VASILIY купил у вас буженины (Boiled Salted Pork).

Excel - не самая дружелюбная программа на свете. Обычный пользователь использует лишь 5% её возможностей и плохо представляет, какие сокровища скрывают её недра. H&F почитал советы Excel -гуру и научился сравнивать прайс-листы, прятать секретную информацию от чужих глаз и составлять аналитические отчёты в пару кликов. (О"кей, иногда этих кликов 15.)

Импорт курса валют

В Excel можно настроить постоянно обновляющийся курс валют.

Выберите в меню вкладку «Данные».

Нажмите на кнопку «Из веба».

В появившемся окне в строку «Адрес» введите http://www.cbr.ru и нажмите Enter.

Когда страница загрузится, то на таблицах, которые Excel может импортировать, появятся чёрно-жёлтые стрелки. Щелчок по такой стрелке помечает таблицу для импорта (картинка 1).

Пометьте таблицу с курсом валют и нажмите кнопку «Импорт».

Курс появится в ячейках на вашем листе.

Кликните на любую из этих ячеек правой кнопкой мыши и выберите в меню команду «Свойства диапазона» (картинка 2).

В появившемся окне выберите частоту обновления курса и нажмите «ОК».

Супертайный лист

Допустим, вы хотите скрыть часть листов в Excel от других пользователей, работающих над книгой. Если сделать это классическим способом - кликнуть правой кнопкой по ярлычку листа и нажать на «Скрыть» (картинка 1), то имя скрытого листа всё равно будет видно другому человеку. Чтобы сделать его абсолютно невидимым, нужно действовать так:

Нажмите ALT+F11.

Слева у вас появится вытянутое окно (картинка 2).

В верхней части окна выберите номер листа, который хотите скрыть.

- В нижней части в самом конце списка найдите свойство «Visible» и сделайте его «xlSheetVeryHidden» (картинка 3). Теперь об этом листе никто, кроме вас, не узнает.

Запрет на изменения задним числом

Перед нами таблица (картинка 1) с незаполненными полями «Дата» и «Кол-во». Менеджер Вася сегодня укажет, сколько морковки за день он продал. Как сделать так, чтобы в будущем он не смог внести изменения в эту таблицу задним числом?

Поставьте курсор на ячейку с датой и выберите в меню пункт «Данные».

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

В выпадающем списке «Тип данных» выбираем «Другой».

В графе «Формула» пишем =А2=СЕГОДНЯ().

Убираем галочку с «Игнорировать пустые ячейки» (картинка 2).

Нажимаем кнопку «ОК». Теперь, если человек захочет ввести другую дату, появится предупреждающая надпись (картинка 3).

Также можно запретить изменять цифры в столбце «Кол-во». Ставим курсор на ячейку с количеством и повторяем алгоритм действий.

Запрет на ввод дублей

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

Выделяем ячейки А1:А10, на которые будет распространяться запрет.

Во вкладке «Данные» нажимаем кнопку «Проверка данных».

Во вкладке «Параметры» из выпадающего списка «Тип данных» выбираем вариант «Другой» (картинка 1).

В графе «Формула» вбиваем =СЧЁТЕСЛИ($A$1:$A$10;A1)<=1.

В этом же окне переходим на вкладку «Сообщение об ошибке» и там вводим текст, который будет появляться при попытке ввести дубликаты (картинка 2).

Нажимаем «ОК».

Выборочное суммирование

Перед вами таблица, из которой видно, что разные заказчики несколько раз покупали у вас разные товары на определённые суммы. Вы хотите узнать, на какую общую сумму заказчик по имени ANTON купил у вас крабового мяса (Boston Crab Meat).

В ячейку G4 вы вводите имя заказчика ANTON.

В ячейку G5 - название продукта Boston Crab Meat.

Встаёте на ячейку G7, где у вас будет подсчитана сумма, и пишете для неё формулу {=СУММ((С3:С21=G4)*(B3:B21=G5)*D3:D21)}. Сначала она пугает своими объёмами, но если писать постепенно, то её смысл становится понятен.

Сначала вводим {=СУММ и открываем скобки, в которых будет три множителя.

Первый множитель (С3:С21=G4) ищет в указанном списке клиентов упоминания ANTON.

Второй множитель (B3:B21=G5) делает то же самое с Boston Crab Meat.

Третий множитель D3:D21 отвечает за столбец стоимости, после него мы закрываем скобки.

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

У вас есть таблица (картинка 1), где указано, какой товар, какому заказчику, на какую сумму продал конкретный менеджер. Когда она разрастается, выбирать отдельные данные из неё очень сложно. Например, вы хотите понять, на какую сумму продано моркови или кто из менеджеров выполнил больше всего заказов. Для решения таких проблем в Excel существуют сводные таблицы. Чтобы её создать, вам нужно:

Во вкладке «Вставка» нажать кнопку «Сводная таблица».

В появившемся окне нажать «ОК» (картинка 2).

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

Товарный чек

Чтобы посчитать общую сумму заказа, можно поступить как обычно: добавить столбец, в котором нужно перемножить цену и количество, а потом посчитать сумму по этому столбцу (картинка 1). Если же перестать бояться формул, можно сделать это более изящно.

Выделяем ячейку C7.

Вводим =СУММ(.

Выделяем диапазон B2:B5.

Вводим звёздочку, которая в Excel ­ - знак умножения.

Выделяем диапазон C2:C5 и закрываем скобку (картинка 2).

Вместо Enter при написании формул в Excel нужно вводить Ctrl + Shift + Enter.

Сравнение прайсов

Это пример для продвинутых пользователей Excel. Допустим, у вас есть два прайса, и вы хотите сравнить их цены. На 1-й и 2-й картинке у нас прайсы от 4 и от 11 мая 2010 года. Часть товаров в них не совпадает - вот как узнать, что это за товары.

Создаём в книге ещё один лист и копируем в него списки товаров и из первого, и из второго прайса (картинка 3).

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

В меню выбираем «Данные» - «Фильтр» - «Расширенный фильтр» (картинка 4).

В появившемся окне отмечаем три вещи: а) скопировать результат в другое место; б) поместить результат в диапазон - выберите место, куда хотите записать результат, в примере это ячейка D4; в) поставьте галочку на «Только уникальные записи» (картинка 5).

Нажимаем кнопку «ОК» и, начиная с ячейки D4, получаем список без дублей (картинка 6).

Удаляем первоначальный список товаров.

Вводим в колонку сравнения формулу =D5-C5, которая будет вычислять разницу (картинка 7).

Осталось автоматически загрузить в колонки «4 мая» и «11 мая» значения из прайсов. Для этого используем функцию: =ВПР(искомое_значение; таблица; номер_столбца; интервальный _просмотр).

- «Искомое_значение» - это строчка, которую мы будем искать в таблице прайса. Легче всего искать товары по их наименованию (картинка 8).

- «Таблица» - это массив данных, в котором мы будем искать нужное нам значение. Он должен ссылаться на таблицу, содержащую прайс от 4-го числа(картинка 9).

- «Номер_столбца» - это порядковый номер столбца в диапазоне, который мы задали для поиска данных. Для поиска мы определили таблицу из двух столбцов. Цена содержится во втором из них (картинка 10).

Интервальный_просмотр. Если таблица, в которой вы ищете значение, отсортирована по возрастанию или по убыванию, надо ставить значение ИСТИНА, если не отсортирована - пишете ЛОЖЬ.

Протяните формулу вниз, не забыв закрепить диапазоны. Для этого поставьте перед буквой столбца и перед номером строки значок доллара (это можно сделать, выделив нужный диапазон и нажав клавишу F4).

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

Оценка инвестиций

В Excel можно посчитать чистый дисконтированный доход (NPV), то есть сумму дисконтированных значений потока платежей на сегодняшний день. В примере рассчитана величина NPV на основе одного периода инвестиций и четырёх периодов получения доходов (строка 3 «Денежный поток»).

Формула в ячейке B6 вычисляет NPV с помощью финансовой функции: =ЧПС($B$4;$C$3:$E$3)+B3 (картинка 1).

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

В ячейке С5 результат получен благодаря формуле =C3/((1+$B$4)^C2) (картинка 2).

В ячейке C6 тот же результат получен через формулу {=СУММ(B3:E3/((1+$B$4)^B2:E2))} (картинка 3).

Сравнение инвестиционных предложений

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

В свободную ячейку нужно ввести формулу =npv(b3/12,A8:A12)+A7, где b3 - учётная ставка, 12 - число месяцев в году, A8:A12 - столбец с цифрами поэтапного возврата инвестиций, A7 - необходимая сумма вложений.

По точно такой же формуле рассчитывается чистая приведённая стоимость другого инвестпроекта.

Теперь их можно сравнить: у кого больше NPV, тот проект выгоднее.

Проблема, с которой мы все сталкиваемся - это не отсутствие данных; наоборот - это огромные объемы данных! Именно поэтому я выступаю за использование замечательной функции Excel - Cводные таблицы , чтобы обобщить и проанализировать ваши данные.

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

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

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

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

Как применять продвинутые приёмы сводных таблицы в Excel (короткое видео)

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

5 продвинутых приёмов сводных таблиц в Excel

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

1. Срезы

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

В этом примере я вставил срез для типа Item . После того, как я нажимаю на Backpack , сводная таблица показывает только этот параметр в таблице.

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

Чтобы добавить срез, кликните в сводной таблице и найдите вкладку Анализ на ленте Excel.

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

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

2. Временная шкала

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

Перейдите к разделу Анализ > Вставить временную шкалу , чтобы добавить временную шкалу, специальный тип среза, который управляет данными, включенными в расширенную сводную таблицу Excel, на основе даты.

Совет: если эта функция у вас не работает, убедитесь, что в исходных данных есть дата, отформатированная в электронной таблице, как дата.

Чтобы добавить временную шкалу, убедитесь, что вы выбрали сводную таблицу (щелкните внутри неё), а затем нажмите кнопку Анализ > Вставить временную шкалу на ленте Excel. Во всплывающем окне отметьте столбец даты (или несколько столбцов) и нажмите OK , чтобы создать временную шкалу.

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

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

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

3. Табличный вид

Стандартный вид сводных таблиц в Excel выглядит как водопад; при перетаскивании большего количества полей в строки, Excel создает больше «слоев» в данных.

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

Выберите Конструктор > Макет отчета > Показать в табличной форме , чтобы работать с вашей сводной таблицей в виде обычной таблицы.

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

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

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

4. Вычисляемые поля

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

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

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

Используйте Анализ > Поля, элементы и наборы > Вычисляемое поле , чтобы вставить вычисленное поле в вашу сводную таблицу.

В новом всплывающем окне начните с присвоения вычисленному полю имени. в моем случае я назову его Total Order . Общая стоимость заказа это количество, умноженное на цену каждой единицы. Затем я дважды кликнул по первому полю названия (quantity) в списке полей в этом окне.

Чтобы вычислить общую стоимость заказа, я умножил существующее поле цены за единицу (Unit Price ) на поле количества (Quantity ).

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

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

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

Я умножил общую стоимость заказа на 1.05 для расчета стоимости, включая налог с продаж; вы можете использовать числовые значения наряду с существующими полями.

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

Перейдите в Вставка > , чтобы попробовать эту функцию.

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

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

Функция рекомендации сводной таблицы предлагает множество вариантов для анализа ваших данных в один клик мыши.

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

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

Повторяйте и продолжайте обучение (с ещё бо́льшими уроками по Excel)

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

Эти уроки вам продвинуть ваши навыки с Excel и сводным таблицам на следующий уровень. Проверь их:

• ExcelZoo имеет большой обзор приёмов сводной таблицы в их статье, 10 уроков для освоения сводных таблиц (на английском).
• Мы в Envato Tuts+ рассмотрели сводные таблицы с помощью урока для новичков .
• Для более простого введения в Microsoft Excel ознакомьтесь с нашей учебной серией .

Что вы всё ещё хотите узнать о сводных таблицах? Сообщите мне ваши идеи или вопросы в комментариях ниже этого урока.

ANTHONY DOMANICO. 11 tricks for Excel power users. PCWorld .

Знание этих функций - от сводных таблиц до Power View - поможет вам влиться в ряды специалистов по электронным таблицам.

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

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

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

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

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

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

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

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

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

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

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

Функция «ЕСЛИ» вычисляет результат в зависимости от задаваемого вами условия.

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

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

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

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

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

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

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

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

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

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

Предположим теперь, что тот же самый столбец с фамилиями и именами присутствует в Excel 2013. Достаточно ввести имя первого человека в ближайшую справа ячейку (1) и на вкладке «Главная» выбрать «Заполнить» и «Мгновенное заполнение». Excel автоматически извлечет все прочие имена и заполнит ими ячейки справа от исходных.

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

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

Новый инструмент быстрого анализа Excel 2013 помогает ускорить создание диаграмм из простых наборов данных. После выделения данных рядом с правым нижним углом выделенной области появляется характерный значок (1). Щелкнув по нему, вы переходите в меню «Быстрого анализа» (2).

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

Быстрый анализ ускоряет работу с простыми наборами данных.

Power View

Интерактивный инструмент исследования и визуализации данных Power View предназначен для извлечения и анализа больших объемов данных из внешних источников. В Excel 2013 для вызова функции Power View перейдите на вкладку «Вставка» (1) и нажмите кнопку «Отчеты» (2).

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

Режим Power View позволяет создавать интерактивные отчеты готовые к презентации.

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

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

Функция условного форматирования позволяет выделять нужные области данных с минимальными усилиями.

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

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

Функция Специальной вставки позволяет транспонировать столбцы и строки.

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

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

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

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

Как работать с базой данных в Excel

База данных (БД) – это таблица с определенным набором информации (клиентская БД, складские запасы, учет доходов и расходов и т.д.). Такая форма представления удобна для сортировки по параметру, быстрого поиска, подсчета значений по определенным критериям и т.д.

Для примера создадим в Excel базу данных.

Информация внесена вручную. Затем мы выделили диапазон данных и форматировали «как таблицу». Можно было сначала задать диапазон для БД («Вставка» - «Таблица»). А потом вносить данные.



Найдем нужные сведения в базе данных

Выбираем Главное меню – вкладка «Редактирование» - «Найти» (бинокль). Или нажимаем комбинацию горячих клавиш Shift + F5 или Ctrl + F. В строке поиска вводим искомое значение. С помощью данного инструмента можно заменить одно наименование значения во всей БД на другое.

Отсортируем в базе данных подобные значения

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

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

Выясним, какие товары стоят меньше 100 р. Нажимаем на стрелочку около «Цены». Выбираем «Числовые фильтры» - «Меньше или равно».

Задаем параметры сортировки.

После нажатия ОК:

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

Найдем промежуточные итоги

Посчитаем общую стоимость товаров на складе №3.

С помощью автофильтра отобразим информацию по данному складу (см.выше).

Под столбцом «Стоимость» вводим формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;E4:E41), где 9 – номер функции (в нашем примере – СУММА), Е4:Е41 – диапазон значений.

Обратите внимание на стрелочку рядом с результатом формулы:

С ее помощью можно изменить функцию СУММ.

В чем прелесть данного метода: если мы поменяем склад – получим новое итоговое значение (по новому диапазону). Формула осталась та же – мы просто сменили параметры автофильтра.

Как работать с макросами в Excel

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

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

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

1. Скопируем таблицу на новый лист.
2. Уберем данные по количеству. Но проследим, чтобы для этих ячеек стоял числовой формат без десятичных знаков (так как возможен заказ товаров поштучно, не в единицах массы).
3. Для значения «Цены» должен стоять денежный формат.
4. Уберем данные по стоимости. Введем в столбце формулу: цена * количество. И размножим.
5. Внизу таблицы – «Итого» (сколько единиц товара заказано и на какую стоимость). Еще ниже – «Всего».

Талица приобрела следующий вид:

Теперь научим Microsoft Excel выполнять определенный алгоритм.

Как работать в Excel одновременно нескольким людям

Чтобы несколько пользователей имели доступ к базе данных в Excel, необходимо его открыть. Для версий 2007-2010: «Рецензирование» - «Доступ к книге».

Примечание. Для старой версии 2003: «Сервис» - «Доступ к книге».

Но! Если 2 и более пользователя изменили значения одной и той же ячейки во время обращения к документу, то будет возникать конфликт доступа.

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

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

К сожалению, в многопользовательском режиме существуют некоторые ограничения. Например:

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

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

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