Microsoft Excel 2013/2010. Расширенный курс.

описание семинара

Категория, группа

должностей

Специалисты и руководители различных структур, желающих повысить свой начинающий уровень Excel до уверенного профессионала

содержание семинара

1. ВСЕ О ВЫЧИСЛЕНИЯХ В EXCEL

1.1 Режимы ввода и правки формул. Использование относительных и абсолютных ссылок – разбираем знак доллара $

1.2 Пользовательские имена ячеек для облегчения написания и чтения формул

1.3 Применение различных категорий встроенных функций       

• математические СУММ, СУММЕСЛИ, СУММЕСЛИМН – выборка по любому количеству условий, применение символов подстановки значений – *;
• финансовая ПЛТ для расчета аннуитетного платежа, суммы переплаты, составление графика платежей на примере ипотечного кредита или лизинга;
• текстовые ЛЕВСИМВ, ПРАВСИМВ, ПСТР, ДЛСТР, ПОИСК/НАЙТИ, СЦЕПИТЬ для деления текста по столбцам и решения обратной задачи;
• вычисления по датам СЕГОДНЯ, РАБДЕНЬ, ДОЛЯГОДА и др.;
• ссылки и подстановки – решение типовых задач сопоставления списков с помощью ВПР или ГПР, использование символа подстановки;
• логические – проверка условия с помощью ЕСЛИ, проверка нескольких условий и работа со вложенными ЕСЛИ.

1.4 Условное форматирование для выборочного, но автоматизированного оформления данных:

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

поиск отличий в двух списках и выделение их цветом.

2. ПРИЕМЫ ОБРАБОТКИ БОЛЬШИХ ТАБЛИЦ (СПИСКОВ)        

2.1 Использование «умных» таблиц и автофильтров, в том числе:

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

2.2 Особенности применения сортировки:

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

2.3 Работа с общими и промежуточными итогами:

• хитрости ручного подведения итогов в больших таблицах;
• автоматическое создание промежуточных итогов;
• использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в сложных таблицах с большим количеством расшифровок.

2.4 Консолидация данных:

• плюсы и минусы автоматической консолидации данных отличающихся друг от друга таблиц, расположенных на разных листах или в разных файлах;
• ручная консолидация данных на итоговом листе при помощи «трехмерных» ссылок – для таблиц жесткой структуры;

режим обработки группы листов.

3. АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ СВОДНЫХ ТАБЛИЦ        

3.1 Построение сводной таблицы:

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

3.2 Оформление сводных таблиц:

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

3.3 Работа с отчетом сводной таблицы:

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

использование ссылок на ячейки отчета сводной таблицы.

4. ПОДГОТОВКА К СОВМЕСТНОЙ РАБОТЕ

4.1 Три инструмента защиты данных:

• пароль на доступ к файлу;
• защита листов от удаления, переименования и т.д.  ;
• защита данных и формул от удаления, корректировок и пр.

4.2 Работа с выпадающими списками:

• создание выпадающего списка;
• как разрешить сделать запись не из списка, и быстрый поиск "новых" записей.