БАШГУ-УНИТ. Лабораторная Информационные системы в экономите и управлении

ПОЛНОЕ ЗАДАНИЕ В ДЕМО ФАЙЛЕ,

ЧАСТЬ ДЛЯ ПОИСКА ДУБЛИРУЮ НИЖЕ

ФГБОУ ВО «БАШКИРСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕСИТЕТ»

Кафедра Математических методов в экономике

Информационные системы в экономике и управлении

Лабораторные работы №3-4

MS EXCEL

Уфа 2017

Содержание

Лабораторная работа 3. Редактирование электронных таблиц. .................................................................. 3

9. Условное форматирование. .................................................................................................................. 3

10. Защита информации (файла, листов, ячеек). ................................................................................ 12

11. Умные таблицы Excel ..................................................................................................................... 14

Лабораторная работа 4. Дополнительные возможности Excel. ................................................................. 17

12. Создание сводных таблиц. ............................................................................................................. 17

13. Горячие клавиши Excel ................................................................................................................... 23

14. Использование шаблонов ............................................................................................................... 25

15. Создание макросов и пользовательских функций на VBA ......................................................... 30

15.1 Создание макросов ............................................................................................................................ 30

15.2 Создание пользовательских функций .............................................................................................. 36

16. Надстройки Excel ............................................................................................................................ 42

Лабораторная работа 3. Редактирование электронных таблиц.

9. Условное форматирование.

Функция условного форматирования в Excel служит обеспечению наилучшей наглядности информации.

Располагается эта возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:

Создать правило

Для создания правила условного форматирования в Excel кликните по соответствующей кнопке на ленте, раскрыв следующее меню:

Выбрав пункт «Создать правило…», приложение отобразит окно:

В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее).

Виды условного форматирования:

1) Форматировать все ячейки на основании их значений

Этот вид правила применяется для сравнения числовых значений в диапазоне. В описании можно выбрать стиль формата и соответствующие этому стилю параметры.

2) Гистограмма

Данная возможность позволяет отобразить в каждой ячейке горизонтальный столбец, похожий на частичную заливку. Если Вы хоть раз использовали гистограмму при построении диаграмм, то Вам будет понятно, о чем идет речь.

Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному – наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).

Настройки стиля:

 Показывать только столбец – установив флажок на данном поле, Вы сообщаете, что для диапазона ячеек правила необходимо скрывать содержимое и оставлять только формат;

 Параметры значений – здесь устанавливаются максимальные и минимальные значения и их типы. В качестве типа может выступать число, процент, формула, процентиль либо по умолчанию (авто). Значение может быть только числовым. Все числа, меньше минимального (включая отрицательные), приравниваются к нулю, т.е. не содержат столбца. А те, которые больше максимального, приравниваются к 100% и закрашиваются полностью.

 Внешний вид столбца – устанавливает способ заливки (сплошной или градиентный), границу и их цвета;

Пример 1

 Направление столбца – определяет способ направленности (слева направо либо наоборот);

 Кнопка «Отрицательные значения и ось…» – настройки отображения столбцов для отрицательных чисел. Что они позволяют:

o Установить свой цвет заливки столбца и его границу или сделать их одинаковыми для всех значений (положительных и отрицательных. По умолчанию они различаются);

o Задать положение оси или одинаковую направленность для всех значений.

3) Цветовые шкалы

Как и гистограммы, шкалы в условном форматировании заливают цветом ячейку с числовым значением, но отличие заключается в том, что последние заливают ее полностью. Чем выше значение, тем более насыщенная заливка. Также можно использовать несколько цветов, где, например, меньшие числа залиты зеленым, средние желтым, а большие красным.

В качестве примера, рассмотрим настройку трехцветной шкалы, хотя она мало чем отличается от настройки двухцветной.

Здесь Вы можете установить, что считать минимальным значением, что средним, а что максимальным. Также возможно задать предпочтительный цвет и тип показателя. Разберем установки, представленные на изображении:

 Минимальным числом задан ноль, а значения меньше его, будут иметь такие же цвет и насыщенность;

 Средним значением указана единица и желтый цвет. Это значит, что переход шкалы от красного к желтому будет осуществлен между 0 и 1;

 4 является максимальным значением. Все, что превышает его, получает те же установки. Переход от желтого к зеленому происходит между 1 и 4.

4) Наборы значков (флажков)

Этот вид условного форматирования, в отличие от цвета заливки, использует различные значки в виде фигур, направлений, индикаторов и оценок.

Как и в случаях, описанных выше, за 100% принимается максимальное число, а остальные составляют от него какую-то долю. Весь диапазон разделяется на определенное количество частей, которое равно количеству значков в выбранном наборе. Каждой такой части соответствует свой флажок. Если диапазон нужно разделить не по долям, а по конкретным значениям, то поменяйте тип значения для значка.

5) Форматировать только ячейки, которые содержат

Этот вид условного форматирования отличается от первого тем, что он создает правило, которое должно соблюдаться, чтобы формат был применен к ячейке.

Пример 2

Рассмотрим правила, которые имеются в этом пункте:

 Значение ячейки. Предполагает работу с числами и текстом. Сравнение производится по шкале сортировки.

 Текст. Позволяет проверить наличие или отсутствие подстроки в тексте.

 Даты. С его помощью легко создать правила типа «вчера», «сегодня», «завтра», «на прошлой неделе», «в следующем месяце» и т.п.

 Пустые. Форматирует пустые ячейки. Пробелы не учитываются.

 Непустые. Противоположное предыдущему правилу.

 Ошибки. Истинно, когда значением ячейки является ошибка.

 Без ошибки. Противоположное предыдущему правилу.

6) Форматировать только первые и последние значения

Из названия понятно, что правило срабатывает для тех ячеек, которые идут первыми (наибольшими) или последними (наименьшими) в указанном диапазоне. Количество таких ячеек указывается в виде числа или процента.

7) Формула в условном форматировании

Когда имеющихся правил недостаточно, можно создать свое, задав ему практически любую логику, на основе формул, результатом выполнения которой должно быть логическое значение. Эти тип называется «Использовать формулу для определения форматируемых ячеек».

Для примера рассмотрим список заказа товаров, который необходимо сравнить с остатком на складе. Всего участвуют 2 таблицы: сам заказ и таблица остатков.

Рассмотрим, как создать такое форматирование.

Пример 3

