Автоматическое заполнение столбцов

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

1) СИНТАКСИС функции ПРОСМОТР:

ПРОСМОТР (искомое значение; просматриваемый вектор; вектор результатов)

Вектор в MS Excel - это массив, который содержит только одну строку или один столбец.

Искомое_значение - это значение, которое ПРОСМОТР ищет в первом векторе.

Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.

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

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

СИНТАКСИС функции ЕСЛИ

ЕСЛИ (Р, А, В), где Р - логическое выражение, А – значение, вычисленное при истинном значении Р, В – значение, вычисленное при ложном значении Р.

С помощью функции ЕСЛИ можно задать отмену заполнения ячеек в том случае, если запись не введена, что позволит избежать появления значений ошибки.

Заполним столбец Наименование товара. Для этого.

Сделайте текущей ячейку E2.

В строке формул введите следующую формулу:

=ПРОСМОТР(D2;Товары!$A$2:$A$10;Товары!$B$2:$B$10), где ячейка D2 – искомое значение, которое необходимо найти в просматриваемом векторе, диапазон $A$2:$A$10 – просматриваемый вектор (столбец Номер товара в списке Товары, $B$2:$B$10 – вектор результатов (столбец Наименование товара в списке Товары).

Скопируйте формулу в смежные ячейки E3:E12.

Используя функцию ПРОСМОТР, заполните самостоятельно столбец Фирма.

Определим значение столбца Сумма: Цена (список Товары)*Количество (список Заказы).

Для этого в ячейку I2 введите формулу: =ЕСЛИ(H2="";"";H2*ПРОСМОТР(D2;Товары!$A$2:$A$10;Товары!$C$2:$C$10)), где H2 – ячейка с количеством, D2 - просматриваемая ячейка, диапазон $A$2:$A$10 – просматриваемый вектор (столбец Номер товара в списке Товары), а диапазон $C$2:$C$10 – вектор результатов (столбец Цена в списке Товары).

Т. о. если ячейка Н2 = 0 (данные отсутствуют), то возвращаемое значение отсутствует (‘’’’), если ячейка Н2 содержит данные, то возвращаемое значение равно H2*ПРОСМОТР(D2;Товары!$A$2:$A$10;Товары!$C$2:$C$10).

Рассчитайте значения столбца Скидка. Для этого в ячейку J2 введите формулу: =I2*ПРОСМОТР(F2;Клиенты!$B$2:$B$10;Клиенты!$F$2:$F$10), где диапазон $B$2:$B$10 - просматриваемый вектор (столбец Код заказчика в списке Клиенты), а диапазон $F$2:$F$10 – вектор результатов (столбец Скидка в списке Клиенты).

Рассчитайте значения столбца Оплачено.

Задание 2. Создание Бланка заказов

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

Откройте 4-й лист вашей рабочей книги. Дайте ему название Бланк.

В область для номера заказа введите любой номер из списка Заказы (рис.1.7)

Рис.1.7 Создание бланка Заказов

В остальные выделенные области будем вносить формулы:

В область Дата внесите формулу, позволяющая вводить автоматически дату

= ПРОСМОТР (Ячейка Номер заказа из Бланка; поле Номер заказа из списка Заказы; поле Дата из этого же списка)

В область Название фирмы внесите формулу, позволяющую вводить автоматически название фирмы заказчика.

= ПРОСМОТР (Ячейка Номер заказа из Бланка; поле Номер заказа из списка Заказы; поле Фирма из этого же списка)

Аналогично заполним выделенные области Количество, Скидка, К оплате.

В область Доверенность выдал внесите формулу, позволяющая вводить автоматически фамилию: = ПРОСМОТР (ячейка Название фирмы из Бланка, Поле Название фирмы из списка Клиенты, поле Контактная персона из этого же списка).

Аналогично заполните область Телефон.

В область Цена единицы внесите формулу: =ПРОСМОТР (ячейка Название товара из Бланка, поле Наименование товара из списка Товары, поле Цена из этого же списка)

Внесите в ячейку G11 для поля Оформил свою Фамилию.

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

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

Выделим весь список.

Выберем команду Данные / Группа и структура / Группировать / Столбцы. Получим структуру первого уровня (вся таблица). Обратите внимание на появившуюся линию уровня Охватывающую все поля списка и заканчивающуюся кнопкой со знаком « - ». Выполнив щелчок по кнопке можно свернуть список (рис.1.8).

Рис.1.8

Выделим из нее таблицы второго уровня, содержащие поля

Месяц

Дата

Номер заказа

Номер товара

Наименование товара

Выберем команду Данные / Группа и структура / Группировать / Столбцы. Получим структуру второго уровня. Продолжая процесс группировки по столбцам, а затем по строкам, получите следующую иерархию (рис.1.9):

Рис.1.9

Щелкая по соответствующим кнопкам со знаком «- » и «+», сверните и разверните элементы структуры.

Для удаления структуры выбирается команда Данные / Группа и структура / Удалить структуру.

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

Выделим столбцы Номер товара и Наименование товара. Выполним группировку. Результатом операции будет группировка полей Номер товара и Наименование товара.

Выполните группировку по записям ОАО Финиш.

Сверните и разверните классы структуры.

Задание 2. Автоструктурирование.

Автоструктурирование выполняется только для таблиц содержащих формулы.

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

Сформируем и заполним таблицу ВЕДОМОСТЬ (рис.1.10).

Рис.1.10

В ячейки E4, F4, G4, H4, I4, J4, K4 вставьте формулы и скопируйте их на остальные ячейки списков (рис.1.11).

Рис.1.11

Выполним над заполненным списком Автоструктурирование. Данные / Группа и структура / Создать структуру (рис.1.12).

Рис.1.12

Свернем структуру по нижним уровням (рис.1.13).

Рис.1.13

Свернем по верхнему уровню. Получим только одно поле «К выдаче» (рис.1.14)

Рис.1.14

Удалите структуру.

Задание 3. Структурирование с подсчетом ИТОГОВ.

Выполним структурирование списка ВЕДОМОСТЬ с подсчетом Итогов.

Для выполнения структурирования необходимо определить основное поле, по которому будет проводиться структурирование. Пусть это будет поле Ф.И.О.

Выполним сортировку записей в поле, тем самым разобьем записи на классы. Для этого выделим диапазон ячеек А4:К12, выполним команду Данные / Сортировка, в окне установите сортировку по полю ФИО.

Выполним команду Данные / Итоги. В открывшемся окне установим

Заголовок изменяющегося поля;

Операцию;

Поля с вычисляемыми итогами (Начислено, Удержано, К выдаче);

Итоги под данными;

Другие опции (рис.1.15).

Рис.1.15

Получим структуру. Строки с итогами выделены жирным шрифтом (рис.1.16).

Рис.1.16

Свернем структуру. Получим только Итоговые строки (рис.1.17).

Рис.1.17

Выполнив свертку еще раз, получим одну строку Общих итогов (рис.1.18).

Рис.1.18

Удалите структуру, выделите список, затем введите команду Данные / Итоги / Убрать все.

Скопируйте таблицу «Ведомость» на новый лист.

Подведите Итоги, изменив функцию Суммы на функцию Среднее.

Перегруппируйте данные (проведите сортировку по месяцам) и выполните подсчет Итогов по месяцам (рис.1.19).

Рис.1.19

Свернем структуру. Получим только Итоговые строки.

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

Измените функцию Суммы на функцию Среднее.