Microsoft Excel: Готовые решения - бери и пользуйся! Год издания: 2014 Автор: Павлов Н. Издательство: Книга по Требованию ISBN: 978-5-519-01837-1 Язык: Русский Формат: PDF Качество: Издательский макет или текст (eBook) Интерактивное оглавление: Да Количество страниц: 382 Описание: Эта книга представляет собой полноценный сборник готовых решений самых распространенных проблем и задач, с которыми сталкиваются в повседневной работе пользователи Microsoft Excel. Материал книги охватывает все основные направления и темы: работу с текстом и датами, форматирование, вычисления с помощью формул и функций, обработку больших массивов данных и создание по ним аналитических отчетов с помощью сводных таблиц. Рекомендуется пользователям Microsoft Excel любого уровня.
Примеры страниц
Оглавление
Оглавление КОРОТКОЕ ПРЕДИСЛОВИЕ ............................................................................................................................................................ 4 ФАЙЛЫ ПРИМЕРОВ И ВИДЕОУРОКИ ......................................................................................................................................... 12 ВЫПАДАЮЩИЕ СПИСКИ ............................................................................................................................................................. 13 КАК СОЗДАТЬ ВЫПАДАЮЩИЙ СПИСОК ..................................................................................................................................................... 14 Способ 1. Быстрый .................................................................................................................................................................... 14 Способ 2. Стандартный ........................................................................................................................................................... 14 Способ 3. Элемент управления ................................................................................................................................................ 16 Способ 4. Элемент ActiveX ........................................................................................................................................................ 18 Итоговая сравнительная таблица всех способов ................................................................................................................ 20 СВЯЗАННЫЕ (ЗАВИСИМЫЕ) ВЫПАДАЮЩИЕ СПИСКИ ................................................................................................................................... 21 Способ 1. Функция ДВССЫЛ (INDIRECT) ..................................................................................................................................... 21 Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH) .......................................................... 22 ВЫПАДАЮЩИЙ СПИСОК ДЛЯ ВЫБОРА ИЗОБРАЖЕНИЙ ТОВАРОВ .................................................................................................................. 25 Шаг 1. Создаем каталог с фото и даем ему имя ................................................................................................................... 25 Шаг 2. Выпадающий список для выбора модели .................................................................................................................... 26 Шаг 3. Копируем фотографию ................................................................................................................................................. 26 Шаг 4. Создаем динамическую ссылку на выбранную фотографию ................................................................................... 27 Шаг 5. Привязываем фотографию к ссылке ........................................................................................................................... 28 ВЫПАДАЮЩИЙ СПИСОК С АВТОМАТИЧЕСКИМ ДОБАВЛЕНИЕМ ОТСУТСТВУЮЩИХ ЭЛЕМЕНТОВ ......................................................................... 29 Шаг 1. Создаем именованный диапазон .................................................................................................................................. 29 Шаг 2. Создаем выпадающий список в ячейке ........................................................................................................................ 29 Шаг 3. Добавляем простой макрос ......................................................................................................................................... 30 ВЫПАДАЮЩИЙ СПИСОК С УДАЛЕНИЕМ ИСПОЛЬЗОВАННЫХ ЭЛЕМЕНТОВ ....................................................................................................... 32 Постановка задачи .................................................................................................................................................................... 32 Шаг 1. Кто сколько работает? ............................................................................................................................................... 32 Шаг 2. Кто еще свободен? ........................................................................................................................................................ 32 Шаг 3. Формируем список ......................................................................................................................................................... 33 Шаг 4. Создаем именованный диапазон свободных сотрудников ....................................................................................... 33 Шаг 5. Создаем выпадающий список в ячейках ...................................................................................................................... 34 ВЫПАДАЮЩИЙ СПИСОК НА ОСНОВЕ ДИНАМИЧЕСКОГО ДИАПАЗОНА ............................................................................................................ 35 ВЫПАДАЮЩИЙ СПИСОК С ДАННЫМИ ИЗ ДРУГОГО ФАЙЛА .......................................................................................................................... 38 Способ 1. Функция ДВССЫЛ ....................................................................................................................................................... 38 Способ 2. Импорт данных ......................................................................................................................................................... 38 ДАТЫ И ВРЕМЯ ........................................................................................................................................................................... 42 КАК ПРАВИЛЬНО ВВОДИТЬ ДАТЫ И ВРЕМЯ ............................................................................................................................................... 43 ВСПЛЫВАЮЩИЕ КАЛЕНДАРИ ДЛЯ БЫСТРОГО ВВОДА ДАТЫ ......................................................................................................................... 44 БЫСТРЫЙ ВВОД ДАТЫ БЕЗ РАЗДЕЛИТЕЛЕЙ ................................................................................................................................................ 46 АВТОМАТИЧЕСКАЯ ФИКСАЦИЯ ТЕКУЩЕЙ ДАТЫ ПРИ ВВОДЕ ДАННЫХ ............................................................................................................. 47 ОСОБЕННОСТИ КОПИРОВАНИЯ ДАТ ......................................................................................................................................................... 48 БЫСТРЫЙ ВВОД ТЕКУЩЕЙ ДАТЫ ............................................................................................................................................................. 49 КАК EXCEL НА САМОМ ДЕЛЕ ХРАНИТ И ОБРАБАТЫВАЕТ ДАТЫ И ВРЕМЯ ......................................................................................................... 50 ЧИСЛО ДНЕЙ МЕЖДУ ДВУМЯ ДАТАМИ ..................................................................................................................................................... 51 Как посчитать количество дней между двумя датами ...................................................................................................... 51 Как посчитать количество рабочих дней между двумя датами ....................................................................................... 51 КАК ВЫДЕЛИТЬ ИЗ ДАТЫ ДЕНЬ, МЕСЯЦ И ГОД ОТДЕЛЬНО ............................................................................................................................ 52 КАК СОБРАТЬ ДАТУ ИЗ ФРАГМЕНТОВ (ДЕНЬ, МЕСЯЦ, ГОД) ........................................................................................................................... 53 СДВИГ ДАТ .......................................................................................................................................................................................... 54 Сдвиг даты на N дней в будущее или прошлое ...................................................................................................................... 54 Сдвиг даты на N рабочих (банковских) дней .......................................................................................................................... 54 Сдвиг даты на N месяцев .......................................................................................................................................................... 54 Сдвиг даты на N лет................................................................................................................................................................. 54 УНИВЕРСАЛЬНЫЙ КАЛЕНДАРЬ ФОРМУЛОЙ ............................................................................................................................................... 55 ВЫЧИСЛЕНИЕ ДНЯ НЕДЕЛИ ПО ДАТЕ ........................................................................................................................................................ 57 ВЫЧИСЛЕНИЕ ВРЕМЕННЫХ ИНТЕРВАЛОВ .................................................................................................................................................. 58 ВЫЧИСЛЕНИЕ ВОЗРАСТА ИЛИ СТАЖА ....................................................................................................................................................... 59 НОМЕР НЕДЕЛИ ПО ДАТЕ ....................................................................................................................................................................... 60 Способ 1. Стандарт ГОСТ ИСО 8601-2001 ............................................................................................................................... 60 Способ 2. Неделя с 1-м января .................................................................................................................................................. 60 НОМЕР КВАРТАЛА ПО ДАТЕ .................................................................................................................................................................... 62 ПРЕОБРАЗОВАНИЕ ТЕКСТОВОЙ ДАТЫ В ПОЛНОЦЕННУЮ ДАТУ ...................................................................................................................... 63 ВЫЧИСЛЕНИЕ НУЖНОЙ ДАТЫ ПО ФОРМУЛЕ .............................................................................................................................................. 64 РАБОТА С ТЕКСТОМ .................................................................................................................................................................... 65 СКЛЕИВАНИЕ ТЕКСТА ИЗ НЕСКОЛЬКИХ ЯЧЕЕК ............................................................................................................................................. 66 Способ 1. Функция СЦЕПИТЬ ...................................................................................................................................................... 66 Способ 2. Символ для склеивания текста (&) ........................................................................................................................ 66 Способ 3. Макрос для объединения ячеек без потери текста ............................................................................................ 66 ИЗВЛЕЧЕНИЕ СИМВОЛОВ ИЗ ТЕКСТОВОЙ СТРОКИ ...................................................................................................................................... 68 ИЗВЛЕЧЕНИЕ N-ГО ПО СЧЕТУ СЛОВА ИЗ ЯЧЕЙКИ ........................................................................................................................................ 69 Способ 1. Формулами ................................................................................................................................................................. 69 Способ 2. Пользовательская макрофункция .......................................................................................................................... 69 ФАМИЛИЯ И.О. ИЗ ФИО ...................................................................................................................................................................... 71 РАЗДЕЛЕНИЕ "СЛИПШЕГОСЯ" ТЕКСТА ПО СТОЛБЦАМ ................................................................................................................................. 72 ПЕРЕВОД ТЕКСТА В ТРАНСЛИТ (НИКОЛАЙ В NIKOLAY И Т.Д.) ....................................................................................................................... 76 ЧИСЛА КАК ТЕКСТ ................................................................................................................................................................................. 77 Когда это хорошо и когда плохо .............................................................................................................................................. 77 ПРЕВРАЩЕНИЕ ЧИСЕЛ-КАК-ТЕКСТ В ПОЛНОЦЕННЫЕ ЧИСЛА ......................................................................................................................... 79 Способ 1. Преобразование в число ........................................................................................................................................... 79 Способ 2. Умножение на единицу ............................................................................................................................................. 79 УДАЛЕНИЕ ИЗ ТЕКСТА ЛИШНИХ СИМВОЛОВ И ПРОБЕЛОВ ............................................................................................................................ 80 Замена ......................................................................................................................................................................................... 80 Удаление пробелов .................................................................................................................................................................... 80 Удаление непечатаемых символов ......................................................................................................................................... 81 Функция ПОДСТАВИТЬ ............................................................................................................................................................... 81 Удаление апострофов в начале ячеек ..................................................................................................................................... 82 ПОДСЧЕТ КОЛИЧЕСТВА СЛОВ В ЯЧЕЙКЕ .................................................................................................................................................... 83 ГЕНЕРАЦИЯ ПАРОЛЕЙ ............................................................................................................................................................................ 84 ПОИСК И ЗАМЕНА АНГЛИЙСКИХ БУКВ В РУССКОМ ТЕКСТЕ ............................................................................................................................ 85 Способ 1. Шрифт без кириллицы ............................................................................................................................................. 85 Способ 2. Подсветка латиницы красным цветом шрифта ................................................................................................ 85 Способ 3. Функция IsLatin на VBA .............................................................................................................................................. 86 Замена латиницы на кириллицу .............................................................................................................................................. 87 ПРОВЕРКА ТЕКСТА НА СООТВЕТСТВИЕ ШАБЛОНУ ........................................................................................................................................ 88 ОПЕРАЦИИ С КНИГАМИ И ЛИСТАМИ ......................................................................................................................................... 90 УДОБНАЯ НАВИГАЦИЯ ПО ЛИСТАМ .......................................................................................................................................................... 91 Горячие клавиши ........................................................................................................................................................................ 91 Переход мышью ......................................................................................................................................................................... 91 Закладки ...................................................................................................................................................................................... 91 ОГЛАВЛЕНИЕ КНИГИ ............................................................................................................................................................................. 93 Гиперссылки ................................................................................................................................................................................ 93 Динамическое оглавление с помощью формул ...................................................................................................................... 94 Макрос автоматического оглавления ................................................................................................................................... 96 БЫСТРОЕ КОПИРОВАНИЕ ЛИСТОВ ............................................................................................................................................................ 98 СОХРАНЕНИЕ ВЫБРАННЫХ ЛИСТОВ КАК ОТДЕЛЬНЫХ ФАЙЛОВ ..................................................................................................................... 99 Простое разделение листов по файлам ................................................................................................................................ 99 Разделение с сохранением ........................................................................................................................................................ 99 Сохранение в новые книги только выделенных листов ..................................................................................................... 100 Сохранение только выделенных листов в новый файл ...................................................................................................... 100 СБОРКА ЛИСТОВ ИЗ РАЗНЫХ КНИГ В ОДНУ .............................................................................................................................................. 101 СОРТИРОВКА ЛИСТОВ .......................................................................................................................................................................... 102 ОПТИМИЗАЦИЯ БЫСТРОДЕЙСТВИЯ........................................................................................................................................................ 103 Новые форматы файлов Excel 2007–2013 ............................................................................................................................. 103 Используемый диапазон .......................................................................................................................................................... 103 Форматирование ..................................................................................................................................................................... 104 Картинки .................................................................................................................................................................................. 104 Примечания .............................................................................................................................................................................. 105 Кэш сводных таблиц ............................................................................................................................................................... 105 Журнал изменений (логи) ........................................................................................................................................................ 105 Макросы и формы на VBA ....................................................................................................................................................... 106 Ручной пересчет формул ........................................................................................................................................................ 106 Замена формул на константы .............................................................................................................................................. 106 Внешние ссылки........................................................................................................................................................................ 107 Именованные диапазоны ........................................................................................................................................................ 107 Условное форматирование с формулами............................................................................................................................. 107 ПОЛУЧЕНИЕ СПИСКА ФАЙЛОВ В ПАПКЕ .................................................................................................................................................. 109 Способ 1. Формулы ................................................................................................................................................................... 109 Способ 2. Макрос ...................................................................................................................................................................... 110 СОЗДАНИЕ РЕЗЕРВНЫХ КОПИЙ ЦЕННЫХ ФАЙЛОВ ..................................................................................................................................... 112 ЗАЩИТА ДАННЫХ ..................................................................................................................................................................... 114 ПРЯЧЕМ СОДЕРЖИМОЕ ЯЧЕЙКИ ............................................................................................................................................................ 115 ЗАЩИТА ЯЧЕЕК ЛИСТА ОТ ИЗМЕНЕНИЙ ................................................................................................................................................... 116 ВЫБОРОЧНАЯ ЗАЩИТА ДИАПАЗОНОВ ЛИСТА ДЛЯ РАЗНЫХ ПОЛЬЗОВАТЕЛЕЙ ................................................................................................ 118 ЗАЩИТА ЛИСТА С СОХРАНЕНИЕМ ГРУППИРОВКИ ...................................................................................................................................... 119 ЗАЩИТА ЛИСТОВ КНИГИ ...................................................................................................................................................................... 121 ШИФРОВАНИЕ КНИГИ ......................................................................................................................................................................... 122 ВСКРЫТИЕ ЗАЩИТЫ ............................................................................................................................................................................ 124 СУПЕРСКРЫТЫЙ ЛИСТ ......................................................................................................................................................................... 125 ВЫБОРОЧНОЕ ОТОБРАЖЕНИЕ ЛИСТОВ ПОЛЬЗОВАТЕЛЯМ .......................................................................................................................... 127 ОГРАНИЧЕНИЕ РАБОЧЕЙ ОБЛАСТИ НА ЛИСТЕ ........................................................................................................................................... 129 Скрытие лишних строк и столбцов ..................................................................................................................................... 129 Свойство ScrollArea .................................................................................................................................................................. 129 ИНТЕРНЕТ, ЭЛЕКТРОННАЯ ПОЧТА ............................................................................................................................................ 131 ВЕБ-ОПРОС С ПОМОЩЬЮ EXCEL И SKYDRIVE ........................................................................................................................................... 132 Шаг 1. Создаем опрос в OneDrive ............................................................................................................................................ 132 Шаг 2. Создаем вопросы .......................................................................................................................................................... 133 Шаг 3. Публикация опроса и сбор данных ............................................................................................................................. 134 ОРГАНИЗАЦИЯ ПОЧТОВОЙ РАССЫЛКИ ................................................................................................................................................... 138 Постановка задачи .................................................................................................................................................................. 138 Подготовка списка клиентов в Excel .................................................................................................................................... 138 Создаем сообщение в Word и подключаем Excel .................................................................................................................. 139 СОЗДАНИЕ ПИСЕМ С ПОМОЩЬЮ ФУНКЦИИ ГИПЕРССЫЛКА ................................................................................................................... 145 ИМПОРТ КУРСА ВАЛЮТ С САЙТА ........................................................................................................................................................... 147 Способ 1. Простой веб-запрос для текущего курса валют................................................................................................ 147 Способ 2. Параметрический веб-запрос для получения курса валют на заданный интервал дат .............................. 149 Способ 3. Импорт XML ............................................................................................................................................................ 153 ФУНКЦИЯ ЗАПРОСА КУРСА ДОЛЛАРА НА ЗАДАННУЮ ДАТУ......................................................................................................................... 156 ОТПРАВКА КНИГИ ИЛИ ЛИСТА ПО ЭЛЕКТРОННОЙ ПОЧТЕ ........................................................................................................................... 157 Способ 1. Встроенная отправка ............................................................................................................................................ 157 Способ 2. Макросы отправки книги/листа по электронной почте ................................................................................. 158 ДУБЛИКАТЫ И УНИКАЛЬНЫЕ ................................................................................................................................................... 160 ПОДСЧЕТ КОЛИЧЕСТВА УНИКАЛЬНЫХ ЗНАЧЕНИЙ В ДИАПАЗОНЕ ................................................................................................................. 161 Способ 1. Если нет пустых ячеек ........................................................................................................................................... 161 Способ 2. Если есть пустые ячейки ....................................................................................................................................... 162 УДАЛЕНИЕ ДУБЛИКАТОВ СТРОК ............................................................................................................................................................ 164 ИЗВЛЕЧЕНИЕ СПИСКА УНИКАЛЬНЫХ ЭЛЕМЕНТОВ ИЗ ДИАПАЗОНА ............................................................................................................... 165 Способ 1. Сводная таблица .................................................................................................................................................... 165 Способ 2. Формулой ................................................................................................................................................................. 166 ВЫДЕЛЕНИЕ ДУБЛИКАТОВ ЦВЕТОМ ....................................................................................................................................................... 168 В одном столбце ...................................................................................................................................................................... 168 В нескольких столбцах ............................................................................................................................................................ 169 ФОРМАТИРОВАНИЕ .................................................................................................................................................................. 171 МИКРОГРАФИКИ В ЯЧЕЙКАХ ................................................................................................................................................................. 172 Гистограммы ........................................................................................................................................................................... 172 Спарклайны ............................................................................................................................................................................... 173 Повтор символа N раз............................................................................................................................................................. 175 ВЫДЕЛЕНИЕ ЦВЕТОМ ЯЧЕЕК ПО УСЛОВИЮ .............................................................................................................................................. 176 ЦВЕТОВЫЕ ШКАЛЫ ............................................................................................................................................................................. 179 ДОБАВЛЕНИЕ ЗНАЧКОВ К ЯЧЕЙКАМ ....................................................................................................................................................... 180 Наборы значков в условном форматировании .................................................................................................................... 180 Нестандартные символы ...................................................................................................................................................... 182 ВЫДЕЛЕНИЕ ЦВЕТОМ СТРОКИ/СТОЛБЦА ПО УСЛОВИЮ ............................................................................................................................. 185 ПОДСВЕТКА ДАТ И СРОКОВ ................................................................................................................................................................... 189 Простой способ ........................................................................................................................................................................ 189 Сложный способ ....................................................................................................................................................................... 189 ОТДЕЛЯЮЩИЕ ЛИНИИ МЕЖДУ ГРУППАМИ СТРОК .................................................................................................................................... 194 ПОДСВЕТКА НЕДОПУСТИМЫХ ЗНАЧЕНИЙ ................................................................................................................................................ 196 ПОДСВЕТКА ЛИШНИХ ПРОБЕЛОВ ........................................................................................................................................................... 198 ПОЛОСАТАЯ ЗАЛИВКА СТРОК ТАБЛИЦЫ "ЗЕБРОЙ" ................................................................................................................................... 200 Способ 1. Форматировать как таблицу .............................................................................................................................. 200 Способ 2. Условное форматирование ................................................................................................................................... 201 ЗАЛИВКА ЯЧЕЕК В ШАХМАТНОМ ПОРЯДКЕ .............................................................................................................................................. 203 ВСТАВКА ПЕЧАТНОЙ ПОДЛОЖКИ ........................................................................................................................................................... 204 НЕСТАНДАРТНЫЕ ФОРМАТЫ ЯЧЕЕК........................................................................................................................................................ 208 МАРКИРОВАННЫЙ И НУМЕРОВАННЫЙ СПИСКИ ....................................................................................................................................... 211 Маркированный список форматированием ......................................................................................................................... 211 Нумерованный список формулой ........................................................................................................................................... 211 SmartArt ..................................................................................................................................................................................... 212 СКРЫТИЕ/ОТОБРАЖЕНИЕ НЕНУЖНЫХ СТРОК И СТОЛБЦОВ ........................................................................................................................ 213 Способ 1. Скрытие строк и столбцов ................................................................................................................................... 213 Способ 2. Группировка ............................................................................................................................................................. 214 Способ 3. Скрытие помеченных строк/столбцов макросом ............................................................................................. 214 КАРТИНКА В ПРИМЕЧАНИИ К ЯЧЕЙКЕ ..................................................................................................................................................... 217 Способ 1. Одиночная вставка ................................................................................................................................................. 217 Способ 2. Вставка картинок оптом ..................................................................................................................................... 218 СУММА ЯЧЕЕК ПО ЦВЕТУ ...................................................................................................................................................................... 220 Цвет шрифта .......................................................................................................................................................................... 220 Количество вместо суммы .................................................................................................................................................... 220 Нюансы пересчета .................................................................................................................................................................. 221 ПОДСВЕТКА ЯЧЕЕК С ФОРМУЛАМИ И БЕЗ ................................................................................................................................................ 222 Способ 1. Выделение по условию ............................................................................................................................................ 222 Способ 2. Условное форматирование и макрофункция ...................................................................................................... 222 ПОМЕТКА ЭЛЕМЕНТОВ СПИСКА ФЛАЖКАМИ (ГАЛОЧКАМИ) ....................................................................................................................... 225 РЕДАКТИРОВАНИЕ .................................................................................................................................................................... 227 БЫСТРОЕ ВЫДЕЛЕНИЕ ДИАПАЗОНОВ И НАВИГАЦИЯ ................................................................................................................................. 228 Быстрое перемещение по листу ........................................................................................................................................... 228 Выделение соседних ячеек ...................................................................................................................................................... 228 Выделение "до упора" .............................................................................................................................................................. 228 Текущая область ..................................................................................................................................................................... 228 От начала до конца ................................................................................................................................................................. 228 АВТОПОДБОР ШИРИНЫ СТОЛБЦОВ ....................................................................................................................................................... 229 РЕДАКТИРОВАНИЕ СРАЗУ НЕСКОЛЬКИХ ЛИСТОВ ....................................................................................................................................... 230 БЫСТРОЕ КОПИРОВАНИЕ ФОРМУЛ И СМАРТ-ТЕГИ .................................................................................................................................... 231 СЛИЯНИЕ ДАННЫХ ИЗ ДВУХ СТОЛБЦОВ В ОДИН ....................................................................................................................................... 233 ПРЕВРАЩЕНИЕ СТРОК В СТОЛБЦЫ И ОБРАТНО ......................................................................................................................................... 234 Способ 1. Специальная вставка ............................................................................................................................................. 234 Способ 2. Функция ТРАНСП ...................................................................................................................................................... 234 Способ 3. Формируем адрес сами ........................................................................................................................................... 235 КОПИРОВАНИЕ ТОЛЬКО ВИДИМЫХ ЯЧЕЕК............................................................................................................................................... 237 ВСТАВКА В ОТФИЛЬТРОВАННЫЕ СТРОКИ ................................................................................................................................................ 239 Способ 1. Вставка одинаковых значений или формул ......................................................................................................... 239 Способ 2. Макрос вставки любых значений ......................................................................................................................... 240 ФОРМУЛЫ ................................................................................................................................................................................. 241 РАЗЛИЧНЫЕ ТИПЫ ССЫЛОК НА ЯЧЕЙКИ В ФОРМУЛАХ ............................................................................................................................... 242 Относительные ссылки .......................................................................................................................................................... 242 Смешанные ссылки .................................................................................................................................................................. 242 Абсолютные ссылки ................................................................................................................................................................ 242 Действительно абсолютные ссылки ................................................................................................................................... 243 ЗАЧЕМ НУЖЕН СТИЛЬ ССЫЛОК R1C1 В ФОРМУЛАХ .................................................................................................................................. 244 Что это .................................................................................................................................................................................... 244 Как это включить/отключить ............................................................................................................................................. 244 Где режим R1C1 может быть полезен ................................................................................................................................. 245 УДОБНЫЙ ПРОСМОТР ФОРМУЛ И РЕЗУЛЬТАТОВ ОДНОВРЕМЕННО .............................................................................................................. 247 ОТЛАДКА ФОРМУЛ И ПОИСК ОШИБОК ................................................................................................................................................... 249 Режим редактирования .......................................................................................................................................................... 249 Отображение стрелок зависимостей ................................................................................................................................. 249 Вычисления "на лету" ............................................................................................................................................................. 251 Пошаговое выполнение сложных формул ............................................................................................................................ 251 Отслеживание результатов вычислений ............................................................................................................................ 252 ОБРАБОТКА ОШИБОК В ФОРМУЛАХ ....................................................................................................................................................... 253 Перехват ошибок .................................................................................................................................................................... 253 Скрытие ошибок на экране .................................................................................................................................................... 253 Скрытие ошибок при печати ................................................................................................................................................. 254 ЗАМЕНА ФОРМУЛ НА ИХ ЗНАЧЕНИЯ ....................................................................................................................................................... 256 ТОЧНОЕ КОПИРОВАНИЕ ФОРМУЛ БЕЗ СДВИГА ССЫЛОК ............................................................................................................................. 258 Способ 1. Абсолютные ссылки ............................................................................................................................................... 258 Способ 2. Временная деактивация формул .......................................................................................................................... 259 Способ 3. Копирование через Блокнот .................................................................................................................................. 259 Способ 4. Макрос ...................................................................................................................................................................... 261 ИМЕНОВАННЫЕ ДИАПАЗОНЫ В ФОРМУЛАХ ............................................................................................................................................ 263 Создание именованного диапазона ....................................................................................................................................... 263 Использование имен диапазонов в формулах ...................................................................................................................... 264 Локальные и глобальные имена ............................................................................................................................................. 264 Именованные константы ...................................................................................................................................................... 265 ВЫЧИСЛЕНИЯ БЕЗ ФОРМУЛ .................................................................................................................................................................. 266 Специальная вставка .............................................................................................................................................................. 266 Строка состояния ................................................................................................................................................................... 267 Калькулятор ............................................................................................................................................................................. 268 ОПЕРАЦИИ С ДИАПАЗОНАМИ ДАННЫХ .................................................................................................................................. 269 УДАЛЕНИЕ ПУСТЫХ ЯЧЕЕК В ДИАПАЗОНЕ ................................................................................................................................................ 270 Способ 1. Грубо и быстро ........................................................................................................................................................ 270 Способ 2. Формула массива .................................................................................................................................................... 270 Способ 3. Пользовательская функция на VBA ...................................................................................................................... 271 УДАЛЕНИЕ ПУСТЫХ СТРОК .................................................................................................................................................................... 273 ЗАПОЛНЕНИЕ ПУСТЫХ ЯЧЕЕК ................................................................................................................................................................ 274 СОРТИРОВКА ДИАПАЗОНА.................................................................................................................................................................... 276 Простая сортировка ............................................................................................................................................................... 276 Многоуровневая сортировка ................................................................................................................................................. 276 Сортировка по цвету .............................................................................................................................................................. 277 Сортировка по смыслу, а не по алфавиту............................................................................................................................ 277 Сортировка текста и чисел одновременно ......................................................................................................................... 280 СОРТИРОВКА ДИАПАЗОНА ФОРМУЛОЙ .................................................................................................................................................. 282 Способ 1. Числовые данные ..................................................................................................................................................... 282 Способ 2. Текстовый список и обычные формулы ............................................................................................................... 282 Способ 3. Формула массива .................................................................................................................................................... 284 СБОРКА ДАННЫХ ИЗ НЕСКОЛЬКИХ ОДИНАКОВЫХ ТАБЛИЦ ......................................................................................................................... 286 Простые формулы ................................................................................................................................................................... 286 Трехмерные формулы .............................................................................................................................................................. 286 Функция ДВССЫЛ (INDIRECT) ................................................................................................................................................... 287 СБОРКА ДАННЫХ ИЗ НЕСКОЛЬКИХ РАЗНЫХ ТАБЛИЦ ................................................................................................................................. 288 ПРЕВРАЩЕНИЕ ПРОСТОГО ДИАПАЗОНА В "УМНУЮ" ТАБЛИЦУ ................................................................................................................... 292 СЛУЧАЙНАЯ ВЫБОРКА ДАННЫХ ИЗ ДИАПАЗОНА ...................................................................................................................................... 295 Способ 1. Случайная сортировка ........................................................................................................................................... 295 Способ 2. Функция НАИМЕНЬШИЙ.......................................................................................................................................... 296 Способ 3. Случайная выборка без повторов – функция Lotto на VBA ................................................................................ 296 ВЫБОРОЧНОЕ СУММИРОВАНИЕ ИЗ ДИАПАЗОНА ПО 1-2-3… КРИТЕРИЯМ .................................................................................................... 298 Способ 1. Функция СУММЕСЛИ, когда одно условие............................................................................................................. 298 Способ 2. Функция СУММЕСЛИМН, когда условий много .................................................................................................... 299 Способ 3. Столбец-индикатор ............................................................................................................................................... 300 Способ 4. Формула массива .................................................................................................................................................... 301 Способ 5. Функция баз данных БДСУММ ............................................................................................................................... 302 ПОИСК И ПОДСТАНОВКА ДАННЫХ .......................................................................................................................................... 303 НАЙТИ И ЗАМЕНИТЬ ............................................................................................................................................................................ 304 Простой поиск .......................................................................................................................................................................... 304 Неточный поиск по маске ....................................................................................................................................................... 305 Поиск по формату ................................................................................................................................................................... 305 АВТОФИЛЬТР ..................................................................................................................................................................................... 307 Простая фильтрация ............................................................................................................................................................. 307 Фильтрация текста ............................................................................................................................................................... 309 Фильтрация дат ..................................................................................................................................................................... 309 Фильтрация чисел ................................................................................................................................................................... 310 Фильтрация по цвету ............................................................................................................................................................. 311 ИСПОЛЬЗОВАНИЕ СРЕЗОВ ДЛЯ ПОИСКА И ФИЛЬТРАЦИИ ........................................................................................................................... 313 ПОДСТАНОВКА С ПОМОЩЬЮ ФУНКЦИИ ВПР (VLOOKUP) ....................................................................................................................... 315 Ошибки #Н/Д и их подавление ................................................................................................................................................ 317 ПРИБЛИЗИТЕЛЬНЫЙ ПОИСК С ПОМОЩЬЮ ФУНКЦИИ ВПР (VLOOKUP) ..................................................................................................... 319 УЛУЧШЕННЫЙ ВАРИАНТ ФУНКЦИИ ВПР (VLOOKUP) .............................................................................................................................. 322 ПОИСК ДАННЫХ В ТАБЛИЦЕ С ПОМОЩЬЮ ФУНКЦИИ ИНДЕКС И ПОИСКПОЗ (INDEX И MATCH) .............................................................. 324 ВЫБОРКА СРАЗУ ВСЕХ ИСКОМЫХ ЗНАЧЕНИЙ ИЗ ТАБЛИЦЫ ФОРМУЛОЙ МАССИВА.......................................................................................... 325 ДВУМЕРНЫЙ ПОИСК В ТАБЛИЦЕ............................................................................................................................................................ 326 Вариант 1. Точный поиск ........................................................................................................................................................ 326 Вариант 2. Приблизительный поиск .................................................................................................................................... 327 ПОИСК В ТАБЛИЦЕ С УЧЕТОМ РЕГИСТРА.................................................................................................................................................. 329 ДИНАМИЧЕСКАЯ ГИПЕРССЫЛКА ДЛЯ БЫСТРОГО ПЕРЕХОДА ИЗ ОДНОЙ ТАБЛИЦЫ В ДРУГУЮ ........................................................................... 332 Шаг 1. Создаем переменную с именем листа ...................................................................................................................... 332 Шаг 2. Создаем гиперссылки ................................................................................................................................................... 333 СВОДНЫЕ ТАБЛИЦЫ ................................................................................................................................................................. 335 СОЗДАНИЕ ОТЧЕТОВ С ПОМОЩЬЮ СВОДНЫХ ТАБЛИЦ .............................................................................................................................. 336 Исходные данные ..................................................................................................................................................................... 336 Создание отчета ..................................................................................................................................................................... 336 Доводка внешнего вида отчета............................................................................................................................................ 339 Обновление и пересчет ........................................................................................................................................................... 339 НАСТРОЙКА ВЫЧИСЛЕНИЙ В СВОДНЫХ ТАБЛИЦАХ ................................................................................................................................... 340 Другие функции расчета вместо банальной суммы ........................................................................................................... 340 Доли и проценты ..................................................................................................................................................................... 343 Отличие и приведенное отличие .......................................................................................................................................... 346 Ранжирование .......................................................................................................................................................................... 347 Индекс влияния ......................................................................................................................................................................... 348 ФИЛЬТРАЦИЯ СРЕЗАМИ ....................................................................................................................................................................... 350 ФИЛЬТРАЦИЯ ДАТ С ПОМОЩЬЮ ВРЕМЕННОЙ ШКАЛЫ ............................................................................................................................. 355 ДЕТАЛИЗАЦИЯ РЕЗУЛЬТАТОВ ................................................................................................................................................................ 356 Просмотр подробностей ....................................................................................................................................................... 356 Разделение сводной таблицы по листам ............................................................................................................................. 356 Экспресс-просмотр .................................................................................................................................................................. 358 БЛАГОДАРНОСТИ ...................................................................................................................................................................... 360
Вы не можете начинать темы Вы не можете отвечать на сообщения Вы не можете редактировать свои сообщения Вы не можете удалять свои сообщения Вы не можете голосовать в опросах Вы не можете прикреплять файлы к сообщениям Вы можете скачивать файлы