Используем 2 условия со следующими формулами:

 Если на складе нет товара, т.е. равен 0, то подсвечиваем позицию заказа красным – =ВПР(D3;A:B;2;ЛОЖЬ)=0

 Если на складе есть товар, но его количество меньше, чем указано в позиции заказа, то последнюю подсвечиваем желтым – =И(ВПР(D3;$A:$B;2;ЛОЖЬ)<E3;ВПР(D3;$A:$B;2;ЛОЖЬ)<>0)

Теперь необходимо выделить требуемый диапазон и создать нужные нам правила.

В функции, в качестве первого аргумента используется ссылка всего на одну ячейку. Вас это не должно смущать, так как приложение «понимает», что ее нужно сместить в соответствии с диапазоном правила. Главное, чтобы она была относительной, т.е. не закреплена символами доллара – $.

8) Остальные правила

Существует еще два вида правил, а именно:

 Форматирование на основе среднего значения – полное название «Форматировать только значения, которые находятся выше или ниже среднего»;

 Форматирование уникальных или повторяющихся значений.

По ним остается добавить только то, что в первом можно использовать стандартные отклонения. В остальном, они говорят сами за себя.

Условное форматирование даты в excel

Создайте и выделите диапазон с датами.

Примените к нему условное форматирование - «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выберите нужное (например, за последние 7 дней) и нажмите ОК.

Красным цветом выделены ячейки с датами последней недели (дата выполнения примера – 02.02.2016).

Управление правилами

Помимо умения создавать правила, условным форматированием также нужно корректно управлять. Особенно это важно, когда для одного диапазона применяется несколько условий. Но обо всем по порядку.

Диспетчер правил условного форматирования отображает список, состоящий из условия, формата и диапазона, к которому применено правило.

Пример 4

В самом верху окна можно выбрать, какие правила следует выводить в списке: из текущего диапазона, с этого листа, из любого другого листа открытой книги.

Первые три кнопки диспетчера должны быть понятны без дополнительных пояснений, а вот на последних двух (стрелки вверх и вниз) остановимся подробнее.

На изображение приведено 2 правила: значение равно трем и значение больше двух. Представьте, что они применены к ячейке со числом 3. Какое из них сработает? В этом случае оба, так как между ними нет конфликта в форматировании, одно отвечает за заливку, а второе за границу. Но если бы они оба отвечали за один и тот же стиль, то выполнилось правило, которое стоит выше, потому что имеет больший приоритет.

Так вот, стрелками окна можно менять положение отдельно выделенного правила и, соответственно, его значимость.

Рассмотрим еще один случай, когда требуется выполнить только одно условие. В конце каждого правила имеется флажок «Остановить, если истина». Выставив его, Вы отменяете выполнение всех последующих правил для текущего диапазона, при условии, что это оно выполняется. Исходя из рассматриваемого примера, если ячейка содержит значение 3, то проверка на условие «больше двух» произведена не будет.

Задание на лабораторную работу

1. Выполните примеры 1-4 (на одном листе, на непересекающихся диапазонах).

2. Выполните следующие задания (на отдельном листе, на непересекающихся диапазонах).

Задание 1. Скрытие данных.

Создайте таблицу, содержащую числа положительные, отрицательные числа и 0. Используйте условное форматирование, чтобы положительные числа были написаны зеленым цветом, отрицательные – красным, а равные 0 были скрыты (цвет шрифта в ячейке белый).

Задание 2. Условное форматирование текста.

Аналогично примеру 4 используйте условное форматирование «Текст содержит..», чтобы отформатировать ячейки с определенным значением ("да", "нет").

Задание 3. Условное форматирование по условиям в других ячейках.

Создайте таблицу по образцу. С помощью условного форматирования выделите только тех сотрудников, которые имеют результат выше 75 и имеют льготы.

Выделите все фамилии. Используйте тип правил «Использовать формулу для определения форматируемых ячеек»:

=И(B2>75;C2="Да")

И –означает, что мы проверяем два условия и они должны оба выполняться.

Важно! Формула прописывается к первой ячейке (строке). Формула обязательно должна быть с относительными ссылками (без $), если мы хотим, чтобы она распространилась на все последующие строки.

Результат должен получиться таким:

10. Защита информации (файла, листов, ячеек).

Задание 1. Защита листов книги

Защитите от изменения ячейки А1 и А2 таблицы предыдущего задания.

Задание 2. Защита листов книги

Защитите листы текущей рабочей книги от:

 удаления, переименования, перемещения листов в книге;

 изменения закрепленных областей ("шапки" и т.п.);

 нежелательных изменений структуры (сворачивание строк/столбцов при помощи кнопок группировки "плюс/минус");

 возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel.

с помощью кнопки Защитить книгу (Protect Workbook) на вкладке Рецензирование (Reveiw):

Задание 3. Шифрование файла

При необходимости, Excel предоставляет возможность зашифровать весь файл книги, используя несколько различных алгоритмов шифрования семейства RC4.

Установите такую защиту на текущую рабочую книгу. При ее сохранении необходимо выбрать команды Файл - Сохранить как (File - Save As), а затем в окне сохранения найти и развернуть выпадающий список Сервис - Общие параметры (Tools - General Options). В появившемся окне мы можем ввести два различных пароля - на открытие файла (только чтение) и на изменение:

11. Умные таблицы Excel

Пусть имеется таблица, с которой постоянно приходится работать (сортировать, фильтровать, считать по ней что-то) и содержимое которой периодически изменяется (добавляется, удаляется, редактируется). Например, вот такого вида:

Размер - от нескольких десятков до нескольких сотен тысяч строк - не важен. Необходимо упростить и облегчить работу с таблицей, превратив эти ячейки в "умную" таблицу.

Указания

Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table):

В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК и получаем на выходе примерно следующее:

В результате после такого преобразования диапазона в "умную" Таблицу имеем следующие преимущества (кроме приятного дизайна):

1. Созданная Таблица получает имя Таблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design). Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы.

2. Созданная один раз Таблица автоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки - она растянется ниже, если добавить новые столбцы - разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:

3. В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data)).

4. При добавлении новых строк в них автоматически копируются все формулы.

5. При создании нового столбца с формулой - она будет автоматически скопирована на весь столбец - не надо тянуть формулу черным крестом автозаполнения.

6. При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):

7. Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:

8. К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):

