Организация ввода данных на основе списка

РАЗДЕЛ 4. Технологии ОБРАБОТКИ ТАБЛИЧНОЙ ИНФОРМАЦИИ средствами MS EXCEL

Лабораторная работа 4.2. Использование функций для автоматизации обработки электронных таблиц. Списки в Excel..

Цели обучения: формирование у учащихся представлений о назначении функций в Excel, о типах и правилах задания функций, умений применять функции и списки для автоматизации работы с электронными таблицами средствами MS Excel.

Ожидаемые результаты обучения - учащиеся должны

знать:

· назначение функций в Excel;

· правила задания функций в Excel;

· назначение списков в Excel;

уметь:

· применять различные виды функций для решения задач в Excel;

· создавать раскрывающиеся списки в Excel.

Описание практического задания

Функции в Excel

Функции Excel – это заранее созданные формулы, которые используют в качестве исходных данных одно или несколько значений и возвращают результат. В Excel можно использовать встроенные функции и функции, которые создаются пользователем на языке VBA.

Каждая функция состоит из уникального имени и аргументов. Имя функции описывает операцию, которую эта функция выполняет, например, СУММ.

Аргументы функции Excel всегда заключены в круглые скобки. В качестве аргументов могут использоваться значения (константы), математические выражения, ссылки на ячейки, на диапазоны ячеек или другие функции. Количество аргументов зависит от конкретной функции. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки после имени функции, например, функция СЕГОДНЯ(), выводящая в качестве результата текущую дату. Если функция имеет более одного аргумента, то все ее аргументы разделяются символом точка с запятой (;). Открывающая скобка ставится без пробела сразу после имени функции. Функции могут вводиться в ячейки электронной таблицы как самостоятельные формулы или являться частью какой-либо формулы.

Например, если в какую-либо ячейку таблицы введена формула =СУММ(A2;A4), то СУММ — это имя функции, а A2 и A4 — ее аргументы. Эта формула суммирует числа в ячейках A2 и A4.

Если в ячейку таблицы введена формула =СУММ(A2:A4), то такая функция будет иметь один аргумент — диапазон ячеек A2:A4 . Эта формула суммирует числа в ячейках A2, А3 и A4.

Функции в ячейку на рабочем листе можно вводить прямо с клавиатуры или с помощью кнопки f_x (Вставить функцию) в группе Библиотека функций вкладки Формулы. Эта кнопка также отображается в окне Excel левее строки формул.

При нажатии кнопки f_x, выводится диалоговое окно Мастер функций – шаг 1 из 2.

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

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

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

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

Ниже будут рассмотрены некоторые часто используемые функции.

Функции категории «Дата и время»

Функция ГОД

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

ГОД(дата_в_числовом_формате)

Например, если в ячейке А12 содержится дата 10.12.16, то результатом функции

=ГОД(А12)будет число 2016.

Функция МЕСЯЦ

Возвращает месяц в дате, заданной в числовом формате. Месяц возвращается как целое число в диапазоне от 1 (январь) до 12 (декабрь). В качестве аргумента может использоваться дата_в_числовом_формате или ссылка на ячейку, содержащую дату.

МЕСЯЦ(дата_в_числовом_формате) , где

Например, если в ячейке А12 содержится дата 10.12.2016, то результатом функции

=МЕСЯЦ(А12)будет число 12.

Функция СЕГОДНЯ

Возвращает текущую дату в числовом формате. Числовой формат даты - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени.

СЕГОДНЯ()

Функции категории «Математические»

Функция ОКРУГЛ

Округляет число до указанного количества десятичных разрядов по правилам округления.

ОКРУГЛ(число; число_разрядов), где

Число - это округляемое число.

Число_разрядов - это количество десятичных разрядов, до которого нужно округлить число.

Функция ОКРУГЛВВЕРХ

Округляет число по модулю до ближайшего большего числа по модулю.

ОКРУГЛВВЕРХ(число; число_разрядов),где

Число - это любое число, которое нужно округлить с избытком.

Число_разрядов - это количество десятичных разрядов, до которого нужно округлить число.

Функция ОТБР

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

ОТБР(число; число_разрядов), где

Число - это усекаемое число.

Число_разрядов - это число, определяющее точность усечения.

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

Примеры использования функций ОКРУГЛ, ОКРУГЛВВЕР, ОТБР:

Результатом функции ОКРУГЛ(11,45;0) будет число 11.

Результатом функции ОКРУГЛВВЕРХ(11,45;0) будет число 12.

Результатом функции ОТБР(11,45;0) будет число 11.

Результатом функции ОКРУГЛ(11,65;0) будет число 12.

Результатом функции ОКРУГЛВВЕРХ(11,65;0) будет число 12.

Результатом функции ОТБР(11,65;1) будет число 11,6.

Функция СУММПРОИЗВ

Перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.

СУММПРОИЗВ(массив1;массив2;массив3; ...), где

Массив1, массив2, массив3, ... - это от 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить их произведения.

