Функции поиска и связи таблиц

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

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

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

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

Это функции из группы «Ссылки и массивы» Мастера функций - ВПР (вертикальный поиск) и

ГПР (горизонтальный поиск).

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

Функция ГПР просматривает первую строку таблицы в поисках заданного значения и возвращает соответствующее значение из строки с указанным номером.

Формат функций:

ВПР(искомое знач.-е;таблица;номер столбца; тип просмотра)

ГПР(искомое знач.-е;таблица;номер строки; тип просмотра)

Аргументы функций ВПР:

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

ü Таблица - это справочная таблица, в которой ищутся данные. Можно указать её координаты (абсолютные адреса!) или имя в списке имён.

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

ü Тип просмотра -необязательный аргумент, имеет значение логической константы ИСТИНА или ЛОЖЬ и определяет, нужно ли, чтобы функция искала точное или приближенное соответствие.

· Если этот аргумент - ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; а именно: если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем заданное. Значения в первом столбце таблицы должны быть расположены в возрастающем порядке.

· Если этот аргумент - ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д. Таблица в этом случае не обязана быть сортированной.

Функция ГПР имеет те же аргументы, за исключением номера столбца, который заменён на номер строки.

Пример 8.1. Подсчитать общую стоимость поступившего товара в магазин, используя справочную таблицу – «Прейскурант товаров».

Рис.19. Таблица учёта

поступившего товара.

Рис.20. Справочная таблица

«Прейскурант товаров».

Решение:

1) Вычислить стоимость партии каждого товара в таблице учёта (рис.19) по формуле:

«Стоимость партии» = «размер партии» * «цена за кг.»,

где «цена за кг.» находится в таблице «Прейскурант товаров» по заданному наименованию продукции:

 

 

= ВПР (B3 ;$G$5:$H$9; 2; ЛОЖЬ)* D3;

 

 

2) Скопировать формулу на блок ячеек Е3:Е10.

3) Подсчитать общую стоимость всех проданных

товаров - å.

9. Контрольные задания

1. Для учёта и анализа сделок некоторой фирмы создать информационную базу данных " Сделки ", содержащую следующую информацию: номер контракта, дата сделки, код изделия, количество приобретенных изделий в партии, стоимость партии.

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

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

1.2. Вывести данные о наиболее и наименее выгодных сделках.

1.3. Сколько по заданному коду изделия заключено контрактов? На какую сумму?

1.4. Вывести итоговые данные по датам сделок, построить диаграмму.

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

При расчётах квартплаты учитывать справочные данные " Прейскурант ": номер комплекса, стоимость кв. м. стоимость коммунальных услуг на 1 человека в месяц.

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

2.2 Сколько людей проживает в заданном комплексе?

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

2.4 Из списка должников выбрать тех владельцев квартир, которые задолжали более чем на заданное число %.

2.5 Сколько задолжников в каждом комплексе? Какова общая сумма их долга?

2.6 Построить диаграмму задолженностей по комплексам.

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

3.1 С учётом справочной информации о процентах на каждый вид вклада рассчитать отдельные суммы по видам вкладов с учетом процентов на заданную дату.

3.2 Какова максимальная сумма по заданному виду вклада?

3.3 Сколько вкладов и какова их общая сумма по каждому виду вклада? Построить график.

3.4 Какой вид вклада наиболее популярен?

3.5 По номеру вклада вывести фамилию вкладчика и сумму его вклада с учетом процентов на заданную дату.

4. Для учёта экспорта товаров за рубеж некоторой фирмой создать информационную базу данных " Экспорт товаров", содержащую следующие сведения: код товара, страна, импортирующая товар, объём поставляемой партии (шт.); объём экспорта в $.

Для расчётов использовать информацию из справочной таблицы " Прейскурант ": код товара, наименование товара, цена товара в $.

4.1 Упорядочить список товаров по коду товара, затем по странам.

4.2 Вывести список экспортируемых товаров с итоговыми показателями экспорта каждого товара, построить диаграмму.

4.3 Выяснить, какой товар пользуется наибольшим спросом за рубежом и в каких странах?

4.4 Вывести список товаров, объём экспорта которых больше среднего.

4.5 Каков общий объём экспорта заданного товара в заданную страну?

4.6 Куда экспортируется самый дешёвый товар?

