Практическая работа Excel "Связанные таблицы"

Приложение (меню разработчик)

Меню разработчик

Лист1 – мат.плата

пкм

пкм

Практическая работа (прайс-лист комплектующие компьютера)

Связанные таблицы

1. В зависимости от количества комплектующих создаем несколько листов с соответствующими именами. (пример)

Лист 1 –присвоим имя Материнская плата

1. Создаем итоговый лист (лист – связку, смотрим приложение)

2. Заполняем лист связку (смотри приложение)

Заполняем первую форму - Материнская плата

В поле Формировать список по диапазону указываем Материнская плата

и адрес диапазона $A$2:$A$4 (лкм на имени листа и диапазона)

В поле Связь с ячейкой введите адрес любой пустой ячейки на том же листе, например, D6. В этой ячейке будет храниться номер элемента, выбранного из списка (по порядку: 1-ый, 2-ой или 3-ий).

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

1. Заполняем D6 на листе – связке используя данные листа Материнская плата

Для того чтобы при выборе разных компонентов в столбце D на лист – связку подставлялись правильные цены, используйте функцию ЕСЛИ. Смысл ее таков: если в той ячейке, где хранится номер выбранного элемента (в нашем примере это ячейка D6 на листе Платы), хранится число 1 – то на листе – связке должна появиться цена соответствующей модели материнской платы из ячейки В2, если 2 – другая цена из ячейки В3 и т.д. (указывайте адреса соответствующих ячеек щелчком мыши)

Для проверки: вложенная функция ЕСЛИ в строке формул:

=ЕСЛИ(Платы!D6=1;Платы!B2;ЕСЛИ(Платы!D6=2;Платы!B3;Платы!B4))

Если в D6 стоит 1, то цена из В2; иначе: если в D6 стоит 2, то цена из В3, иначе цена из В4

Проверьте работу функции ЕСЛИ (выбирайте разные платы и следите за изменением цены):

Выполните аналогичные действия для всех остальных полей (процессоров, памяти, винчестеров и т.д.)

1. В ячейке D18 на листе – связке вычислите сумму по столбцу D (=D6+D8+D10+D12+D14+D16). Проверьте ее изменение при выборе других моделей комплектующих.

1. Работа с переключателями. Переключатели будут работать следующим образом: если будет выбран переключатель 2 (гарантия 2 года), то сумма покупки увеличится на 50$. Щелкните ПКМ по любому из переключателей и выберите команду Формат объекта, установите связь с любой пустой ячейкой на листе – связке, например G20, в которой будет храниться номер выбранного переключателя (1 или 2)

1. В ячейке D20, используя функцию ЕСЛИ (=ЕСЛИ(G20=2;50;0)), вычислите стоимость гарантии (если переключатель в ячейке G20 = 2, то начисляем 50 $, иначе нет начислений)

1. Работа с флажком. Если установлен флажок «нужна доставка», то цена покупки увеличится на 20 $. Щелкните ПКМ по флажку и команду Формат объекта. Установите связь с любой пустой ячейкой на листе – связке, например G22, в которой будет храниться значение, соответствующее состоянию флажка (ИСТИНА, если флажок установлен и ЛОЖЬ в противном случае).

1. В ячейке D22, используя функцию ЕСЛИ (=ЕСЛИ(G22=ИСТИНА;20;0)), вычислите стоимость доставки (если в ячейке G22 стоит значение ИСТИНА, то начисляем 20$, иначе нет начислений)

1. В ячейке D24, используя функцию ЕСЛИ (=ЕСЛИ(D18700;D18*0,05;0)), вычислите величину скидки, которая зависит от стоимости заказа (если сумма в ячейке D18 более 700$ то предоставляется скидка 5%, иначе – не предоставляется).

1. В ячейке D26 вычислите полную стоимость компьютера с учетом гарантии, доставки, скидки. (=D18+D20+D22-D24)

1. Вычислите в столбце Е соответствующие цены в рублях, используйте абсолютную ссылку на курс $.

2. Подберите самый дешевый и самый дорогой вариант комплектации компьютера.