Павлов Николай - Microsoft Excel – готовые решения. Бери и пользуйся! + примеры, 2-е изд. [2022, PDF, RUS]

ВВЕДЕНИЕ .......................................................................................................................................................................12
Короткое предисловие ............................................................................................................................................ 13
Как работать с этой книгой ...................................................................................................................................... 14
Файлы с примерами ................................................................................................................................................. 14
Тренинги.................................................................................................................................................................... 15
Онлайн-курсы ........................................................................................................................................................... 15
Видеоуроки и канал на YouTube ............................................................................................................................. 16
ВЫПАДАЮЩИЕ СПИСКИ ................................................................................................................................................17
Как создать выпадающий список............................................................................................................................ 18
Способ 1. Быстрый ....................................................................................................................................................... 18
Способ 2. Стандартный .............................................................................................................................................. 18
Способ 3. Элемент управления формы ...................................................................................................................... 19
Способ 4. Элемент ActiveX ........................................................................................................................................... 21
Итоговая сравнительная таблица всех способов ................................................................................................... 23
Выпадающий список на основе динамического диапазона ................................................................................ 24
Связанные (зависимые) выпадающие списки ....................................................................................................... 27
Способ 1. Функция ДВССЫЛ .......................................................................................................................................... 27
Способ 2. Умные таблицы и ДВССЫЛ ......................................................................................................................... 28
Способ 3. Отсортированный справочник .................................................................................................................. 29
Способ 4. Неотсортированный справочник .............................................................................................................. 31
Очистка второго списка при изменениях в первом ................................................................................................. 31
Выпадающий список для выбора изображений ................................................................................................... 33
Шаг 1. Готовим каталог изображений ..................................................................................................................... 33
Шаг 2. Создаем именованные диапазоны для ячеек с картинками ........................................................................ 34
Шаг 3. Создаем выпадающий список товаров .......................................................................................................... 35
Шаг 4. Вытаскиваем имя диапазона для выбранного товара ............................................................................... 35
Шаг 5. Создаем именованный диапазон с ДВССЫЛ ................................................................................................... 36
Шаг 6. Добавляем картинку и привязываем её к списку .......................................................................................... 36
Примечания ................................................................................................................................................................... 37
Выпадающий список с автоматическим добавлением отсутствующих элементов ........................................... 38
Шаг 1. Создаем именованный диапазон ..................................................................................................................... 38
Шаг 2. Создаем выпадающий список в ячейке ........................................................................................................... 38
Шаг 3. Добавляем простой макрос ............................................................................................................................ 39
Выпадающий список с удалением использованных элементов .......................................................................... 41
Постановка задачи....................................................................................................................................................... 41
Шаг 1. Кто сколько работает? .................................................................................................................................. 41
Шаг 2. Кто еще свободен? ........................................................................................................................................... 42
Шаг 3. Формируем список ............................................................................................................................................ 42
Шаг 4. Создаем именованный диапазон свободных сотрудников .......................................................................... 42
Шаг 5. Создаем выпадающий список в ячейках ......................................................................................................... 43
Выпадающий список с данными из другого файла ............................................................................................... 44
Способ 1. Функция ДВССЫЛ .......................................................................................................................................... 44
Способ 2. Импорт данных ............................................................................................................................................ 44
Выпадающий список с мультивыбором ................................................................................................................. 48
ДАТЫ И ВРЕМЯ ...............................................................................................................................................................49
Как правильно вводить даты и время .................................................................................................................... 50
Всплывающие календари для быстрого ввода даты ............................................................................................ 51
Быстрый ввод даты без разделителей ................................................................................................................... 52
Автоматическая фиксация текущей даты при вводе данных ............................................................................... 53
Microsoft Excel: Готовые решения ‒ бери и пользуйся! www.PlanetaExcel.ru
Особенности копирования дат ................................................................................................................................ 54
Быстрый ввод текущей даты .................................................................................................................................... 55
Как Excel на самом деле хранит и обрабатывает даты и время ........................................................................... 56
Число дней между двумя датами ........................................................................................................................... 57
Как посчитать количество дней между двумя датами ......................................................................................... 57
Как посчитать количество рабочих дней между двумя датами .......................................................................... 57
Как выделить из даты день, месяц и год отдельно ............................................................................................... 58
Как собрать дату из фрагментов (день, месяц, год) .............................................................................................. 59
Сдвиг дат на N периодов (дней, месяцев, лет) ...................................................................................................... 60
Сдвиг даты на N дней в будущее или прошлое ......................................................................................................... 60
Сдвиг даты на N рабочих (банковских) дней ............................................................................................................. 60
Сдвиг даты на N месяцев ............................................................................................................................................. 60
Сдвиг даты на N лет .................................................................................................................................................... 60
Универсальный календарь формулой .................................................................................................................... 61
Вычисление дня недели по дате ............................................................................................................................. 63
Вычисление временных интервалов ...................................................................................................................... 64
Вычисление возраста или стажа .............................................................................................................................. 65
Номер недели по дате .............................................................................................................................................. 66
Способ 1. Стандарт ГОСТ ИСО 8601-2001 .................................................................................................................. 66
Способ 2. Неделя с 1-м января ...................................................................................................................................... 66
Номер квартала по дате ........................................................................................................................................... 67
Преобразование текстовой даты в полноценную дату ......................................................................................... 68
Вычисление нужной даты по формуле ................................................................................................................... 69
РАБОТА С ТЕКСТОМ ........................................................................................................................................................ 70
Склеивание текста из нескольких ячеек ................................................................................................................. 71
Способ 1. Функции СЦЕПИТЬ и СЦЕП ............................................................................................................................ 71
Способ 2. Функция ОБЪЕДИНИТЬ ................................................................................................................................. 71
Способ 3. Символ & для склеивания текста .............................................................................................................. 72
Способ 4. Макрос для объединения ячеек без потери текста ............................................................................... 72
Извлечение символов из текстовой строки ............................................................................................................ 74
Извлечение N-го по счету слова из ячейки ............................................................................................................. 75
Способ 1. Формулами .................................................................................................................................................... 75
Способ 2. Пользовательская макрофункция ............................................................................................................. 75
Фамилия И.О. из ФИО ............................................................................................................................................... 77
Разделение «слипшегося» текста по столбцам ...................................................................................................... 78
Обработка текста по образцу Мгновенным заполнением ................................................................................... 81
Извлечение слов из текста и/или перестановки слов............................................................................................. 81
Деление текста по регистру ...................................................................................................................................... 81
Склейка текста ............................................................................................................................................................ 82
Извлечение отдельных символов ............................................................................................................................... 82
Извлечение только чисел, текста или дат .............................................................................................................. 82
Преобразование форматов дат ................................................................................................................................. 83
Преобразование текста (чисел) в дату .................................................................................................................... 83
Изменение регистра..................................................................................................................................................... 83
Ограничения и нюансы ................................................................................................................................................. 83
Перевод текста в транслит ....................................................................................................................................... 85
Числа как текст .......................................................................................................................................................... 86
Когда это хорошо и когда плохо ................................................................................................................................. 86
Превращение чисел-как-текст в полноценные числа............................................................................................ 88
Способ 1. Преобразование в число .............................................................................................................................. 88
Способ 2. Умножение на единицу ................................................................................................................................ 88
Удаление из текста лишних символов и пробелов ................................................................................................ 89
Замена ............................................................................................................................................................................ 89
Удаление пробелов........................................................................................................................................................ 89
Microsoft Excel: Готовые решения ‒ бери и пользуйся! www.PlanetaExcel.ru
Удаление непечатаемых символов ............................................................................................................................ 90
Удаление переносов строк (Alt+Enter) ........................................................................................................................ 90
Функция ПОДСТАВИТЬ .................................................................................................................................................. 91
Удаление апострофов в начале ячеек ........................................................................................................................ 92
Подсчет количества слов в ячейке .......................................................................................................................... 93
Генерация паролей .................................................................................................................................................. 94
Поиск и замена английских букв в русском тексте ............................................................................................... 95
Способ 1. Шрифт без кириллицы ................................................................................................................................ 95
Способ 2. Подсветка латиницы красным цветом шрифта ................................................................................... 95
Способ 3. Функция IsLatin на VBA ................................................................................................................................. 96
Замена латиницы на кириллицу ................................................................................................................................. 97
Проверка текста на соответствие шаблону ............................................................................................................ 98
ОПЕРАЦИИ С КНИГАМИ И ЛИСТАМИ .......................................................................................................................... 100
Удобная навигация по листам ............................................................................................................................... 101
Горячие клавиши ......................................................................................................................................................... 101
Переход мышью .......................................................................................................................................................... 101
Закладки ....................................................................................................................................................................... 101
Оглавление книги ................................................................................................................................................... 103
Гиперссылки ................................................................................................................................................................. 103
Динамическое оглавление с помощью формул ....................................................................................................... 103
Макрос автоматического оглавления .................................................................................................................... 106
Быстрое копирование листов ................................................................................................................................ 108
Сохранение выбранных листов как отдельных файлов ...................................................................................... 109
Простое разделение листов по файлам ................................................................................................................. 109
Разделение с сохранением ......................................................................................................................................... 109
Сохранение в новые книги только выделенных листов ........................................................................................ 110
Сохранение только выделенных листов в новый файл ......................................................................................... 110
Сборка листов из разных книг в одну ................................................................................................................... 111
Сортировка листов ................................................................................................................................................. 112
Ускорение и облегчение книги Excel .................................................................................................................... 113
Новые форматы файлов ........................................................................................................................................... 113
Используемый диапазон ............................................................................................................................................. 113
Форматирование ........................................................................................................................................................ 114
Изображения и фотографии..................................................................................................................................... 114
Автофигуры ................................................................................................................................................................ 115
Примечания ................................................................................................................................................................. 115
Кэш сводных таблиц .................................................................................................................................................. 115
Журнал изменений (логи) ........................................................................................................................................... 115
Ручной пересчёт формул ........................................................................................................................................... 116
Замена формул на константы ................................................................................................................................. 116
Макросы и формы на VBA .......................................................................................................................................... 117
Именованные диапазоны ........................................................................................................................................... 117
Условное форматирование с формулами ............................................................................................................... 117
Внешние ссылки и связи ............................................................................................................................................. 117
Получение списка файлов в папке ........................................................................................................................ 119
Способ 1. Скелет из шкафа – функция ФАЙЛЫ ....................................................................................................... 119
Способ 2. Готовый макрос ......................................................................................................................................... 120
Способ 3. Надстройка Power Query ........................................................................................................................... 122
Создание резервных копий ценных файлов ........................................................................................................ 126
Как открыть новый Excel в отдельном окне ......................................................................................................... 128
Способ 1. Клавиша Alt ................................................................................................................................................. 128
Способ 2. Командная строка ..................................................................................................................................... 128
Способ 3. Макрос ......................................................................................................................................................... 128
Способ 4. Файл сценария VBScript .............................................................................................................................. 129
Microsoft Excel: Готовые решения ‒ бери и пользуйся! www.PlanetaExcel.ru
Недостатки отдельного экземпляра Excel ............................................................................................................. 129
ЗАЩИТА ДАННЫХ ........................................................................................................................................................ 130
Прячем содержимое ячейки .................................................................................................................................. 131
Защита ячеек листа от изменений ......................................................................................................................... 132
Выборочная защита диапазонов листа для разных пользователей .................................................................. 134
Защита листа с возможностью группировки ........................................................................................................ 135
Защита листов книги ............................................................................................................................................... 137
Шифрование книги ................................................................................................................................................. 138
Вскрытие защиты .................................................................................................................................................... 140
Суперскрытый лист ................................................................................................................................................. 141
Выборочное отображение листов пользователям .............................................................................................. 142
Ограничение рабочей области на листе ............................................................................................................... 144
Скрытие лишних строк и столбцов ......................................................................................................................... 144
Свойство ScrollArea ..................................................................................................................................................... 144
ИНТЕРНЕТ, ЭЛЕКТРОННАЯ ПОЧТА ............................................................................................................................... 146
Организация почтовой рассылки .......................................................................................................................... 147
Постановка задачи ..................................................................................................................................................... 147
Подготовка списка клиентов в Excel ........................................................................................................................ 147
Создаем сообщение в Word и подключаем Excel...................................................................................................... 148
Формируем рассылку .................................................................................................................................................. 151
Отправка книги или листа по электронной почте ................................................................................................ 154
Способ 1. Встроенная отправка ............................................................................................................................... 154
Способ 2. Макросы отправки книги/листа по электронной почте ..................................................................... 154
Способ 3. Создание писем с помощью функции ГИПЕРССЫЛКА ............................................................................. 156
Загрузка данных с сайтов из интернета ................................................................................................................ 158
Веб-запрос курса валют в старых версиях Excel 2007-2013................................................................................... 158
Получение данных из интернета в новых версиях Excel через Power Query ........................................................ 159
Импорт данных из интернета в формате XML ...................................................................................................... 162
Способ 1. Функции ВЕБСЛУЖБА и ФИЛЬТР.XML ........................................................................................................ 162
Способ 2. Прямой импорт XML в старых версиях Excel 2007-2013 ........................................................................ 165
Способ 3. VBA-функция выполнения XML-запроса курса доллара на заданную дату ......................................... 166
ДУБЛИКАТЫ И УНИКАЛЬНЫЕ ...................................................................................................................................... 168
Подсчет количества уникальных значений в диапазоне .................................................................................... 169
Способ 1. Если нет пустых ячеек .............................................................................................................................. 169
Способ 2. Если есть пустые ячейки .......................................................................................................................... 170
Удаление дубликатов строк ................................................................................................................................... 171
Извлечение списка уникальных элементов из диапазона .................................................................................. 172
Способ 1. Сводная таблица ....................................................................................................................................... 172
Способ 2. Формулой..................................................................................................................................................... 173
Способ 3. Функция УНИК ............................................................................................................................................. 173
Выделение дубликатов цветом ............................................................................................................................. 175
В одном столбце ......................................................................................................................................................... 175
В нескольких столбцах ............................................................................................................................................... 176
Внутри ячеек ............................................................................................................................................................... 177
ФОРМАТИРОВАНИЕ ..................................................................................................................................................... 178
Микрографики в ячейках ....................................................................................................................................... 179
Гистограммы условного форматирования ............................................................................................................. 179
Спарклайны .................................................................................................................................................................. 180
Повтор символа N раз ................................................................................................................................................ 182
Выделение цветом ячеек по условию ................................................................................................................... 183
Цветовые шкалы ..................................................................................................................................................... 185
Добавление значков к ячейкам ............................................................................................................................. 186
Microsoft Excel: Готовые решения ‒ бери и пользуйся! www.PlanetaExcel.ru
Наборы значков в условном форматировании ....................................................................................................... 186
Нестандартные символы ......................................................................................................................................... 188
Вставка эмодзи .......................................................................................................................................................... 189
Выделение цветом строки/столбца по условию ................................................................................................. 190
Подсветка дат и сроков .......................................................................................................................................... 193
Простой способ ........................................................................................................................................................... 193
Сложный способ .......................................................................................................................................................... 193
Отделяющие линии между группами строк ........................................................................................................ 198
Подсветка недопустимых значений ..................................................................................................................... 199
Подсветка лишних пробелов ................................................................................................................................. 201
Полосатая заливка строк таблицы "зеброй" ........................................................................................................ 203
Способ 1. Форматировать как таблицу ................................................................................................................. 203
Способ 2. Условное форматирование ...................................................................................................................... 204
Заливка ячеек в шахматном порядке ................................................................................................................... 205
Вставка печатной подложки .................................................................................................................................. 206
Нестандартные форматы ячеек ............................................................................................................................ 209
Маркированный и нумерованный списки ........................................................................................................... 211
Маркированный список форматированием ............................................................................................................ 211
Нумерованный список формулой .............................................................................................................................. 211
SmartArt ........................................................................................................................................................................ 212
Пометка элементов списка флажками (галочками) ............................................................................................ 213
Использование стилей ........................................................................................................................................... 215
Как создать стиль ..................................................................................................................................................... 215
Стиль для целых чисел с разделителями ................................................................................................................ 216
Стили для своих единиц измерения (шт, чел, упак, кг...)....................................................................................... 217
Стили для тысяч (К) и миллионов (М) ..................................................................................................................... 219
Стиль с цветом и процентами для отклонения от плана .................................................................................. 219
Стили со значками и спецсимволами ....................................................................................................................... 221
Стили со сложными условиями и эмодзи ................................................................................................................ 222
Как лучше хранить стили ......................................................................................................................................... 223
Идеи для продолжения ............................................................................................................................................... 223
Скрытие/отображение ненужных строк и столбцов ........................................................................................... 224
Способ 1. Скрытие строк и столбцов ..................................................................................................................... 224
Способ 2. Группировка ................................................................................................................................................ 225
Способ 3. Скрытие помеченных строк/столбцов макросом ................................................................................ 225
Картинка в примечании к ячейке.......................................................................................................................... 228
Способ 1. Одиночная вставка ................................................................................................................................... 228
Способ 2. Массовая вставка картинок в примечания макросом ......................................................................... 228
Вычисления по цвету ячеек ................................................................................................................................... 231
Ограничения и нюансы пересчёта ............................................................................................................................ 232
Подсветка ячеек с формулами и без .................................................................................................................... 233
Способ 1. Выделение по условию ............................................................................................................................... 233
Способ 2. Условное форматирование и макрофункция ......................................................................................... 233
РЕДАКТИРОВАНИЕ ........................................................................................................................................................ 236
Быстрое выделение диапазонов и навигация ..................................................................................................... 237
Быстрое перемещение по листу .............................................................................................................................. 237
Выделение соседних ячеек ......................................................................................................................................... 237
Выделение «до упора» ................................................................................................................................................ 237
Текущая область ........................................................................................................................................................ 237
От начала до конца .................................................................................................................................................... 237
Возврат к первой ячейке выделения ........................................................................................................................ 237
Прыжки по углам выделения ..................................................................................................................................... 237
Автоподбор ширины столбцов ............................................................................................................................. 238
Редактирование сразу нескольких листов ........................................................................................................... 239
Microsoft Excel: Готовые решения ‒ бери и пользуйся! www.PlanetaExcel.ru
Быстрое копирование формул и смарт-теги ........................................................................................................ 240
Слияние данных из двух столбцов в один ............................................................................................................ 241
Превращение строк в столбцы и обратно ............................................................................................................ 242
Способ 1. Специальная вставка ................................................................................................................................ 242
Способ 2. Функция ТРАНСП ......................................................................................................................................... 242
Способ 3. Формируем адрес сами .............................................................................................................................. 243
Копирование только видимых ячеек .................................................................................................................... 244
Вставка в отфильтрованные строки....................................................................................................................... 245
Способ 1. Вставка одинаковых значений или формул ............................................................................................ 245
Способ 2. Макрос вставки любых значений ............................................................................................................. 246
ФОРМУЛЫ .................................................................................................................................................................... 247
Различные типы ссылок на ячейки в формулах ................................................................................................... 248
Относительные ссылки ............................................................................................................................................. 248
Смешанные ссылки ..................................................................................................................................................... 248
Абсолютные ссылки ................................................................................................................................................... 248
Действительно абсолютные ссылки ...................................................................................................................... 249
Зачем нужен стиль ссылок R1C1 в формулах ....................................................................................................... 250
Что это........................................................................................................................................................................ 250
Как это включить/отключить ................................................................................................................................ 250
Кому нужен режим R1C1?........................................................................................................................................... 251
Удобный просмотр формул и результатов одновременно ................................................................................ 252
Отладка формул и поиск ошибок .......................................................................................................................... 254
Режим редактирования ............................................................................................................................................. 254
Отображение стрелок зависимостей .................................................................................................................... 254
Вычисления «на лету» ................................................................................................................................................ 256
Пошаговое выполнение сложных формул ................................................................................................................ 256
Отслеживание результатов вычислений ............................................................................................................... 256
Обработка ошибок в формулах ............................................................................................................................. 258
Перехват ошибок ........................................................................................................................................................ 258
Скрытие ошибок на экране ....................................................................................................................................... 258
Скрытие ошибок при печати .................................................................................................................................... 259
Замена формул на их значения ............................................................................................................................. 260
Точное копирование формул без сдвига ссылок ................................................................................................. 262
Способ 1. Абсолютные ссылки .................................................................................................................................. 262
Способ 2. Временная деактивация формул ............................................................................................................. 263
Способ 3. Копирование через Блокнот ..................................................................................................................... 263
Способ 4. Макрос ......................................................................................................................................................... 265
Именованные диапазоны в формулах .................................................................................................................. 266
Создание именованного диапазона .......................................................................................................................... 266
Использование имен диапазонов в формулах.......................................................................................................... 267
Локальные и глобальные имена ................................................................................................................................ 267
Именованные константы ......................................................................................................................................... 268
Вычисления без формул ......................................................................................................................................... 269
Специальная вставка ................................................................................................................................................. 269
Строка состояния ...................................................................................................................................................... 270
Калькулятор ................................................................................................................................................................ 270
ОПЕРАЦИИ С ДИАПАЗОНАМИ ДАННЫХ ..................................................................................................................... 272
Удаление пустых ячеек в диапазоне ..................................................................................................................... 273
Способ 1. Грубо и быстро ........................................................................................................................................... 273
Способ 2. Формула массива ........................................................................................................................................ 273
Способ 3. Пользовательская функция на VBA ......................................................................................................... 274
Удаление пустых строк и столбцов в данных ....................................................................................................... 276
Способ 1. Поиск пустых ячеек .................................................................................................................................... 276
Microsoft Excel: Готовые решения ‒ бери и пользуйся! www.PlanetaExcel.ru
Способ 2. Поиск незаполненных строк ..................................................................................................................... 277
Способ 3. Удаление пустых строк/столбцов на всем листе макросом .............................................................. 278
Способ 3. Удаление пустых строк в выделенном диапазоне макросом .............................................................. 278
Заполнение пустых ячеек ...................................................................................................................................... 279
Способ 1. Формулами .................................................................................................................................................. 279
Способ 2. Макросом .................................................................................................................................................... 280
Способ 3. Power Query ................................................................................................................................................. 280
Сортировка диапазона ........................................................................................................................................... 282
Простая сортировка.................................................................................................................................................. 282
Многоуровневая сортировка .................................................................................................................................... 282
Сортировка по цвету................................................................................................................................................. 282
Сортировка по смыслу, а не по алфавиту .............................................................................................................. 283
Сортировка текста и чисел одновременно............................................................................................................ 286
Сортировка диапазона формулами ...................................................................................................................... 288
Способ 1. Числовые данные........................................................................................................................................ 288
Способ 2. Текстовый список и обычные формулы .................................................................................................. 288
Способ 3. Формула массива ....................................................................................................................................... 290
Способ 4. Новая функция СОРТ .................................................................................................................................. 290
Сборка данных из нескольких одинаковых таблиц ............................................................................................ 292
Простые формулы ...................................................................................................................................................... 292
Трехмерные формулы ................................................................................................................................................. 292
Функция ДВССЫЛ ......................................................................................................................................................... 293
Сборка данных из нескольких разных таблиц ..................................................................................................... 294
Превращение простого диапазона в «умную» таблицу ..................................................................................... 297
Имена и ссылки на содержимое ................................................................................................................................ 298
Вычисляемые столбцы .............................................................................................................................................. 298
Автоподстройка размеров ....................................................................................................................................... 299
Закрепление шапки и фильтры ................................................................................................................................ 299
Строка итогов ............................................................................................................................................................ 299
Внешний вид ................................................................................................................................................................ 300
Фильтрация срезами .................................................................................................................................................. 300
Разделение таблицы по листам ............................................................................................................................ 301
Подготовка ................................................................................................................................................................. 301
Макрос для деления .................................................................................................................................................... 302
Случайная выборка данных из диапазона ........................................................................................................... 303
Способ 1. Случайная сортировка .............................................................................................................................. 303
Способ 2. Функция НАИМЕНЬШИЙ ............................................................................................................................ 304
Способ 3. Случайная выборка без повторов – функция Lotto на VBA ................................................................... 304
Выборочное суммирование из диапазона по 1-2-3… критериям ...................................................................... 306
Способ 1. Функция СУММЕСЛИ, когда одно условие ............................................................................................... 306
Способ 2. Функция СУММЕСЛИМН, когда условий много ....................................................................................... 307
Способ 3. Столбец-индикатор .................................................................................................................................. 307
Способ 4. Формула массива ....................................................................................................................................... 308
Способ 5. Функция баз данных БДСУММ .................................................................................................................. 309
ПОИСК И ПОДСТАНОВКА ДАННЫХ .............................................................................................................................. 310
Найти и заменить ................................................................................................................................................... 311
Простой поиск ............................................................................................................................................................ 311
Неточный поиск по маске .......................................................................................................................................... 311
Поиск по формату ...................................................................................................................................................... 312
Работа со списком через форму............................................................................................................................ 313
Автофильтр ............................................................................................................................................................. 315
Простая фильтрация ................................................................................................................................................ 315
Фильтрация текста .................................................................................................................................................. 315
Фильтрация дат ........................................................................................................................................................ 316
Microsoft Excel: Готовые решения ‒ бери и пользуйся! www.PlanetaExcel.ru
Фильтрация чисел ...................................................................................................................................................... 317
Фильтрация по цвету ................................................................................................................................................ 318
Фильтрация срезами .............................................................................................................................................. 320
Подстановка с помощью функции ВПР (VLOOKUP) .............................................................................................. 322
Ошибки #Н/Д и их подавление ................................................................................................................................... 323
Приблизительный поиск чисел и дат с помощью функции ВПР (VLOOKUP) ..................................................... 325
Улучшенный вариант функции ВПР (VLOOKUP) на VBA ....................................................................................... 328
Поиск данных в таблице с помощью функции ИНДЕКС и ПОИСКПОЗ ............................................................... 330
Новая функция ПРОСМОТРХ на замену ВПР ......................................................................................................... 331
Выборка сразу всех искомых значений из таблицы ............................................................................................ 333
Способ 1. Новая функция ФИЛЬТР ............................................................................................................................. 333
Способ 2. Формула массива ........................................................................................................................................ 334
Двумерный поиск в таблице .................................................................................................................................. 335
Вариант 1. Точный поиск ........................................................................................................................................... 335
Вариант 2. Приблизительный поиск ........................................................................................................................ 336
Поиск в таблице с учетом регистра ....................................................................................................................... 337
Динамическая гиперссылка для быстрого перехода из одной таблицы в другую ........................................... 340
Улучшение 1. Переход к нужному столбцу .............................................................................................................. 342
Улучшение 2. Красивый символ ссылки ..................................................................................................................... 342
СВОДНЫЕ ТАБЛИЦЫ .................................................................................................................................................... 343
Создание отчетов с помощью сводных таблиц .................................................................................................... 344
Исходные данные ........................................................................................................................................................ 344
Создание отчета ........................................................................................................................................................ 344
Настройка внешнего вида ......................................................................................................................................... 346
Обновление и пересчет .............................................................................................................................................. 347
Фильтрация сводной таблицы срезами ................................................................................................................ 349
Фильтрация дат с помощью временной шкалы................................................................................................... 353
Группировка данных в сводных таблицах ............................................................................................................ 354
Группировка чисел ....................................................................................................................................................... 354
Группировка дат ......................................................................................................................................................... 355
Произвольная группировка ........................................................................................................................................ 356
Независимая группировка сводных таблиц ......................................................................................................... 358
Вариант 1. Строим независимую сводную через Мастер .................................................................................... 359
Вариант 2. Отвязываем уже созданную сводную от общего кэша .................................................................... 360
Настройка вычислений в сводных таблицах ........................................................................................................ 361
Другие функции расчета вместо суммы ................................................................................................................. 361
Доли и проценты ........................................................................................................................................................ 363
Отличие и приведенное отличие ............................................................................................................................. 366
Ранжирование ............................................................................................................................................................. 367
Индекс влияния ............................................................................................................................................................ 368
Детализация результатов ....................................................................................................................................... 370
Просмотр подробностей .......................................................................................................................................... 370
Разделение сводной таблицы по листам ................................................................................................................ 370
БЛАГОДАРНОСТИ ......................................................................................................................................................... 373