5. Создать информационную базу об абонентах сотовой связи МТС, содержащую сведения: фамилия абонента, номер телефона, адрес (по комплексу), тариф. При расчётах использовать справочную таблицу оплаты за телефон в месяц, в которой по каждому тарифу установлена своя плата.

5.1 Организовать поиск по вводимой фамилии абонента его номера телефона.

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

5.3 Сколько владельцев заданного тарифа и какую сумму они должны заплатить?

5.4 Вывести итоговый список оплаты за телефон по комплексам проживания абонентов.

5.5 В каком комплексе проживает наибольшее количество абонентов? Построить диаграмму количества абонентов в каждом комплексе.

6. Создать информационную базу данных " Лекарства " на основе следующих сведений: название лекарства, дата изготовления, срок годности, цена, номер аптеки, в которой это лекарство есть. Использовать данные таблицы " Аптеки ": номер аптеки, адрес аптеки, наценка в %.

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

6.2 Удалить из базы данных сведения о лекарствах, срок годности которых истек на текущую дату.

6.3 По запросу "название лекарства" выдать номера и адреса аптек, в которых это лекарство имеется, а также цену лекарства.

6.4 В какой аптеке это лекарство дешевле?

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

7. Создать информационную базу, содержащую сведения об акционерах на основе таблицы " Акционер ": Фамилия И.О., название акционерного общества, количество привилегированных акций, количество обычных акций, дата приобретения акций. При расчётах использовать справочные данные из таблицы " Акции ": название акционерного общества, номинал привилегированной акции, номинал обычной акции, дивиденды по привилегированным акциям (% в год), дивиденды по обычным акциям(% в год).

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

7.2 Каково среднее количество приобретённых акций каждым акционером?

7.3 Вывести итоговые данные по каждому АО. Подсчитать общее количество акций каждого типа, приобретённых акционерами каждого АО.

7.4 Построить диаграмму стоимости акций каждого типа каждого акционера.

7.5 По заданной фамилии акционера и на заданную дату вывести сумму дивидендов по всем его акциям.

8. Создать информационную базу, содержащую сведения о работе АМТС (автоматической междугородней телефонной сети) на основе таблицы " Абонент ": номер телефона, фамилия владельца, адрес, код города, с которым разговаривал абонент, количество минут разговора, стоимость разговора подсчитать с помощью справочной таблицы " Тариф ": код города, цена 1 минуты разговора.

8.1 Упорядочить данные по номерам телефонов.

8.2 Вывести итоговые данные по каждому абоненту с указанием общей стоимости всех его разговоров.

8.3 По заданному номеру телефона выписать счет на оплату телефонного разговора.

8.4 Выяснить, с каким городом чаще всего связываются абоненты АМТС

8.5 Построить диаграмму количества вызовов каждого города.

8.6 Вывести трёх самых разговорчивых (по длительности разговоров) абонентов.

9. Для учёта и анализа работы клиники создать базу данных, содержащую сведения о пациентах клиники на основе таблиц - " Пациент ": фамилия пациента, пол, возраст, место проживания, диагноз, необходимость операции (да/нет) и " Тариф ": диагноз, стоимость терапевтического лечения, стоимость операции.

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

9.2 Подсчитать стоимость лечения каждому пациенту.

9.3 Подсчитать количество иногородних, прибывших в клинику;

9.4 Вывести список пациентов старше заданного возраста с заданным диагнозом.

9.5 Скольким пациентам необходима операция? Какова общая стоимость этих операций?

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

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

10. Создать базу данных, содержащую сведения о работе частного колледжа в таблице " Студент ": Фамилия И.О., курс обучения, успеваемость (да/нет), оплата(руб.), которую они внесли за учёбу, в справочной таблице " Оплата ": курс обучения, стоимость курса обучения и в таблице " Оценки ": Фамилия И.О, оценки за последнюю сессию.

10.1 Вывести в поле БД "успеваемость" сообщение "нет", если результат последней сессии - хотя бы одна двойка, и "да" - иначе. Каков процент успеваемости?

10.2 Ввести в базу данных поле с указанием суммы, которую студенты должны заплатить за обучение. Сколько должников в списке?

10.3 Вывести в отдельный список успевающих студентов-должников и неуспевающих.

10.4 Вывести в отдельный список студентов-отличников и не имеющих долгов по оплате.