Аргументы, которые являются массивами, должны иметь одинаковые размерности. Если это не так, то функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!.

СУММПРОИЗВ трактует нечисловые элементы массивов как нулевые.

Пример использования функции СУММПРОИЗВ:

Функции СУММПРОИЗВ (А1:А6; С1:С6) перемножает все компоненты двух массивов, а затем складывает полученные произведения, то есть выполняются следующие вычисления: 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3. Результатом вычисления будет число 156.

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

Рис. 1. Фрагмент таблицы для примера.

Функция СУММЕСЛИ

Суммирует ячейки диапазона строк или столбцов, если они удовлетворяют заданному критерию.

СУММЕСЛИ(диапазон; условие; диапазон_суммирование), где

Диапазон — это интервал ячеек, в которых проверяется, удовлетворяют ли они заданному условию.

Условие — это условие в форме числа, выражения, или текста или адрес ячейки, где записано это условие. Например, условие может быть выражено как 100, ">0", "занято" или F5 (в ячейке F5 записано 100, или >0, или слово занято).

Диапазон_суммирования — это ячейки для суммирования. Ячейки в Диапазон_суммирования суммируются, только если соответствующие им ячейки в аргументе Диапазон удовлетворяют условию.

Например, для того, чтобы подсчитать суммарный фонд з/платы в плановом отделе (см. Рис. 1) функция будет иметь вид:

=СУММЕСЛИ(E2:E21;"Плановый";J2:J21)

или

=СУММЕСЛИ(E2:E21;L2;J2:J21) (в ячейке L2 - название отдела «Плановый»)

Функция СУММЕСЛИМН

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

СУММЕСЛИМН(диапазон_суммирование; диапазон условия1; условие1; диапазон условия2; условие2;….), где

Диапазон_суммирования — это ячейки для суммирования. Ячейки в Диапазон_суммирования суммируются, только если соответствующие им ячейки в аргументах Диапазон условия… удовлетворяют заданным условиям.

Диапазон условия… — это интервал ячеек, в которых проверяется, удовлетворяют ли они заданному условию.

Условие…— это условие в форме числа, выражения, или текста или адрес ячейки, где записано это условие. Например, условие может быть выражено как 100, ">0", "занято" или F5 (в ячейке F5 записано 100, или >0, или слово занято).

Например, для того, чтобы подсчитать суммарный фонд з/платы всех экономистов в плановом отделе (см. Рис. 1) и оформить в виде таблицы, приведенной на рисунке 2, функция будет иметь вид:

=СУММЕСЛИМН(J2:J21;E2:E21;L2;F2:F21;M2)

Рис. 2. Пример использования СУММЕСЛИМН.

Функции категории «Статистические»

Функция МАКС

Возвращает наибольшее значение из набора значений.

МАКС(число1;число2; ...), где

Число1, число2, ... - это от 1 до 255 чисел, ссылок на ячейки или диапазоны ячеек с числами, среди которых ищется максимальное значение.

Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Если аргумент является массивом или ссылкой, то в нем учитываются только числа. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются. Если логические значения или текст не должны игнорироваться, следует использовать функцию МАКСА.

Функция МИН

Возвращает наименьшее значение в списке аргументов.

МИН(число1;число2; ...), где

Число1, число2, ... — это от 1 до 255 чисел, ссылок на ячейки или диапазоны ячеек с числами, среди которых ищется минимальное значение.

Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения или тексты в массиве или ссылке игнорируются. Если логические значения или тексты игнорироваться не должны, следует пользоваться функцией МИНА.

Функция СРЗНАЧ

Возвращает среднее (арифметическое) своих аргументов.

СРЗНАЧ(число1; число2; ...), где

Число1, число2, ... - это от 1 до 255 аргументов, для которых вычисляется среднее значение.

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

Совет. Вычисляя средние значения ячеек, следует учитывать различие между пустыми ячейками и ячейками, содержащими нулевые значения. Пустые ячейки не учитываются, но нулевые ячейки учитываются.

Функция СЧЕТ

Подсчитывает количество чисел в списке аргументов. Функция СЧЁТ используется для получения количества числовых ячеек в интервалах или массивах ячеек.

СЧЁТ(значение1; значение2; ...), где

Значение1, значение2, ... - это от 1 до 30 аргументов, которые могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа.

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

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

Примеры использования функций МАКС, МИН, СРЗНАЧ и СЧЕТ для таблицы, приведенной на Рис. 1

Результатом функции МАКС(J2:J21) будет значение 75000р.

Результатом функции МИН(J2:J21) будет значение 1500р.

Результатом функции СРЗНАЧ(J2:J21) будет значение 33878р.

Результатом функции СЧЁТ(J2:J21) будет значение 20, т.к. в заданном интервале столбца J – 20 ячеек с числами.

Результатом функции СЧЁТ(B2:B21) будет значение 0, т.к. в заданном интервале столбца В – нет ячеек с числами.