=Таблица1[#Все] - ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов

=Таблица1[#Данные] - ссылка только на данные (без строки заголовка)

=Таблица1[#Заголовки] - ссылка только на первую строку таблицы с заголовками столбцов

=Таблица1[#Итоги] - ссылка на строку итогов (если она включена)

=Таблица1[#Эта строка] - ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] - будет ссылаться на значение НДС из текущей строки таблицы.

Лабораторная работа 4. Дополнительные возможности Excel.

12. Создание сводных таблиц.

Задание 1. Создайте сводную таблицу по примеру.

Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert). Отобразится окно с настройками:

В нем нужно выбрать источник данных и место вывода сводной таблицы, нажать ОК и перейти к редактированию макета. Это можно делать перенося поля в нижнюю часть окна Список полей сводной таблицы, где представлены области:

 Названия строк (Row labels)

 Названия столбцов (Column labels)

 Значения (Values) – в этой области происходят вычисления.

 Фильтр отчета (Report Filter).

Задание 2. Создайте отчеты по сводной таблице по примерам.

Каждый отчет должен находиться на отдельном листе. Для этого сделайте 6 копий листа со сводной таблицей.

Пример 1. Выборка уникальных значений

Одной из самых популярных задач, решаемой при помощи сводной таблицы – это выборка уникальных значений из списка или массива данных. Использование интерфейса сводной таблицы позволяет решить эту задачу самым «элегантным» способом – без использования формул.

На листе Выборка выведите список стран и количество упоминаний в массиве данных.

В поле данных необходимо, чтобы стоял вид операции – «количество». Этот параметр позволяет обрабатывать в области данных сводной таблицы нечисловые поля

исходных данных. Альтернативой операции подсчета количества служит стандартная функция СЧЁТЕСЛИ. Сформировать набор уникальных значения только с помощью формул в принципе тоже возможно (см. часть 1), но это потребует очень сложных формул с вычисляемой адресацией. То есть, использование сводной таблицы в данной задаче – это самый оптимальный способ решения.

Пример 2. Суммирование значений

Другая популярная задача для применения интерфейса сводных таблиц – это получение итоговых значений по уникальным записям массива данных.

На листе Сумма сформируйте итоговые данные по заказам по каждой стране:

Вид операции «Сумма» в поле данных допускает использование только числовых полей. Прочие виды агрегации исходных данных на практике почти не используются.

Для решения задачи при помощи стандартных формул можно использовать функцию СУММЕСЛИ. Очевидно, что сложность возникает не в консолидации значений, а, также как и в прошлом примере, в выборке уникального списка (в примере - названия стран).

Пример 3. Двухмерный анализ

Описанные ранее примеры демонстрируют анализ данных по одному критерию. Электронные таблицы позволяют наглядно представить данные в двух измерениях: по столбцам и по строкам. Сводные таблицы также имеют эти области отображения данных.

На листе Таблица сформируйте отчет по странам и датам, показывающий изменение показателя количества заказов во времени. Обратите внимание, что для поля типа дата должна быть применена дополнительная группировка: по месяцам и по годам.

Суммирование по нескольким критериям допустимы и через стандартные функции Excel СУММЕСЛИМН, СУММПРОИЗВ, а также функции обработки массивов. Однако, такой вариант требует предварительно известные значения параметров - ключей выборки. Кроме того, расчет при помощи формул требует значительно больше времени, что на больших объемах данных может привести к большим потерям в производительности работы.

Пример 4. Многомерный анализ

Кроме визуального анализа в области по строкам и столбцам, в сводных таблицах можно использовать глобальный фильтр по одному или нескольким полям исходных данных. Для этого предназначена специальная область ячеек, расположенная над сводной таблицей.

На листе Фильтр реализуйте возможность просмотра данных по компаниям одной страны с использованием области фильтра сводной таблицы:

Поле фильтра можно переместить в область строк или столбцов, что позволит просмотреть больший массив информации. Кроме описанной области фильтров, дополнительную фильтрацию данных можно осуществлять через настройку списков ключевых полей в областях строк или столбцов.

Аналогом использования фильтров сводной таблицы при помощи формул рабочего листа являются в большинстве случаев формулы обработки массивов.

Пример 5. Долевые проценты

На листе Доля в окне Параметры поля перейдите на вкладку Дополнительные вычисления и станет доступен выпадающий список Дополнительные вычисления (Show data as):

В этом списке, например, можно выбрать варианты Доля от суммы по строке (% of row), Доля от суммы по столбцу (% of column) или Доля от общей суммы(% of total), чтобы автоматически подсчитать проценты для каждой страны.

Пример 6. Динамика заказов

На листе Динамика постройте сводную таблицу с группировкой по годам, в которой будут показаны отличия заказов каждого следующего года от предыдущего, т.е. – динамика заказов. Для этого в выпадающем списке Дополнительные вычисления (Show data as) выберите вариант Приведенное отличие (% of difference), а в нижних окнах Поле (Base field) и Элемент(Base item) выберите OrderDate и Назад (в англоязычной версии вместо этого странного слова было более понятное Previous, т.е. предыдущий).

Добавьте условное форматирование для выделения отрицательных значений красным цветом, положительных – зеленым.

13. Горячие клавиши Excel

Задание.

1. Создайте рабочую книгу Excel, состоящую из 3 листов. На 1-м листе создайте произвольную таблицу на листе (примерно 20х20), содержащую как данные (текстовые, числовые), так и формулы с вычислениями.

2. Изучите работу следующих «горячих клавиш» (столбец 1):

3. Выполните следующие примеры:

Аналогично производится автоподбор высоты строк (двойной щелчок на границе строк).

14. Использование шаблонов

Очень часто требуется создавать много похожих документов, но содержащих разные сведения, например различные формы справок, ведомостей, счета, где меняются только фамилии или даты, и т.п.

Обычно их создают так. Открывают последнюю версию ранее созданного документа, например ведомость за прошлый месяц, вносят в документ необходимые изменения, печатают и сохраняют измененный документ под новым именем. Однако такой метод создания новых документов имеет ряд недостатков. Очень часто при сохранении документа мы отвлекаемся и в результате какой-нибудь из документов теряем: либо старый, либо новый документ.

Для создания таких однотипных документов удобно использовать шаблоны. Шаблон – это заготовка документа, незаполненный данными бланк, форма. Excel предоставляет вам встроенные шаблоны, используя которые можно быстро подготовить авансовый отчет, счет-фактуру, карточку табельного учета и другие документы. В случае если ни один из готовых шаблонов вам не подошел, вы можете создать свой собственный шаблон, в дальнейшем работая с ним, как и с любым шаблоном Excel.

Задание 1. Использование готовых шаблонов.

Найдите шаблон для расчета графика погашения кредита или процентов по вкладу. Проведите соответствующие расчеты в таблице и сохраните документ.

Указания:

Для того чтобы создать документ на основе шаблона, нужно в меню Файл выбрать команду Создать. В результате откроется окно Доступные шаблоны.

В этом окне отображены все доступные шаблоны. Шаблон Новая книга позволяет создавать новые таблицы с параметрами, заданными в программе Excel по умолчанию, которые вы сами можете изменять, выбрав команду Параметры в меню Файл.

Группа Последние шаблоны содержит шаблоны, с которыми вы недавно работали. Образцы шаблонов – стандартные шаблоны, установленные на компьютере: авансовый отчет, выписка по счету, личный бюджет на месяц, отчет о продажах, рабочий листок, рассрочка. Мои шаблоны - шаблоны, созданные пользователем. Из существующих документов - шаблоны, созданные на основе уже готового документа. В группе Шаблоны Office.com перечислены документы, которые вы можете создать на основе шаблонов, находящихся на сайте Office.com (шаблоны также можно скачать на сайте https://templates.office.com/ru-ru). Это могут быть бланки, бюджеты, ведомости, календари, наряды на работу, отчеты, повестки дня, расписания, списки и множество других шаблонов. В результате щелчка на имени документа программа Excel делает попытку подключиться к серверу Office.com. Если компьютер подключен к Интернету, то после установки соединения открывается окно, в котором можно выбрать шаблон.

После того как вы выберете шаблон, рядом в окне предварительного просмотра можно сразу увидеть содержимое шаблона. Если это то что надо, то щелкните на кнопке Загрузить. В результате будет создана новая книга, содержащая заготовку выбранного документа.

Работа с документом, созданным на основе шаблона, практически ничем не отличается от работы с обычной таблицей. Однако при создании шаблона можно защитить некоторые поля документа, чтобы пользователь не мог внести в них изменения. Например, в ячейках таблицы находятся формулы, поэтому они заблокированы, активизирован режим отображения подсказок и контроля данных.

После ввода всей необходимой информации таблицу, созданную на основе шаблона, необходимо сохранить. Делается это обычным образом - выбором из меню Файл команды Сохранить.

Задание 2. Создание шаблона в Excel

Создайте собственный шаблон в Excel.

Указания

Для того чтобы создать шаблон, требуется в меню Файл выбрать команду Создать -> Новая книга и щелкнуть на кнопке Создать. В рабочем окне программы появится новая книга под названием Книга1 или Книга2, Книга3 и т.п. Затем требуется создать таблицу, которую вы в дальнейшем будете использовать в качестве шаблона.

Очевидно, что таблица должна содержать заголовки и формулы и иметь оформление, соответствующее вашим требованиям, например:

Как только таблица подготовлена, ее необходимо сохранить в формате шаблона Excel. Для этого в меню Файл выберите команду Сохранить, а также можно воспользоваться панелью быстрого доступа. Следует обратить внимание, если шаблон создается на основе документа, у которого уже есть имя, то в меню Файл надо выбрать команду Сохранить как.

В окне Сохранение документа в списке Тип файла выбрать формат сохранения таблицы - Шаблон Excel. Следует обратить внимание, что автоматически будет выбрана папка C:\Users\AppData\Roaming\Microsoft\Шаблоны, в которую по умолчанию сохраняются шаблоны пользователя. Имя пользователя соответствует имени зарегистрированного пользователя, с которым в этом сеансе вы вошли в операционную систему. После этого в поле Имя файла надо ввести имя шаблона и сделать щелчок на кнопке Сохранить.

Шаблон сохранен, теперь можно закрыть документ, на основе которого вы его создавали. Следует обратить внимание, если шаблон требуется изменить, то его можно редактировать, как и обычную книгу.

15. Создание макросов и пользовательских функций на VBA

15.1 Создание макросов

Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на Excel. Другим поводом для использования макросов в работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные функции. Например, функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.

Макрос – это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Макрос можно запускать сколько угодно раз, заставляя Excel выполнять последовательность любых нужных вам действий, которые не хочется выполнять вручную.

В принципе, существует множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP...), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение – Excel, Word, Outlook или Access.

Создать макросы можно 3-мя способами.

Способ 1. Создание макросов в редакторе Visual Basic

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно – редактор программ на VBA, встроенный в Microsoft Excel.

В Excel 2007 и новее нужно сначала отобразить вкладку Разработчик (Developer). Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer).

Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic (Visual Basic Editor).

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

 Обычные модули – используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля.

 Модуль Эта книга - также виден в левом верхнем углу редактора Visual Basic в окне, которое называется Project Explorer. В этот модуль обычно записываются макросы, которые должны выполнятся при наступлении каких-либо событий в книге (открытие или сохранение книги, печать файла и т.п.):

 Модуль листа - доступен через Project Explorer и через контекстное меню листа, т.е. правой кнопкой мыши по ярлычку листа – команда Исходный текст (View Source). Сюда записывают макросы, которые должны выполняться при наступлении определенных событий на листе (изменение данных в ячейках, пересчет листа, копирование или удаление листа и т.д.).

Обычный макрос, введенный в стандартный модуль выглядит примерно так:

Давайте разберем приведенный выше в качестве примера макрос Zamena:

 Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (входных значений) в скобках. Если аргументов нет, то скобки надо оставить пустыми.

 Любой макрос должен заканчиваться оператором End Sub.

 Все, что находится между Sub и End Sub - тело макроса, т.е. команды, которые будут выполняться при запуске макроса. В данном случае макрос выделяет ячейку заливает выделенных диапазон (Selection) желтым цветом (код = 6) и затем проходит в цикле по всем ячейкам, заменяя формулы на значения. В конце выводится окно сообщения (MsgBox).

Запуск и редактирование макроса

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик(Developer):

Пример 1

 Любой выделенный в списке макрос можно запустить кнопкой Выполнить (Run).

 Кнопка Параметры (Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.

 Кнопка Изменить (Edit) открывает редактор Visual Basic и позволяет просмотреть и отредактировать текст макроса на VBA.

Создание кнопки для запуска макросов

Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:

 Кнопка на панели быстрого доступа

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar):

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

 Кнопка на листе

Откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer). Выберите объект Кнопка (Button):

Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.

! Для дальнейшего подробного изучения возможностей по самостоятельному созданию макросов вы можете воспользоваться учебником «Учебник по VBA.doc».

Способ 2. Запись макросов макрорекордером.

Макрорекордер – это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операции, перемотал пленку и запустил выполнение тех же действий еще раз. У такого способа есть свои плюсы и минусы:

 Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу – запись останавливается.

 Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).

 Если во время записи макроса макрорекордером вы ошиблись – ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) – во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer).

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