10.5 Сформировать итоговую таблицу по курсам обучения, подсчитать общую сумму оплаты обучения студентами каждого курса.

10.6 Построить диаграмму сумму оплаты обучения студентами каждого курса.

11. Для анализа работы отдела телепередач создать информационную базу данных «Телепередачи», содержащую информацию о телепередачах за неделю: название, время выхода в эфир, время в эфире, стоимость одного выхода в эфир. При работе с базой данных применить справочную таблицу " Прейскурант телевизионных передач", содержащий информацию: название, цена одной минуты эфира.

11.1 Выбрать те передачи, стоимость которых на заданное число % больше средней стоимости всех передач за неделю.

11.2 Подсчитать стоимость вечерних передач в выходные дни.

11.3 Указать передачи с наименьшей стоимостью эфира.

11.4 Подсчитать общее количество выходов и стоимость каждой передачи.

11.5 Построить диаграмму количества выходов телепередач за неделю.

12. Для учёта и анализа суммы кредитов, выданных Всемирным банком различным странам, создать базу данных, содержащую сведения: страна, сумма кредита, год выдачи кредита, срок (в годах). Размер годового процента за кредит для каждой страны указан в отдельной таблице. Начисляемые каждый год проценты за кредит добавляются к сумме долга за него.

12.1 Подсчитать сумму возвращаемого долга по каждому кредиту.

12.2 Вывести итоговые данные по странам и подсчитать, сколько кредитов получила каждая страна и на какую общую сумму?

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

12.4 Какая сумма должна быть возвращена Всемирному банку в заданном году?

12.5 Какая страна должна будет вернуть наибольшую сумму через 5 лет?

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

13. Для учёта использования рекламы в радиопередачах создать информационную базу данных " Реклама ", содержащую следующие сведения: название рекламы, название передачи, в которую вставлена реклама, длительность рекламы (в минутах).

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

13.1 Найти среднюю стоимость всех реклам.

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

13.3 Вычислить общее время всех реклам в заданной передаче. В скольких передачах передаётся заданная реклама?

13.4 Вывести итоговые данные по каждой рекламе (в скольких передачах участвует реклама, какова её общая стоимость), построить диаграмму.

14. Для учёта и анализа работы салона проката видеокассет создать базу данных, содержащую сведения в таблице " Кассеты ": наименование фильма, тип видеокассеты, категория фильма (мультики, мелодрама, комедия, эротика, боевики и т.д.), дата выдачи, срок проката. При расчётах стоимости проката пользоваться справочной таблицей " Цены ": тип видеокассеты, цена проката 1 шт. (в сутки).

14.1 Вывести список видеокассет, сгруппированный по датам выдачи.

14.2 Подсчитать, сколько кассет выдавалось ежедневно и какова их общая стоимость проката.

14.3 Построить диаграмму ежедневной выдачи видеокассет.

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

14.5 Вывести на экран хит-парад популярных фильмов за последний месяц.

14.6 Фильмов какой категории оказалось больше?.

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

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

15.2 Удалить из базы данные о "старых" книгах (год издания раньше заданного года).

15.3 Сколько учебников, задачников и справочников отдельно удалено из базы данных?

16. Создать базу данных для справочной службы Аэрофлота, содержащую сведения о наличии билетов и рейсах Аэрофлота на основе следующих таблиц- " Билеты ": номер рейса, пункт назначения, время вылета, время прибытия, количество свободных мест в салоне, стоимость билета (цена + ком. сбор) и " Тариф ": пункт назначения, цена билета. Комиссионный сбор установлен единым на все направления.

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

16.2 Вывести в таблицу данные о тех рейсах, на которые есть места в салоне.

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

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

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

16.6 По заданному пункту назначения вывести все рейсы туда и время вылета каждого рейса, а также цену билета в этот пункт назначения.

17. Для учёта продаж посуды создать базу данных " Посуда ", содержащую следующую информацию: название посуды, дата продажи посуды, количество; выручка от продажи этой посуды.

Для расчётов воспользоваться справочной таблицей " Прейскурант товаров на посуду": название посуды, цена посуды.

17.1. Какая посуда пользуется наибольшей популярностью у покупателей?

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

17.3. Выбрать те товары, по которым выручка больше средней выручки от продажи всех товаров на заданное число %.

17.4. На какую сумму продано посуды после заданной даты?

17.5. Вывести итоговые показатели продажи за день, построить диаграмму.

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

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

