Читаем онлайн «Microsoft Excel: Мастер формул»
Николай Павлов
Microsoft Excel: Мастер Формул
Подробное руководство по «высшему пилотажу»
в формулах и функциях Microsoft Excel
Москва
Издательство «Книга по Требованию»
2016
© Павлов Николай, 2017
П 12
Microsoft Excel: Мастер Формул. Подробное руководство по «высшему пилотажу» в формулах и функциях /
Николай Павлов – М., 2017. – 240 с. : ил.
ISBN 978-5-519-50143-9
В этой книге практикующий IT-тренер и MVP по Excel Николай Павлов делится секретами создания и отладки
сложных формул в программе Microsoft Excel. Подробно разбираются нюансы работы с дубликатами,
извлечение топовых значений, сравнение списков, поиск и подстановка данных, настройка связей между
таблицами и многое другое. Книга рассчитана на средних и продвинутых пользователей Microsoft Excel. Ко
всем описанным в книге задачам в комплекте идут живые файлы-примеры, которые можно использовать в
работе.
УДК 004,42(075.8)
ББК 32.81-018,2*32.973.26
Оглавление
ОТ АВТОРА ....................................................................................................................................................................... 4
ФАЙЛЫ ПРИМЕРОВ И ВИДЕОУРОКИ .............................................................................................................................. 5
ВВЕДЕНИЕ В ФОРМУЛЫ МАССИВОВ .............................................................................................................................. 6
ОПРЕДЕЛЕНИЕ И СПОСОБЫ ВВОДА ФОРМУЛ МАССИВА.................................................................................................................... 7
Способы ввода формул массива .................................................................................................................................... 7
КЛАССИФИКАЦИЯ ФОРМУЛ МАССИВА .......................................................................................................................................... 8
Тип 1. Массив(ы) на входе, массив на выходе .............................................................................................................. 8
Тип 2. Массив(ы) на входе, ячейка на выходе ............................................................................................................... 8
Тип 3. Массив констант внутри формулы ................................................................................................................. 9
ПЛЮСЫ И МИНУСЫ ФОРМУЛ МАССИВА ...................................................................................................................................... 10
Плюсы формул массива ................................................................................................................................................ 10
Минусы формул массива .............................................................................................................................................. 10
ХИТРОСТИ ВВОДА И ОТЛАДКИ СЛОЖНЫХ ФОРМУЛ ....................................................................................................11
ВЫЧИСЛЕНИЕ ФРАГМЕНТА ФОРМУЛЫ......................................................................................................................................... 12
ПОШАГОВОЕ ВЫПОЛНЕНИЕ....................................................................................................................................................... 13
БЫСТРОЕ ВЫДЕЛЕНИЕ ВСЕГО МАССИВА....................................................................................................................................... 14
НАГЛЯДНОСТЬ СЛОЖНЫХ ФОРМУЛ ............................................................................................................................................ 15
УМНЫЕ ТАБЛИЦЫ.................................................................................................................................................................... 16
ИМЕНОВАННЫЕ ДИАПАЗОНЫ .................................................................................................................................................... 19
ТОЧНОЕ КОПИРОВАНИЕ ФОРМУЛ БЕЗ СДВИГА ССЫЛОК.................................................................................................................. 23
ВЫБОРОЧНЫЙ ПОДСЧЕТ ПО УСЛОВИЯМ .....................................................................................................................25
СРЕДНЕЕ БЕЗ УЧЕТА НУЛЕЙ И ОШИБОК ........................................................................................................................................ 26
ВЫБОРОЧНОЕ СУММИРОВАНИЕ ОДНОМУ УСЛОВИЮ..................................................................................................................... 27
ВЫБОРОЧНОЕ СУММИРОВАНИЕ ПО НЕСКОЛЬКИМ УСЛОВИЯМ........................................................................................................ 29
Способ 1. Функция СУММЕСЛИМН .............................................................................................................................. 29
Способ 2. Функция баз данных БДСУММ .................................................................................................................... 30
Способ 3. Формула массива ......................................................................................................................................... 31
ПОДСЧЕТ КОЛИЧЕСТВА И СУММЫ ПО УСЛОВИЮ С УЧЕТОМ РЕГИСТРА .............................................................................................. 32
СУММИРОВАНИЕ ЯЧЕЕК ЧЕРЕЗ ОДНУ, ДВЕ, ТРИ И Т.Д. ................................................................................................................... 34
СУММИРОВАНИЕ ЯЧЕЕК ПО ДИАГОНАЛИ..................................................................................................................................... 37
СУММИРОВАНИЕ ПО «ОКНУ» НА ЛИСТЕ ..................................................................................................................................... 39
СУММИРОВАНИЕ ТОЛЬКО ВИДИМЫХ ЯЧЕЕК ................................................................................................................................ 41
СУММИРОВАНИЕ ЯЧЕЕК ПО ЦВЕТУ, ШРИФТУ, ФОРМАТУ И Т.Д. ....................................................................................................... 43
Оживляем призрака ...................................................................................................................................................... 43
Применение на практике ............................................................................................................................................ 44
Ложка дегтя ................................................................................................................................................................. 45
СУММИРОВАНИЕ ЧИСЕЛ, НАПИСАННЫХ ЧЕРЕЗ ДРОБЬ ................................................................................................................... 47
ПОИСК И ПОДСТАНОВКА
Microsoft Excel: Мастер Формул
Подробное руководство по «высшему пилотажу»
в формулах и функциях Microsoft Excel
Москва
Издательство «Книга по Требованию»
2016
© Павлов Николай, 2017
П 12
Microsoft Excel: Мастер Формул. Подробное руководство по «высшему пилотажу» в формулах и функциях /
Николай Павлов – М., 2017. – 240 с. : ил.
ISBN 978-5-519-50143-9
В этой книге практикующий IT-тренер и MVP по Excel Николай Павлов делится секретами создания и отладки
сложных формул в программе Microsoft Excel. Подробно разбираются нюансы работы с дубликатами,
извлечение топовых значений, сравнение списков, поиск и подстановка данных, настройка связей между
таблицами и многое другое. Книга рассчитана на средних и продвинутых пользователей Microsoft Excel. Ко
всем описанным в книге задачам в комплекте идут живые файлы-примеры, которые можно использовать в
работе.
УДК 004,42(075.8)
ББК 32.81-018,2*32.973.26
Оглавление
ОТ АВТОРА ....................................................................................................................................................................... 4
ФАЙЛЫ ПРИМЕРОВ И ВИДЕОУРОКИ .............................................................................................................................. 5
ВВЕДЕНИЕ В ФОРМУЛЫ МАССИВОВ .............................................................................................................................. 6
ОПРЕДЕЛЕНИЕ И СПОСОБЫ ВВОДА ФОРМУЛ МАССИВА.................................................................................................................... 7
Способы ввода формул массива .................................................................................................................................... 7
КЛАССИФИКАЦИЯ ФОРМУЛ МАССИВА .......................................................................................................................................... 8
Тип 1. Массив(ы) на входе, массив на выходе .............................................................................................................. 8
Тип 2. Массив(ы) на входе, ячейка на выходе ............................................................................................................... 8
Тип 3. Массив констант внутри формулы ................................................................................................................. 9
ПЛЮСЫ И МИНУСЫ ФОРМУЛ МАССИВА ...................................................................................................................................... 10
Плюсы формул массива ................................................................................................................................................ 10
Минусы формул массива .............................................................................................................................................. 10
ХИТРОСТИ ВВОДА И ОТЛАДКИ СЛОЖНЫХ ФОРМУЛ ....................................................................................................11
ВЫЧИСЛЕНИЕ ФРАГМЕНТА ФОРМУЛЫ......................................................................................................................................... 12
ПОШАГОВОЕ ВЫПОЛНЕНИЕ....................................................................................................................................................... 13
БЫСТРОЕ ВЫДЕЛЕНИЕ ВСЕГО МАССИВА....................................................................................................................................... 14
НАГЛЯДНОСТЬ СЛОЖНЫХ ФОРМУЛ ............................................................................................................................................ 15
УМНЫЕ ТАБЛИЦЫ.................................................................................................................................................................... 16
ИМЕНОВАННЫЕ ДИАПАЗОНЫ .................................................................................................................................................... 19
ТОЧНОЕ КОПИРОВАНИЕ ФОРМУЛ БЕЗ СДВИГА ССЫЛОК.................................................................................................................. 23
ВЫБОРОЧНЫЙ ПОДСЧЕТ ПО УСЛОВИЯМ .....................................................................................................................25
СРЕДНЕЕ БЕЗ УЧЕТА НУЛЕЙ И ОШИБОК ........................................................................................................................................ 26
ВЫБОРОЧНОЕ СУММИРОВАНИЕ ОДНОМУ УСЛОВИЮ..................................................................................................................... 27
ВЫБОРОЧНОЕ СУММИРОВАНИЕ ПО НЕСКОЛЬКИМ УСЛОВИЯМ........................................................................................................ 29
Способ 1. Функция СУММЕСЛИМН .............................................................................................................................. 29
Способ 2. Функция баз данных БДСУММ .................................................................................................................... 30
Способ 3. Формула массива ......................................................................................................................................... 31
ПОДСЧЕТ КОЛИЧЕСТВА И СУММЫ ПО УСЛОВИЮ С УЧЕТОМ РЕГИСТРА .............................................................................................. 32
СУММИРОВАНИЕ ЯЧЕЕК ЧЕРЕЗ ОДНУ, ДВЕ, ТРИ И Т.Д. ................................................................................................................... 34
СУММИРОВАНИЕ ЯЧЕЕК ПО ДИАГОНАЛИ..................................................................................................................................... 37
СУММИРОВАНИЕ ПО «ОКНУ» НА ЛИСТЕ ..................................................................................................................................... 39
СУММИРОВАНИЕ ТОЛЬКО ВИДИМЫХ ЯЧЕЕК ................................................................................................................................ 41
СУММИРОВАНИЕ ЯЧЕЕК ПО ЦВЕТУ, ШРИФТУ, ФОРМАТУ И Т.Д. ....................................................................................................... 43
Оживляем призрака ...................................................................................................................................................... 43
Применение на практике ............................................................................................................................................ 44
Ложка дегтя ................................................................................................................................................................. 45
СУММИРОВАНИЕ ЧИСЕЛ, НАПИСАННЫХ ЧЕРЕЗ ДРОБЬ ................................................................................................................... 47
ПОИСК И ПОДСТАНОВКА