Пример 2

 Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.

 Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить (Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладкеРазработчик (Developer) или нажав ALT+F8.

 Сохранить в... - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос:

Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel

Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента

Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.

После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording).

Способ 3. Найти необходимый макрос в Интернете или еще где-нибудь и скопировать его в модуль в Редакторе Visual Basic.

15.2 Создание пользовательских функций

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция – только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined):

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

Пример 3

Задание на лабораторную работу:

1. Выполните примеры 1-3.

2. Найдите в Интернете текст какого-нибудь макроса и скопируйте его в существующий модуль рабочей книги Excel. Проверьте работоспособность созданного макроса.

Макросы можно взять, например, на сайте http://www.planetaexcel.ru/techniques/ или на других подобных сайтах (http://office-menu.ru/, http://macros-vba.ru, http://excelworks.ru/ и др.).

3. На новом листе создайте шаблон экзаменационной ведомости, в котором оценки проставляются командными кнопками, т.е. при нажатии на кнопку в выделенную ячейку вставляется соответствующая оценка.

Пример 3

4. Создайте следующий 3D-макрос в новой книге.

На вкладке Вставка - Фигуры (Insert - Shapes) выберите сердце:

Нарисуйте его на листе, внутрь впишите текст и отформатируйте по примеру:

Добавьте 3D-эффект на вкладке Формат - Эффекты фигур (Format - Shapes Effects):