18.2. Вывести 3 самых дешёвых наименования детской обуви.

18.3. Вывести наименование и артикул самой дорогой обуви.

18.4. Подсчитать общее количество женских туфель.

18.5. Удалить из базы данных сведения о полностью проданной партии. На какую сумму продано обуви?

19. Для анализа игры двух команд собрать информацию о них в базу данных, которая содержала бы неупорядоченные сведения о двенадцати игроках хоккейных команд " Ак Барс " и " ЦСКА ": название команды, номер игрока, фамилия, амплуа, число заброшенных им шайб, сделанных голевых передач, заработанное штрафное время, сумму очков (голы + передача). В справочной таблице содержатся сведения об игроках обеих команд: амплуа, зарплата в месяц.

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

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

19.3. Вывести рейтинг лучших игроков каждой команды с указанием их фамилий.

19.4. Сколько всего заброшено шайб только нападающими и "Ак Барса", и "ЦСКА"?

19.6. По заданному номеру игрока вывести все данные об его игре, а также фамилию и имя.

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

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

20.2 Упорядочить список партий по количеству процентов. Построить диаграмму.

20.3 Вывести в отдельный список те партии или блоки, которые преодолели 10-процентный барьер.

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

20.5 Вывести название партии, потратившей максимальную сумму, и место, которое она заняла в окончательном (упорядоченном) списке.

20.6 Сколько потратила на избирательную компанию победившая партия?

20.7 Какова общая сумма, истраченная партиями, преодолевшими 10-процентный барьер.

21. Создать базу данных, содержащую сведения об отправлении поездов дальнего следования с Казанского вокзала на основе таблиц " Поезд ": номер поезда, станция назначения, время отправления, время в пути, количество свободных мест, стоимость билета (цена + ком. сбор) и " Тариф ": станция назначения, цена билета, комиссионный сбор (%) в зависимости от направления.

21.1 Упорядочить данные по станции назначения, затем по номерам поездов.

21.2 Подсчитать количество свободных мест по каждой станции назначения.

21.3 Построить диаграмму количества билетов на поезда.

21.4 Вывести время отправления поездов в заданный город в заданном временном интервале; подсчитать общее количество билетов на эти поезда;

21.5 Сколько имеется билетов на поезд с заданным номером и цена билета на этот поезд;

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

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

22. Создать базу данных на основе таблицы " Багаж ", содержащую сведения о багаже нескольких пассажиров: номер билета, номер рейса, количество вещей, общий вес вещей, а также таблицы «Доплата»: номер рейса, стоимость доплаты за 1 кг превышения заданного предельного значения веса багажа.

22.1 Упорядочить данные по номеру рейса, затем по номерам билетов.

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

22.3 Вычислить общий вес вещей пассажиров, багаж которых больше заданного предельного значения веса багажа.

22.4 Каков процент от общего веса составляет этот вес?

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

22.6 Сколько пассажиров доплатили за багаж?

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

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

23.2 Подсчитать заработок каждого сборщика за неделю и общую сумму заработка всех.

23.3 Вывести список передовых рабочих, собравших общее количество изделий больше среднего.

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

23.5 Выделить на экране фамилию самого отстающего сборщика.

23.6 Сколько всего собрано изделий каждого наименования?

24. Для учёта работы сборщиков некоторого завода создать базу данных «Сборка», содержащую сведения о количестве изделий категорий А, В, С, собранных рабочими за месяц: фамилия сборщика, наименование цеха, количество изделий по категориям, собранных рабочим за месяц.

Дана таблица расценок за выполненную работу по сборке единицы изделия категорий А,В,С соответственно. Ответить на следующие вопросы:

24.1 Каково общее количество изделий каждой категории, собранных всеми сборщиками, каких изделий собрано больше? Проиллюстрировать диаграммой.

24.2 Напечатать ведомость заработной платы всех рабочих (упорядоченную по цехам и по фамилиям);

24.3 Сколько сборщиков работало в каждом цехе? Какую общую сумму зарплаты нужно им выдать?

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

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

24.6 Сколько собрано изделий категории А в заданном цехе?

25. Создать таблицу расчёта зарплаты рабочих завода. Таблица «Зарплата» содержит следующие сведения: наименование цеха, фамилия рабочего, специальность, количество отработанных им дней за месяц, начисление зарплаты за месяц, удержания, сумма к выдаче.

