Краткие теоретические сведения

Функции в MS Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами. Помимо встроенных функций вы можете использовать в вычислениях пользовательские функции, которые создаются при помощи средств MS Excel. Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если вы нарушите правила синтаксиса, MS Excel выдаст сообщение о том, что в формуле имеется ошибка.

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

Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой ";". Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. Помните о том, что при записи функции должны присутствовать открывающая и закрывающая скобки, при этом не следует вставлять пробелы между названием функции и скобками. В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах MS Excel можно использовать до семи уровней вложенности функций.

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

Функция ЕСЛИ имеет следующий синтаксис: =ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь).

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно также использовать текстовые аргументы.

Функция ПРОСМОТР возвращает значение из строки, столбца или (Диапазон: Две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными.)массива (Массив: Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.). Функция имеет две синтаксических формы: векторную и форму массива.

Вектор — это диапазон, содержащий только одну строку или один столбец. Векторная форма функции ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор), в поисках определенного значения и возвращает значение из соответствующей позиции второго диапазона. Эта форма функции ПРОСМОТР применяется, когда требуется указать диапазон, содержащий значения для сопоставления. Другая форма функции ПРОСМОТР автоматически выполняет поиск в первом столбце или первой строке.Синтаксис ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов]).

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

Совет. Как правило, вместо формы массива функции ПРОСМОТР лучше использовать функцию ГПР или ВПР. Эта форма функции ПРОСМОТР предназначена для обеспечения совместимости с другими редакторами электронных таблиц. Синтаксис ПРОСМОТР (искомое_значение; массив).

Задания и инструкции по выполнению

Задание 1 Создание списка Клиенты.

1. Открыть программу электронных таблиц Microsoft Excel.

2. Переименовать Лист 1 в База данных, для этого дважды щелкните мышью по ярлычку и введите новое имя.

3. Создать заголовки к столбцам таблицы по образцу

 4. Выделить столбец J и отформатировать столбец, выбрав процентный формат.

 5. Залить светло-серым цветом строку заголовка.

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

7. Ввести в соответствующие поля данные о клиентах, завершая ввод каждой записи нажатием кнопки Добавить. Переход между отдельными полями ввода осуществляется посредством щелчка мыши или нажатием клавиши Tab. После ввода последней записи щелкните на кнопке Закрыть.

 8. Отсортируйте данные в алфавитном порядке по столбцу Названиям фирмы.

 9. Выделить столбец С и в строке формул присвоить имя Код, столбцу В-Фирма, столбцу J – Скидка.

Задание 2 Создание списка Товары.

1. Перейти на Лист 2. Переименовать Лист 2 в Товары.

2. Создать таблицу по образцу.

4. Выделите столбец A и в строке формул присвоить имя Номер, столбцу B – Товар, столбцу C - Цена.

Задание 3 Создание списка Заказы.

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

1. Перейти на Лист 3. Переименовать Лист 3 в Заказы.

2. Создать структуру базы данных. Для этого в ячейках A1-L1 указать следующие названия полей: Месяц, Дата, Номер заказа, Номер товара, Наименование товара, Количество, Цена за ед., Код заказчика, Название фирмы, Сумма заказа, Скидка, Уплачено.

3. Выделить по очереди столбцы и ввести в поле имени имена В- Дата, С- Заказ, D- Номер2,

Е- Товар2, F- Количество, G- Цена2, Н- Код2, I- Фирма2, J- Сумма, К- Скидка2, L- Оплата:

4. Определить и присвоить форматы для полей: Дата— Дата; Цена, Сумма заказа, Скидка, Уплачено— Денежный.

5. Данные в таблицу НЕ ВВОДИТЬ!

6. Создать расчетные формулы:

6.1.  В ячейку Е2  ввести формулу =ЕСЛИ($D2="";"";ПРОСМОТР($D2;Номер;Товар))

ПРИМЕЧАНИЕ: Данная формула требует небольших пояснений. Функция ЕСЛИ проверяет содержимое ячейки D2. Если в ячейке D2 данные отсутствуют, то в ячейка Е2 также останется незаполненной. Если же в ячейку D2 уже введен номер товара, то будет выполняться поиск номера товара в диапазоне Номер и в ячейку D2 возвратится соответствующее значение диапазона Товар. Для этого используется функция ПРОСМОТР.

6.2. В ячейку G 2 ввести формулу = ЕСЛИ($D2="";"";ПРОСМОТР($D2;Номер;Цена))

Скопировать формулу до строки 15

ПРИМЕЧАНИЕ: Целесообразно скопировать формулу из ячейки E2 в ячейку G2 и затем только изменить имя диапазона.

6.3. В ячейку I 2 ввести формулу = ЕСЛИ($ H 2="";"";ПРОСМОТР($ H 2;Код;Фирма))

Скопировать формулу до строки 15

6.4. В ячейку J 2 введите формулу =ЕСЛИ(F2="";"";F2*G2)

Скопировать формулу до строки 15

6.5. В ячейку K 2 ввести формулу самостоятельно. Скопировать формулу до строки 15

6.6. В ячейку L 2 введите формулу = ЕСЛИ(J2="";"";J2-J2*K2)

Скопировать формулу до строки 15.

7.Заполнить таблицу данными, используя Форму.

Задание 4 Создание бланка заказа.

1. Переименуйте Лист 4 в Бланк.

2.Создать бланк заказа по образцу.

 3. Ввести формулы в бланк заказа

3.1. В ячейку F 2 ввести формулу =ЕСЛИ($D$2="";"";ПРОСМОТР($D$2;Заказ;Дата))

Помните, что формат ячейки F 2 должен быть Дата

3.2.В ячейку D 4 ввести формулу = ЕСЛИ($D$2="";"";ПРОСМОТР($D$2;Заказ;Фирма2))

3.3. В ячейку H 4 ввести формулу =ЕСЛИ($D$2=""; ""; ПРОСМОТР($D$2;Заказ;Код2))

3.4. Для остальных полей Бланка заказа формулы ввести самостоятельно.

ПРАКТИЧЕСКАЯ РАБОТА

« Средства анализа данных в таблицах. Диаграммы. Графики » Совместное использование нескольких рабочих листов

Цель работы

1. Освоить процесс создания оборотно-сальдовой ведомости с помощью MS Excel.

    2 Формируемые образовательные результаты

   Образовательные результаты У2, У3, З4, З9, ОК5, ПК2.1