Добавьте макрос для анимации. Нажав Alt+F11, в открывшемся окне редактора Visual Basic вставьте новый пустой модуль через меню Insert - Module и скопируйте туда текст макроса:

Sub Heart()

For i = 1 To 720

With ActiveSheet.Shapes.Range(Array("Сердце 1")).ThreeD

.RotationX = i

.RotationY = i / 20

.RotationZ = i / 2

End With

DoEvents

Application.Wait (Now + TimeSerial(0, 0, 0.1))

Next i

End Sub

Внимание! Чтобы макрос работал правильно удостоверьтесь, что название фигуры на листе такое же как в коде макроса – Сердце 1. Если фигура называется по-другому – измените название в коде макроса (P.S. таким образом анимировать можно любую фигуру, а не только сердце – нужно лишь правильно указать ее название в макросе).

Закройте редактор Visual Basic и запустите макрос. Назначьте созданному макросу сочетание клавиш (например, CTRL+l) и нажмите Выполнить. В дальнейшем запускайте макрос помощью сочетания клавиш.

Если вы хотите, чтобы макрос работал дольше, измените во 2-й строке макроса число 720 на бОльшее (например, 1500, 3000).

16. Надстройки Excel

Надстройки расширяют диапазон команд и возможностей Microsoft Excel. По умолчанию они доступны не сразу, поэтому сначала их необходимо установить и (в некоторых случаях) активировать.

Некоторые надстройки, такие как "Пакет анализа" и "Поиск решения", встроены в Excel. Другие доступны в Центре загрузки, и их необходимо предварительно скачать и установить. Кроме того, некоторые надстройки создаются сторонними организациями, например, поставщиками программных решений или программистами.

Активация надстройки Excel

1. На вкладке Файл выберите команду Параметры, а затем — категорию Надстройки.

2. В поле Управление выберите пункт Надстройки Excel, а затем нажмите кнопку Перейти. Откроется диалоговое окно Надстройки.

3. В поле Доступные надстройки установите флажок той надстройки, которую необходимо активировать, а затем нажмите кнопку ОК .

Если в окне Доступные надстройки не удается найти надстройку, которую требуется активировать, возможно, ее требуется установить.

Установка надстройки Excel  Чтобы установить надстройку, которая обычно устанавливается вместе с Excel, например "Пакет анализа" или "Поиск решения", запустите программу установки Excel или Microsoft Office и выберите вариант Изменить, а затем выберите требуемую надстройку. После перезапуска Excel она должна появиться в поле Доступные надстройки.  Некоторые надстройки Excel хранятся на компьютере. Чтобы установить или активировать их, нажмите кнопку Обзор (в диалоговом окне Надстройки), найдите надстройку, а затем нажмите кнопку ОК.  Некоторые надстройки Excel требуют запуска пакета установки. В этом случае может потребоваться загрузить или скопировать пакет установки на компьютер (обычно пакеты установки имеют расширение MSI), а затем запустить его.  Другие надстройки, отсутствующие на компьютере, можно скачать и установить с помощью веб-браузера со страницы загрузок MS Office или с других сайтов в Интернете или на сервере организации. Чтобы скачать их, следуйте инструкциям по установке.

Задание 1. Использование встроенной надстройки «Поиск решения»

Задача 1.

Предположим, что компания имеет два складских помещения, откуда товар поступает в пять магазинов, разбросанных по всему городу. Каждый магазин в состоянии реализовать определенное, известное нам количество товара. Каждый из складов имеет ограниченную вместимость. Задача состоит в том, чтобы рационально выбрать – с какого склада в какие магазины нужно доставлять товар, чтобы минимизировать общие транспортные расходы.

Перед началом оптимизации необходимо будет составить таблицу на листе Excel – математическую модель, описывающую ситуацию:

Подразумевается, что:  Серая таблица (B3:G5) описывает стоимость доставки единицы от каждого склада до каждого магазина.  Лиловые ячейки (C14:G14) описывают необходимое для каждого магазина количество товаров на реализацию.  Красные ячейки (J10:J11) отображают емкость каждого склада – предельное количество товара, которое склад может вместить.  Желтые (C12:G12) и синие (H10:H11) ячейки – соответственно, суммы по строке и столбцу для зеленых ячеек.  Общая стоимость доставки (E17) вычисляется как сумма произведений количества товаров на соответствующие им стоимости доставки.

Таким образом, задача сводится к подбору оптимальных значений зеленых ячеек. Причем так, чтобы общая сумма по строке (синие ячейки) не превышала вместимости склада (красные ячейки), и при этом каждый магазин получил необходимое ему количество товаров на реализацию (сумма по каждому магазину в желтых ячейках должна быть как можно ближе к требованиям – лиловым ячейкам).

Решение:

Запустите надстройку Поиск решения (Solver), доступную с вкладки Данные (Data). Откроется окно:

В этом окне нужно задать следующие настройки:  Целевая ячейка (Target cell) – тут необходимо указать конечную главную цель нашей оптимизации, т.е. розовую ячейку с общей стоимостью доставки (E17). Целевую ячейку можно минимизировать (если это расходы, как в нашем случае), максимизировать (если это, например, прибыль) или попытаться привести к заданной константе.  Изменяемые ячейки (By changing cells) – здесь укажем зеленые ячейки (C10:G11), варьируя значения которых мы хотим добиться нашего результата – минимальных затрат на доставку.  Ограничения (Subject to the Constraints) – список ограничений, которые надо учитывать при проведении оптимизации. В нашем случае это ограничения на вместимость складов и потребности магазинов. Для добавления ограничений в список нужно нажать кнопку Добавить (Add) и ввести условие в появившееся окно:

Кроме очевидных ограничений, связанных с физическими факторами (вместимость складов и средств перевозки, ограничения бюджета и сроков и т.д.) иногда приходится добавлять ограничения «специально для Excel». В нашем случае, например, нужно будет добавить вот такое ограничение:

Оно дополнительно уточнит, что объем перевозимого товара (зеленые ячейки) не может быть отрицательным – для человека такое само собой очевидно, но для компьютера это надо прописать явно.

После настройки всех необходимых параметров окно должно выглядеть следующим образом:

Теперь, когда данные для расчета введены, нажмите кнопку Выполнить (Solve), чтобы начать оптимизацию. В сложных случаях с большим количеством изменяемых ячеек и ограничений нахождение решения может занять продолжительное время, но данная задача для Excel проблемы не составит:

Обратите внимание на то, как интересно распределились объемы поставок по магазинам, не превысив при этом емкости наших складов и удовлетворив все запросы по требуемому количеству товаров для каждого магазина.

Если найденное решение нам подходит, то можно его сохранить, либо откатиться назад к исходным значениям и попробовать еще раз с другими параметрами. Также можно сохранить подобранную комбинацию параметров как Сценарий. По желанию пользователя Excel может построить три типа Отчетов по решаемой задаче на отдельных листах: отчет по результатам, отчет по математической устойчивости решения и отчет по пределам (ограничениям) решения, однако они, в большинстве случаев, интересны только специалистам.

Бывают, однако, ситуации, когда Excel не может найти подходящего решения. Имитировать такой случай можно, если указать в нашем примере требования магазинов в сумме большие, чем общая вместимость складов. Тогда при выполнении оптимизации

Excel попытается приблизиться к решению, насколько это возможно, а затем выдаст сообщение о невозможности найти решение. Тем не менее, даже в этом случае мы имеем массу полезной информации – в частности можем видеть «слабые звенья» наших бизнес-процессов и понять направления совершенствования.

Рассмотренный пример, конечно, является относительно простым, но легко масштабируется под решение гораздо более сложных нелинейных задач. Например:  Оптимизация распределения финансовых средств по статьям расходов в бизнес-плане или бюджете проекта. Ограничениями, в данном случае, будут являться объемы финансирования и сроки выполнения проекта, а целью оптимизирования – максимизация прибыли и минимизация расходов на проект.  Оптимизация расписания сотрудников с целью минимизации фонда заработной платы предприятия. Ограничениями, в этом случае, будут пожелания каждого сотрудника по графику занятости и требования штатного расписания.  Оптимизация инвестиционных вложений – необходимость грамотно распределить средства между несколькими банками, ценными бумагами или акциями предприятий с целью, опять же, максимизации прибыли или (если это более важно) минимизации рисков.

В любом случае, надстройка Поиск решения (Solver) является весьма мощным инструментом Excel и может выручить во многих сложных ситуациях, с которыми приходится сталкиваться в современном бизнесе.

Задача 2.

Создайте на новом листе таблицу графика работы персонала.

Задание 2. Установка сторонней надстройки.

Скопируйте файл надстройки Function_OM.xlam в вашу рабочую папку. Чтобы установить ее, нажмите кнопку Обзор (в диалоговом окне Надстройки), найдите надстройку, а затем нажмите кнопку ОК.

Данная надстройка содержит следующие полезные функции:

1. Объединение ячеек в Excel без потери данных (кнопка «Объединить без потерь»).

У стандартного объединения в Excel имеется недостаток, который заметен только при объединении нескольких ячеек с данными, когда сохраняется значение только верхней левой ячейки выделенного диапазона, а остальные затираются. Но часто возникает необходимость сохранить все данные во время объединения. Настройка office-menu включает в себя возможность объединения ячеек без потери данных с возможностью их разделения специальными символами.

Объединяя ячейки внутри выделенного диапазона, надстройка применяет символы-разделители, которые вы можете задать самостоятельно. По умолчанию для разделения значений, расположенных на одной строке листа Excel, используются квадратные скобки […], а для разделения разных строк применяется символ переноса строки.

2. Объединение строк (кнопка «Объединить строки»).

В отличие от кнопки, описанной выше, объединение строк создает внутри выделенного диапазона ячеек несколько объединенных областей, разбитых по строкам. В качестве разделителя используется только разделитель значений:

3. Объединение столбцов (кнопка «Объединить столбцы»).

Действие этой кнопки создает объединенные области в диапазоне, разделенные по столбцам. Для разделителя используется только разделитель строк:

4. Отмена объединения ячеек (кнопка «Разбить по шаблону»).

Программа, выполняемая данной кнопкой, производит обратные объединению без потерь действия, т.е. отменяет объединение ячеек, разделяя строку по указанным разделителям значений и строк и размещает полученный массив данных в соответствующие ячейки.

Задание для выполнения. Проверьте работоспособность элементов надстройки. Впишите в отдельные ячейки ФИО свои и соседа. Установите в качестве разделителя значений «пробел» (для этого в поле «Разделитель значений» поставьте пробел).

Задание 3. Создание собственной надстройки.

Это задание продвинутого уровня. Выполняется по желанию.

Все материалы находятся по ссылке:

http://exceltip.ru/пошаговая-инструкция-по-созданию-над/

_

ФГБОУ ВО «БАШКИРСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕСИТЕТ»

Кафедра Математических методов в экономике

Информационные системы в экономике и управлении

Лабораторные работы №1-2

MS EXCEL

Уфа 2017

Содержание

Лабораторная работа 1. Основные операции с данными. ............................................................................ 3

1. Фильтрация данных. Расширенный фильтр. ...................................................................................... 3

2. Сортировка данных. Нестандартные и интересные способы применения. .................................... 7

3. Проверка данных. ............................................................................................................................... 11

4. Выпадающий список в ячейке ........................................................................................................... 12

Лабораторная работа 2. Использование формул и функций. .................................................................... 13

5. Работа с логическими формулами..................................................................................................... 13

6. Основные принципы работы с датами и временем в Excel ............................................................ 21