При расчёте воспользоваться таблицей " Расценки ", где имеются сведения: специальность, цена 1 рабочего дня;

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

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

25.3 В каком цехе наибольшие выплаты? Подсчитать количество рабочих этого цеха.

25.4 Напечатать для бухгалтерии ведомость (упорядоченную по алфавиту) для выдачи заработной платы рабочим заданного цеха.

26. Для учёта и анализа работы продавцов магазина создать информационную базу данных " Продавец ", содержащую информацию о работе каждого продавца магазина: фамилия продавца, код проданного товара, количество проданных единиц товара, выручка от продажи товара.

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

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

26.2 Отметить на экране трёх лучших продавцов и вывести список трёх самых отстающих.

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

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

27. Создать базу данных, содержащую сведения о почтовых посылках на основе таблицы " Посылка ": вид посылки (авиа, обычная, бандероль, заказное письмо), вес, пункт назначения, стоимость пересылки. При расчёте стоимости пересылки использовать справочные данные из таблицы " Тариф ": пункт назначения, цена 1 кг веса, страховой сбор(%).

27.1 Упорядочить список посылок по пунктам назначения, затем по видам посылок.

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

27.3 В какой пункт нужно отправить наибольшее количество авиа посылок и их общий вес?

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

27.5 Какова средняя стоимость обычной посылки? В какие пункты назначения отправлены обычные посылки выше средней стоимости?

27.6 Вывести список пяти самых дорогих бандеролей, куда они отправлены?

28. Создать базу данных, содержащую сведения о работе продовольственной базы на основе таблицы " Товары ": наименование товара, вес партии, пункт назначения (номер магазина), дата отправления, стоимость партии. Стоимость товара подсчитать с помощью справочной таблицы " Цены ": наименование товара, цена 1 кг.

28.1 Упорядочить данные по номерам магазинов.

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

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

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

28.5 Куда отправлены три наибольшие партии заданного товара?

29. Для учёта и анализа работы некоторого магазина создать информационную базу " Продажа товаров ", содержащую информацию: код товара, дата продажи, количество проданных единиц, выручка от продажи товара. Для расчётов использовать справочную таблицу " Прейскурант ", содержащую информацию: код товара, название товара, цену единицы товара.

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

29.2 Выбрать те товары, которые были проданы после данного числа на сумму, превышающую среднюю выручку на заданное число %.

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

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

30.1 Подсчитать стоимость каждой услуги каждому клиенту.

30.2 Сколько клиентов обратилось в автосалон? На какую сумму обошёлся ремонт каждому клиенту?

30.3 Построить диаграмму стоимости ремонта каждого клиента.

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

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

30.6 Сколько услуг оказывалось автомобилям каждой марки, на какую сумму? Автомобили какой марки чаще ремонтируются?

 

Литература

1. Microsoft Excel 2000. Шаг за шагом: Практ. пособ. / Пер. с англ.- М.: Издательство ЭКОМ, 2000.- 472с.

2. Додж М. Эффективная работа с Microsoft Excel 97: Пер. с англ./ - СПб: Питер,1998. - 1072 с.

3. Долженков В.А. Microsoft Excel 2000 / СПб: БХВ - Санкт-Петербург,1999.- 1088 с.

4. Лавренев С.М. Excel: Сборник примеров и задач. - М.: Финансы и статистика, 2000. - 336 с.

5. Гончаров А. Excel 97 в примерах - СПб: Питер, 1997. - 336 с.

6. Райнер Гебхардт. Excel 97. Справочник / Пер. с нем. - М.: "Издательство БИНОМ", 1998. - 464 с.

 

Оглавление

Стр.

1. Консолидация. 3

2. Сортировка данных. 10

3. Транспонирование таблицы.. 14

4. Закрепление строк и столбцов. 14

5. Итоги. 15

6. Сводные таблицы.. 18

7. Базы данных. 23

7.1. Создание базы данных. 24

7.2. Поиск записей по критериям в форме. 27

7.3. Фильтрация данных. 28

7.3.1. Автофильтр. 29

7.3.2. Расширенный фильтр. 33

7.3.3. Функции базы данных.. 38

8. Функции поиска и связи таблиц. 41

9. Контрольные задания. 44

 

[1] Заглавие таблицы и имена столбцов.