Павлов Н. Microsoft Excel Готовые решения - бери и пользуйся!

Издательство: Книга по Требованию
Автор: Павлов Н.
ISBN: 978-5-519-01837-1
Страниц: 382
Язык: Русский
Год издания: 2014

 
 
 

Описание книгиСодержаниеДополнительно

Книги по IT. Это издание, используя подготовленные решения, поможет вам справится с самыми распространенными повседневными задачами и проблемами, стоящими при использовании 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. Макрос 11О
СОЗДАНИЕ РЕЗЕРВНЫХ КОПИЙ ЦЕННЫХ ФАЙЛОВ 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