Функция СЧЕТЕСЛИ

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

СЧЁТЕСЛИ(диапазон; условие), где

Диапазон — это интервал ячеек, в которых проверяется, удовлетворяют ли они заданному условию.

Условие — это условие в форме числа, выражения, или текста или адрес ячейки, где записано это условие. Например, условие может быть выражено как 100, ">0", "занято" или F5 (в ячейке F5 записано 100, или >0, или слово занято).

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

=СЧЕТЕСЛИ(E2:E21;"Плановый")

Функции категории «Логические»

Функция ЕСЛИ

Возвращает одно значение, если заданное условие выполняется, и другое значение, если заданное условие не выполняется.

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь), где

Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ. Знак равенства ( = ) в логическом выражении называется оператором сравнения. В качестве операторов сравнения могут также использоваться знаки >, <, >=, <=, <> (не равно).

Значение_если_истина — это значение, которое возвращается, если лог_выражение выполняется, т.е. равно ИСТИНА. Значение_если_истинаможет быть формулой, числом или текстом.

Значение_если_ложь — это значение, которое возвращается, если лог_выражение не выполняется, т.е. равно ЛОЖЬ. Значение_если_ложь может быть формулой, числом или текстом.

Несколько функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов Значение_если_истина и Значение_если_ложь для конструирования более сложных проверок.

На Рис. 3 приведен пример использования функции ЕСЛИ. С помощью этой функции статус «занято» присваивается помещению, если для этого помещения задан Арендатор, т.е. ячейка с именем Арендатора не пустая. Если арендатор не задан для помещения, то присваивается статус «свободно».

Рис. 3. Пример использования функции ЕСЛИ.

Функция И

Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА (т.е. выполняются все проверяемые условия); возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ (т.е. не выполняется хотя бы одно условие).

И(лог_ значение1; лог_ значение2; … ), где

Логическое_значение1, логическое_значение2, ... — это от 1 до 255 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

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

Функция ИЛИ

Возвращает значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА (т.е. выполняется хотя бы одно проверяемое условие); возвращает значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ (т.е. не выполняется ни одно условие).

ИЛИ(лог_ значение1; лог_ значение2; … ), где

Логическое_значение1, логическое_значение2, ... — это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

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

Функции И и ИЛИ часто используются как аргументы функции ЕСЛИ.

Функция категории «Ссылки и массивы»

Функция ВПР

Ищет значение в крайнем левом столбце таблицы и возвращает значение из той же строке из указанного столбца таблицы.

ВПР(искомое_значение; таблица;номер_столбца;интервальный_просмотр), где

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

Таблица - это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например, БазаДанных..

Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента таблица должны быть расположены в возрастающем порядке; в противном случае функция ВПР может выдать неправильный результат. Если интервальный_просмотр имеет значение ЛОЖЬ, то таблица не обязана быть отсортированной.

Регистр не учитывается (т. е. строчные и заглавные буквы не различаются).

Номер_столбца - это номер столбца в массиве таблица, в котором должно быть найдено соответствующее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента таблица; если номер_столбца равен 2, то возвращается значение из второго столбца аргумента таблица и так далее. Если номер_столбца больше, чем количество столбцов в аргументе таблица, то функция ВПР возвращает значение ошибки #ССЫЛ!.

Интервальный_просмотр - это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение 1 (ИСТИНА) или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение 0 (ЛОЖЬ), то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Замечания

§ Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение.

§ Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента таблица, то функция ВПР возвращает значение ошибки #Н/Д.

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

Организация ввода данных на основе списка

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

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

Рис. 4. Таблица "Отчет о продажах".

Рис. 5. Справочник товаров.

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

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

· списку наименований товаров, находящемуся в таблице Справочник товаров присвоить имя (описано ниже), например, имя Наименование_товара;

· в таблице, в которой надо организовать ввод данных через список, выделить блок ячеек для ввода данных на основе списка;

· выполнить команду Проверка данных группы Работа с данными вкладки Данные;

· в появившемся окне на вкладке Параметры выбрать Тип данных – Список;

· установить курсор в окошко Источник и нажать клавишу F3 на клавиатуре (вызов списка именованных блоков);

· в появившемся окошке выбрать блок, содержащий названия всех товаров – Наименование_товара();

· нажать ОК.

Список значений может быть организован не только на основе данных другой таблицы, но и на основе фиксированного набора значений. Для этого в окошке Источник окна Проверка вводимых значений необходимо ввести нужные элементы списка через «;» (точка с запятой).

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

На Рис. 6 показан вид окна Проверка вводимых значений для создания раскрывающегося списка.

Рис. 6. Пример заполнения окна для организации списка.

Для удаления проверки данных при вводе необходимо:

· в таблице выделить блок ячеек, в которых надо отменить проверку вводимых данных;

· выполнить команду Проверка данныхгруппы Работа с данными вкладки Данные;

· в окне Проверка вводимых значений на вкладке Параметры нажать кнопку Очистить все.