Функции Excel: диспетчер сценариев, макросы, функция ВПР

ЛЕКЦИЯ 7 План лекции Функции Excel. (Продолжение) 1. Диспетчер сценариев 2. Макросы 3. Функция ВПР 2 ДИСПЕТЧЕР СЦЕНАРИЕВ ДИСПЕТЧЕР СЦЕНАРИЕВ Диспетчер сценариев позволяет создавать и подставлять различные значения исходных данных в модель, а также составлять автоматические отчеты, отображающие результаты вычислений. Инструмент MS EXCEL из группы Анализ "что-если" ( Вкладка Данные/ Группа Работа с данными ). ДИСПЕТЧЕР СЦЕНАРИЕВ Необходимо составить Сценарии для различных вариантов вклада 1. Вызовите Диспетчер сценариев (Данные – Анализ «что-если» – Диспетчер сценариев) 2. Нажмите кнопку Добавить 3. Введите название сценария и диапазон ячеек, в которые должны подставляться значения исходных данных 4. Нажмите ОК, откроется еще одно диалоговое окно для ввода данных 5. Нажмите Добавить, чтобы ввести еще один сценарий или ОК, чтобы вернуться в окно Диспетчера сценариев. Если нажать кнопку Вывести, то в указанные ячейки будут вставлены данные из выбранного сценария (окно при этом не закроется). Нажмите кнопку Отчет..., чтобы вывести на отдельный лист все сценарии и полученные результаты. Результаты могут быть выведены в виде Структуры или Сводной таблицы . Не забудьте указать ячейки результата (в которые выводятся результаты вышей модели). Для указания несвязных диапазонов используйте клавишу CTRL . МАКРОСЫ ЧТО ТАКОЕ МАКРОС? Макрос - это код, написанный на встроенном в Excel языке VBA (Visual Basic for Application) Макрорекодер - это инструмент в Excel, который пошагово записывает все что вы выполняете в Excel и преобразует это в код на языке VBA. Макрорекодер создает очень подробный код, который вы сможете при необходимости отредактировать в дальнейшем. ОТОБРАЖЕНИЕ ВКЛАДКИ "РАЗРАБОТЧИК" В ЛЕНТЕ МЕНЮ 1. Щелкните правой кнопкой мыши по любой из существующих вкладок на ленте и нажмите «Настроить ленту». Он откроет диалоговое окно «Параметры Excel». ОТОБРАЖЕНИЕ ВКЛАДКИ "РАЗРАБОТЧИК" В ЛЕНТЕ МЕНЮ 2. В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». Справа на панели «Основные вкладки» установите флажок «Разработчик». 3. Нажмите «ОК». ОТОБРАЖЕНИЕ ВКЛАДКИ "РАЗРАБОТЧИК" В ЛЕНТЕ МЕНЮ В результате на ленте меню появится вкладка "Разработчик" ЗАПИСЬ МАКРОСА В EXCEL Запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например «Экономическая информатика». 1.Перейдите на вкладку "Разработчик". 2.В группе "Код" нажмите кнопку "Запись макроса". Откроется одноименное диалоговое окно. ЗАПИСЬ МАКРОСА В EXCEL 1. В диалоговом окне "Запись макроса" введите имя для своего макроса, например "ВводТекста". 2. Есть несколько условий именования, которые необходимо соблюдать при назначении макроса. • Нельзя использовать пробелы между словами. • Можно использовать подчеркивание для разделения двух слов - например, "Ввод_текста". ЗАПИСЬ МАКРОСА В EXCEL Можно задать сочетание клавиш. В примере будем использовать ярлык Ctrl + Shift + N. !!! Сочетание, которое вы указываете, будет отменять любые существующие горячие клавиши в вашей книге. Например, если вы назначили сочетание Ctrl + S, вы не сможете использовать это для сохранения рабочей книги (вместо этого, каждый раз, когда вы его используете, он выполняет макрос). ЗАПИСЬ МАКРОСА В EXCEL Нажмите "ОК". Как только вы нажмете OK, Excel начнет записывать ваши действия. Вы можете увидеть кнопку "Остановить запись" на вкладке "Разработчик", которая указывает, что выполняется запить макроса. Выберите ячейку A2. Введите текст «Экономическая информатика»). Нажмите клавишу Enter. Вы попадете на ячейку A3. Нажмите кнопку "Остановить запись" на вкладке "Разработчик". ПРОСМОТР МАКРОСА Чтобы открыть редактор кода: 1. Удалите текст в ячейке A2. Это нужно, чтобы проверить будет ли макрос вставлять текст в ячейку A2 или нет. 2. Выберите любую ячейку - кроме A2. Это нужно проверить, выбирает ли макрос ячейку A2 или нет. 3. Перейдите на вкладку "Разработчик". 4. В группе "Код" нажмите кнопку "Макросы". 5. В диалоговом окне "Макрос" щелкните макрос "ВводТекста". 6. Нажмите кнопку "Выполнить". Вы увидите, что как только вы нажмете кнопку "Выполнить", текст "Экономическая информатика" будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия. ЧТО ЗАПИСЫВАЕТ МАКРОС? меню Открытие редактора VB в Excel: 1. Перейдите на вкладку "Разработчик". 2. В группе "Код" нажмите кнопку "Visual Basic". Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA. Панель инструментов Окно проектов Окно свойств Окно с кодом АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ ЗАПИСЬ МАКРОСА Если изначально была выделена ячейка A1, а затем выбрана ячейка C5, то в код макроса попадет такая запись: При использовании относительной ссылки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. 1ActiveCell.Offset(4, 2).Range("A1").Select В этом случае программа будет "двигаться" относительно активной ячейки Она указывает приложению на необходимость выделить ячейку, смещенную на 4 строки и 2 столбца от активной на данный момент ячейки. Это значит, если при последующем выполнении макроса предварительно выделить ячейку G10, то смещенной на 4 строки и 2 столбца окажется ячейка I14. Следите за тем, чтобы смещенные ссылки не оказались за пределами листа, т.к. это вызовет ошибку и остановит дальнейшее выполнение процедуры. ЗАПИСЬ МАКРОСА С ОТНОСИТЕЛЬНЫМИ ССЫЛКАМИ 1. Выберите ячейку A1. 2. Перейдите на вкладку "Разработчик". 3. В группе "Код" нажмите кнопку "Относительные ссылки". Он будет подсвечиваться, указывая, что он включен. Нажмите кнопку "Запись макроса" ЗАПИСЬ МАКРОСА С ОТНОСИТЕЛЬНЫМИ ССЫЛКАМИ В диалоговом окне "Запись макроса" введите имя для своего макроса. Например, имя "ОтносительныеСсылки". 1.В опции "Сохранить в" выберите "Эта книга". 2.Нажмите "ОК". 3.Выберите ячейку A2. 4.Введите текст "Excel" (или любой другой). 5.Нажмите клавишу Enter. Курсор переместиться в ячейку A3. 6.Нажмите кнопку "Остановить запись" на вкладке "Разработчик". Макрос в режиме относительных ссылок будет сохранен. ЗАПИСЬ МАКРОСА С ОТНОСИТЕЛЬНЫМИ ССЫЛКАМИ Для проверки макроса с относительными ссылками: 1. 2. 3. 4. Выберите любую ячейку (кроме A1). Перейдите на вкладку "Разработчик". В группе "Код" нажмите кнопку "Макросы". В диалоговом окне "Макрос" кликните на сохраненный макрос "ОтносительныеСсылки". 5. Нажмите кнопку "Выполнить". Результат макрос записал текст "Excel" не в ячейки A2. Код, который записал макрорекодер: 1Sub ОтносительныеСсылки() 2' 3' ОтносительныеСсылки Макрос 4' 5 6' 7 ActiveCell.Offset(1,0).Range("A1").Select 8 ActiveCell.FormulaR1C1 = "Excel" 9 ActiveCell.Offset(1, 0).Range("A1").Select 10End Sub Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки. ОГРАНИЧЕНИЯ МАКРОРЕКОРДЕРА Инструмент Excel для записи макросов – это очень простой способ создавать код VBA, но подходит он только для создания самых простых макросов Инструмент не умеет использовать многие возможности VBA, такие как: Константы, переменные и массивы; Выражения IF; Циклы; Обращения к встроенным функциям или внешним процедурам. РАСШИРЕНИЕ ФАЙЛОВ EXCEL, КОТОРЫЕ СОДЕРЖАТ МАКРОСЫ Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm). .xlsm – внешний вид файла с поддержкой макросов ВПР ВПР ВПР(искомое_значение;таблица; номер_столбца;[интервальный_ просмотр]) Функция ВПР выполняет поиск по спискам данных на основе уникального идентификатора и предоставляет часть информации, связанную с этим уникальным идентификатором. Буква «В» в ВПР означает «вертикальный». Она используется для дифференциации функции ВПР и ГПР, которая ищет значение в верхней строке массива («Г» обозначает «горизонтальный»). ВПР(ИСКОМОЕ_ЗНАЧЕНИЕ;ТАБЛИЦА; НОМЕР_СТОЛБЦА;[ИНТЕРВАЛЬНЫЙ_П РОСМОТР]) 1. искомое_значение – это значение для поиска Это может быть либо значение (число, дата или текст), либо ссылка на ячейку (ссылка на ячейку, содержащую значение поиска), или значение, возвращаемое некоторой другой функцией Поиск числа: =ВПР(40; A2:B15; 2) формула будет искать число 40. Поиск текста: =ВПР(«яблоки»; A2:B15; 2) - формула будет искать текст «яблоки». Обратите внимание, что всегда нужно включать текстовые значения в «двойные кавычки». Поиск значения из другой ячейки: =ВПР(C2; A2:B15; 2) - формула будет искать значение в ячейке C2. ВПР(ИСКОМОЕ_ЗНАЧЕНИЕ;ТАБЛИЦА; НОМЕР_СТОЛБЦА;[ИНТЕРВАЛЬНЫЙ_П РОСМОТР]) 1. таблица - это два или более столбца данных функция ВПР всегда ищет искомое значение в первом столбце таблицы. Таблица может содержать различные значения, такие как текст, дата, числа или логические значения. Значения нечувствительны к регистру, что означает, что прописные и строчные буквы считаются идентичными. =ВПР(«яблоки»; A2:B15; 2) - будет искать «яблоки» в ячейках от A2 до A15, потому что A - это первый столбец таблицы A2: B15. ВПР(ИСКОМОЕ_ЗНАЧЕНИЕ;ТАБЛИ ЦА;НОМЕР_СТОЛБЦА;[ИНТЕРВАЛ ЬНЫЙ_ПРОСМОТР]) =ВПР(«яблоки»; A2:B15; 2) Формула ищет «яблоки» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (B является вторым столбцом в указанной таблице A2:B15). 1. номер_столбца - номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке. Самый левый столбец в указанной таблице равен 1, второй столбец - 2, третий - 3 и т. д. ВПР(ИСКОМОЕ_ЗНАЧЕНИЕ;ТАБЛИЦА; НОМЕР_СТОЛБЦА;[ИНТЕРВАЛЬНЫЙ_ ПРОСМОТР]) =ВПР(«яблоки»; A2:B15; 2; ЛОЖЬ) Формула ищет «яблоки» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (B является вторым столбцом в указанной таблице A2:B15). 1. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). 2. Этот последний параметр является необязательным, но очень важным. ПОЧЕМУ НЕ РАБОТАЕТ ФУНКЦИЯ ВПР Тип ошибки Причина #Н/Д Неверное расположение столбца, по которому происходит поиск Решение Столбец таблицы, по которому происходит поиск ОБЯЗАТЕЛЬНО должен быть крайним левым. Перенесите столбец, по которому происходит поиск в крайнее левое положение таблицы. • Или создайте вспомогательный дублирующий столбец, слева в таблице. Если первое значение было выведено правильно, а после протягивания формулы ВПР в некоторых ячейках встречается ошибка #Н/Д, то диапазон таблицы не закреплен. • #Н/Д Не закреплен диапазон таблицы Используйте абсолютные ссылки ($) для закрепления диапазона таблицы, чтобы при заполнении формула использовала один и тот же диапазон. • Или используйте именованный диапазон Отсортируйте первый столбец таблицы по возрастанию наименований. • #Н/Д В интервальном просмотре выполняется поиск ближайшего значения (1), а таблица, по которой происходит поиск не отсортирована. ПОЧЕМУ НЕ РАБОТАЕТ ФУНКЦИЯ ВПР Тип ошибки Причина Решение #Н/Д Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ. Данные содержат лишние пробелы, недопустимые кавычки или непечатаемые символы. #ССЫЛКА! Значение номер столбца превышает число столбцов в таблице #ИМЯ? В формуле пропущены кавычки Проверьте номер столбца, содержащий возвращаемое значение. Если вы используете в качестве искомого значения не ссылку на ячейку, а текст, то его необходимо заключить в кавычки. Например: =ВПР("Продукт 1"; Цены!$A$2:$B$8;2;0)