7. Поиск данных в диапазоне ................................................................................................................. 25

8. Формулы массива ............................................................................................................................... 30

Лабораторная работа 1. Основные операции с данными.

1. Фильтрация данных. Расширенный фильтр.

На новом листе создайте таблицу по приведенному образцу и переименуйте лист в Задание 1.

4. На втором, используя расширенный фильтр, выведите список работников старше 40 лет или имеющих зарплату з квартал менее 40000 руб.

5. На третьем, используя команду Итоги, рассчитайте зарплату по отделам, т.е. выведите общую зарплату 1-го отдела, 2-го отдела и 3-го отдела по месяцам.

1-1, задание 1-2, задание 1-3.

1. Создайте приведенную таблицу в MS Excel.

Указания

1. Переименуйте лист в задание 1-1.

2. На листе задание 1-1 воспроизведите таблицу из примера.

1-2

В ячейку G12 введите: <40000, а в ячейку H13 введите >40.

1-3

2. Сортировка данных. Нестандартные и интересные способы применения.

На листе Задание 2 выполните следующие примеры.

Пример 1. Сортировка по смыслу, а не по алфавиту

Распространенная ситуация: есть таблица, в которой присутствует столбец с названием месяца (январь, февраль, март...) или дня недели (пт, вт, ср...). При простой сортировке по данному столбцу Excel располагает элементы по алфавиту (т.е. от АдоЯ):

Получить на выходе привычную последовательность с января по декабрь или с понедельника по вторник можно с помощью особой сортировки по пользовательскому списку (custom list sorting).

Выделите таблицу и нажмите большую кнопку Сортировка на вкладке Данные (Data - Sort). Откроется диалоговое окно, в котором нужно задать поле (столбец) сортировки и в последнем раскрывающемся списке выбрать тип сортировки Настраиваемый список (Custom List):

После этого откроется следующее окно, в котором можно выбрать нужную нам последовательность месяцев или дней недели:

Если нужного списка (например, месяцев, но на английском) нет, то его можно ввести в правое поле, выбрав опцию Новый список (New List):

В качестве разделителя можно использовать запятую или клавишу Enter. Единожды создав такой пользовательский список, вы сможете им пользоваться и в других книгах Excel.

Интересный нюанс состоит в том, что таким образом можно сортировать не по алфавиту, а по значимости и важности любые иерархические объекты, а не только месяцы или дни недели. Например:

 должности (директор, замдиректора, руководитель департамента, начальник отдела...)

 воинские звания (генерал, полковник, подполковник, майор...)

 сертификации (TOEFL, ITIL, MCP, MVP...)

 клиентов или товары по важности лично для вас (виски, текила, коньяк, вино, пиво, лимонад...) и т.д.

Пример 2. Сортировка текста и чисел одновременно

Предположим, что в таблице есть столбец с кодами различных запчастей и агрегатов для автомобилей (part number). Причем крупные детали в сборе (например, коробка передач, двигатель, рулевое управление) обозначаются чисто цифровым кодом, а мелкие детали, которые в них входят - кодом с добавлением уточняющего номера через, допустим, точку. Попытка отсортировать такой список обычным образом приведет к нежелательному результату, т.к. Excel сортирует отдельно числа (номера крупных агрегатов в сборе) и отдельно текст (номера мелких деталей с точками):

Чтобы получить список, где после каждого крупного агрегата будут идти его детали:

Нужно временно добавить еще один столбец к таблице, в котором превратить все коды в текст с помощью функции ТЕКСТ:

Если затем произвести сортировку по этому столбцу, то Excel спросит о том, как ему сортировать числа и текст:

Если выбрать второй вариант в этом диалоговом окне, то Excel не будет преобразовывать номера крупных агрегатов в числа и станет сортировать весь список как текст, что и даст нам нужный результат. Вспомогательный столбец потом можно удалить.

3. Проверка данных.

На новом листе создайте таблицу по приведенному образцу и переименуйте лист в Задание 3.

4. Выпадающий список в ячейке

Создайте на новом листе список по приведенному образцу и переименуйте лист в Задание 4.

Сообщение об ошибке: тип «Предупреждение», текст сообщения придумайте сами.

Указания

Лабораторная работа 2. Использование формул и функций.

5. Работа с логическими формулами

На новом листе создайте таблицу по приведенному образцу и переименуйте лист в Задание 5-1.

На новом листе создайте таблицу по приведенному образцу и переименуйте лист в Задание 5-2.

5-3.

5-4.

5-4

5-2

5-2

5-3.

На новом листе создайте таблицу по приведенному образцу и переименуйте лист в Задание 5-5.

6. Основные принципы работы с датами и временем в Excel

Как Excel на самом деле хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек - вкладка Число - Общий), то можно увидеть следующую картину:

То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417

На самом деле любую дату Excel хранит и обрабатывает именно так - как число с целой и дробной частью. Целая часть числа (41209) - это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

 Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года.

 Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они – числа.

Как вводить даты и время в Excel

Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами - и понимает их все:

Классическая" форма

3.10.2006

Сокращенная форма

3.10.06

С использованием дефисов

3-10-6

С использованием дроби

3/10/6

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню - правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells):

Время вводится в ячейки с использованием двоеточия. Например

16:45

По желанию можно дополнительно уточнить количество секунд - вводя их также через двоеточие:

16:45:30

И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть

27.10.2012 16:45

Создать новый лист, переименовать его в Задание 6 и выполнить примеры 1-8.

Пример 1. Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

Если скопировать ячейку с датой (протянуть за правый нижний угол ячейки), удерживая правую кнопку мыши, то можно выбрать - как именно копировать выделенную дату:

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата - лучше воспользоваться функцией СЕГОДНЯ (TODAY):

Пример 2. Количество дней между двумя датами

Считается простым вычитанием - из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:

Пример 3. Количество рабочих дней между двумя датами

Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):

Пример 4. Сдвиг даты на заданное количество дней

Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.

Пример 5. Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.

Пример 6. Вычисление дня недели

Можно легко вычислить для любой даты при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

Первый аргумент этой функции - ячейка с датой, второй - тип отсчета дней недели (самый удобный - 2).

Пример 7. Вычисление временных интервалов

Поскольку время в Excel, как было сказано выше, такое же число, как дата, но только дробная его часть, то с временем также возможны любые математические

операции, как и с датой - сложение, вычитание и т.д.

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

Пример 8. Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.

Для вычислений длительностей интервалов дат в Excel есть функция РАЗНДАТ(), в английской версии - DATEDIF().

Нюанс в том, что Вы не найдете эту функцию в списке Мастера функций, нажав кнопку fx - она является недокументированной возможностью Excel. Точнее говоря, найти описание этой функции и ее аргументов можно только в полной версии англоязычной справки, поскольку на самом деле она оставлена для совместимости со старыми версиями Excel и Lotus 1-2-3. Однако, несмотря на то, что эту функцию не получится вставить стандартным способом через окно Вставка - Функция (Insert - Function), ее можно вручную вписать в ячейку с клавиатуры - и она сработает.

Синтаксис функции следующий:

РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

Самый интересный аргумент последний. Он определяет, каким именно образом и в каких единицах будет измеряться интервал между начальной и конечной датами. Этот параметр может принимать следующие значения:

"y"

разница в полных годах

"m"

в полных месяцах

"d"

в полных днях

"yd"

разница в днях с начала года без учета лет

"md"

разница в днях без учета месяцев и лет

"ym"

разница в полных месяцах без учета лет

Т.е. при желании подсчитать и вывести, например, ваш стаж в виде "3 г. 4 мес. 12 дн.", необходимо ввести в ячейку следующую формулу:

=РАЗНДАТ(A1;A2;"y")&" г. "&РАЗНДАТ(A1;A2;"ym")&" мес. "&РАЗНДАТ(A1;A2;"md")&" дн."

где А1 - ячейка с датой поступления на работу, А2 - с датой увольнения.

В качестве примера рассчитайте свой возраст в виде "20 л. 4 мес. 12 дн." и время учебы в университете в месяцах и днях.

7. Поиск данных в диапазоне

Создать новый лист, переименовать его в Задание 7 и выполнить примеры 1-4.

Пример 1. Функция ВПР

Имеем две таблицы - таблицу заказов и прайс-лист:

Задача - подставить цены из прайс-листа в таблицу заказов автоматически, ориентируясь на название товара с тем, чтобы потом можно было посчитать стоимость.

В наборе функций Excel, в категории Ссылки и массивы имеется функция ВПР (VLOOKUP). Эта функция ищет заданное значение (в нашем примере это слово "Яблоки") в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

Для простоты дальнейшего использования функции сразу дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме "шапки" (G3:H19), выберите в меню Вставка - Имя – Присвоить или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы - Вставка функции (Formulas - Insert Function). В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

o Искомое значение (Lookup Value) - то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае - слово "Яблоки" из ячейки B3.

o Таблица (Table Array) - таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя "Прайс" данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4, чтобы закрепить ссылку знаками доллара, т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.

o Номер_столбца (Column index number) - порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.

o Интервальный_просмотр (Range Lookup) - в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:

o Если введено значение 0 или ЛОЖЬ (FALSE), то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, "Кокос"), то она выдаст ошибку #Н/Д (нет данных).

o Если введено значение 1 или ИСТИНА (TRUE), то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с

"кокосом" функция попытается найти товар с наименованием, которое максимально похоже на "кокос" и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст - например, при расчете Ступенчатых скидок.

Нажать ОК и скопировать введенную функцию на весь столбец.

Ошибки #Н/Д и их подавление

Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:

o Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.

o Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.

o Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так: =ВПР(ТЕКСТ(B3);прайс;0)

o Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:

=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)

Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться функциейЕСЛИОШИБКА (IFERROR). Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:

=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)

Пример 2. Функции: ИНДЕКС и ПОИСКПОЗ .

Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.

Задача решается при помощи двух функций:

=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)

Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16. Последний аргумент функции 0 - означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемый артикул.

Функция ИНДЕКС выбирает из диапазона A1:G13 значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция ПОИСКПОЗ) и столбца (нам нужен регион, т.е. второй столбец).

Пример 3. Многоразовый ВПР (VLOOKUP)

Имеем список заказов с номерами и названиями товаров. Хотелось бы, для примера, вытаскивать из таблицы по номеру заказа все товары, которые в него входят. Примерно так:

Функция ВПР в такой ситуации поможет только частично, т.к. умеет

вытаскивать данные только по первому найденному соответствию, т.е. даст нам только Яблоки. Для нахождения и извлечения из таблицы всех наименований лучше использовать формулу массива. Вот такую:

=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5))

Ее надо ввести следующим образом:

o выделить ячейки, куда должны выводиться результаты (в нашем примере - это диапазон D6:D20)

o ввести (скопировать формулу в первую ячейку) диапазона

o нажать Ctrl + Shift + Enter (т.е. ввести формулу в массив См. п.8 Формулы массива)

Вычитание единицы в фрагменте СТРОКА(B2:B16)-1 делается из-за шапки таблицы. По той же причине для компенсации сдвига результирующего диапазона относительно исходного вычитается число пять во фрагменте СТРОКА()-5

Чтобы скрыть ошибку #ЧИСЛО!, которая будет появляться в незаполненных ячейках результирующего диапазона D6:D20 можно использовать функции проверки ошибок ЕСЛИ и ЕОШ, заменив нашу формулу чуть более сложной:

=ЕСЛИ(ЕОШ(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5)));"";ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5)))

В Excel 2007 появилась более удобная функция ЕСЛИОШИБКА - она позволяет решить задачу более компактно:

=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5));"")

Пример 4. Функция ПРОСМОТР.

Изучите самостоятельно возможности функции ПРОСМОТР, её ограничения. Проиллюстрируйте ее применение на данных примера 1.

8. Формулы массива

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

Формулы массива в Excel - это специальные формулы для обработки данных из таких массивов. Формулы массива делятся на две категории - те, что возвращают одно значение и те, что дают на выходе целый набор (массив) значений. Рассмотрим их на простых примерах.

Создать новый лист, переименовать его в Задание 8 и выполнить примеры 1-3.

Пример 1. Товарный чек

Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:

1. выделяем ячейку С7

2. вводим с клавиатуры =СУММ(

3. выделяем диапазон B2:B5

4. вводим знак умножения (звездочка)

5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ - в итоге должно получиться так:

Чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter.

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Обратите внимание на фигурные скобки, появившиеся в формуле - отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно - они автоматически появляются при нажатии Ctrl + Shift + Enter.

Пример 2. Транспонирование

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП это делается на раз.

Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

1. Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.

2. Вводим функцию транспонирования =ТРАНСП(

3. В качестве аргумента функции выделяем наш массив ячеек A1:B8

4. Жмем Ctrl + Shift + Enter и получаем "перевернутый массив" в качестве результата:

Примечание: Есть другой вариант транспонирования.

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажавF2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Пример 3. Выборочное суммирование

При помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.