ЗАДАНИЯ К ЛАБОРАТОРНОЙ РАБОТЕ 1 страница

Раздел. «ЭЛЕКТРОННЫЕ ТАБЛИЦЫ

MICROSOFT EXCEL»

Донецк-2008

Задания к лабораторным работам с методическими указаниями. Раздел. «Электронные таблицы Microsoft Excel» / Сост.: С.В. Вакуленко, М.Р. Терованесов, Е.И. Демидова, Е.А. Литвинова.

В данном методическом пособии представлены задания к лабораторным работам по направлению «электронные таблицы Microsoft Excel» с методическими указаниями и примерами решения подобных заданий. В состав пособия входят девять лабораторных работ, которые охватывают все основные элементы электронных таблиц Microsoft Excel. Задания составлены с учетом практического применения в области бухгалтерии, финансового анализа и банковского дела.

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

© Донецкий институт железнодорожного

транспорта, 2008

© Вакуленко С.В., Терованесов М.Р.,

Демидова Е.И., Литвинова Е.А.

СОДЕРЖАНИЕ

ЛАБОРАТОРНАЯ РАБОТА № 1
Тема: «Обработка данных с использованием таблицы - справочника». 4

ЛАБОРАТОРНАЯ РАБОТА № 2
Тема: «Расчет значений функций на заданном интервале». 38

ЛАБОРАТОРНАЯ РАБОТА № 3
Тема: «Работа с датами. Использование фильтра». 51

ЛАБОРАТОРНАЯ РАБОТА № 4
Тема: «Гиперссылка как способ передвижения». 64

ЛАБОРАТОРНАЯ РАБОТА № 5
Тема: «Использование элементов управления в списках». 82

ЛАБОРАТОРНАЯ РАБОТА № 6
Тема: «Использование элементов управления в списках». 82

ЛАБОРАТОРНАЯ РАБОТА №7
Тема: «Макросы, элементы Visual Basic». 82

ЛАБОРАТОРНАЯ РАБОТА №8
Тема: «Использование элементов управления. Финансовые функции». 82

ЛАБОРАТОРНАЯ РАБОТА № 9
Тема: «Регрессионный анализ в Excel». 82

ЛАБОРАТОРНАЯ РАБОТА № 1
Тема: «Обработка данных с использованием таблицы - справочника»

Цель: Приобретение привычек работы с функциями ПРОСМОТР и ПРОМЕЖУТОЧНЫЕ ИТОГИ

Объяснение к выполнению лабораторной работы

1. Исходные таблицы создать на первом листе.

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

3. Результаты расчетов помещать на второй лист, а при необходимости и на третий.

4. Диаграмму построить на отдельном листе.

Теоретическая часть

Описание некоторых функций, задействованных в работе

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;диапазон_1;…)

Возвращает промежуточные итоги в списке или базе данных. Номер функции – число от 1 до 11, указывает функцию. (1 – СРЗНАЧ, 2 – СЧЕТ, 6- ПРОИЗВЕД, 9 – СУММ). Диапазон_1 задает диапазон, над которым применяется указанная функция.

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

Суммирование чисел, заданные определенным критерием. Диапазон – диапазон, ячеек для проверки Критерия, Диапазон_Суммирования – диапазон ячеек для суммирования. Если Диапазон_Суммирования не задан суммируются ячейки из Диапазон.

СЧЕТСЛИ(диапазон, критерий)

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

ПРОСМОТР(искомое_значение; массив)

Искомое_значение - значение, которое ПРОСМОТР ищет в массиве. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.

Пример решения задания

Задание

В таблице 1 приведенная себестоимость выпуска 1 пары обуви того или другого артикула. В таблице 2 приведенные данные о выпуске обуви равноценными бригадами. Заполнить таблицы, поместив в первую из них 7, а в друге - 14 записей.

Таблица 1

Таблица 2

С помощью электронных таблиц Excel вычислить:

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

2. Сумму наценки, которая составляет 25% от себестоимости обуви каждого артикула, выпущенного каждой бригадой.

3. Цену обуви каждого артикула, выпущенного каждой бригадой, включая наценку.

4. Сумму, на которую выпущена обувь каждой бригадой.

5. Сумму, на которую выпущена обувь каждого артикула.

6. Общую сумму за всю выпущенную обувь.

7. Количество видов обуви с себестоимостью выпуска 1 пары меньшей, чем 30 грн.

8. Среднюю себестоимость выпуска 1 пары обуви.

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

10. На отдельном письме составить отчет о выпуске обуви разными бригадами, в который поместить: артикул, бригада, цена выпущенной обуви.

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

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

Отформатировать отчет в таком виде:

Ø шапку таблицы выделить более жирной рамкой и более большим шрифтом;

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

Ход решения задания

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

I. Для вычисления себестоимости необходимо перемножить количество "Выпущено" на "Себестоимость выпуска 1 пары". Можно перемножать почленно, а можно воспользоваться функцией ВПР, т.е. в ячейку F14 необходимо ввести

=ВПР(A14;$A$3:$B$9;2)*E14 è растянуть ее вниз на всю таблицу

II. Для вычисления наценки необходимо "Стоимость" умножить на 25%, т.е. в ячейку G14 необходимо ввести

=F14*25% è растянуть ее вниз на всю таблицу

III. Для вычисления стоимости с наценки необходимо "Стоимость" сложить с "Наценкой", т.е. в ячейку H14 необходимо ввести

=F14+G14 è растянуть ее вниз на всю таблицу

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

=СУММЕСЛИ($D$14:$D$27;A32;$H$14:$H$27) è растянуть ее вниз

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

=СУММЕСЛИ($A$14:$A$27;A3;$H$14:$H$27) è растянуть ее вниз

VI. Необходимо воспользоваться кнопкой автосуммирования для столбца H, т.е. просуммировать диапазон (H14:H27)

VII. Необходимо воспользоваться функцией СЧЁТЕСЛИ, т.е. в ячейку Е37 необходимо ввести

=СЧЁТЕСЛИ(B3:B9;"<30")

VIII. Необходимо воспользоваться функцией СРЗНАЧ, т.е. в ячейку Е38 необходимо ввести

=СРЗНАЧ(B3:B9)

IX. Для построения диаграммы необходимо выделить диапазон ячеек А2:А9 и С2:С9 и запусти мастер диаграмм (Вставка è Диаграмма)

X. Необходимо

1. Скопировать столбцы: артикул, бригада, Стоимость обуви с наценкой на новый лист.

2. Отсортировать: Выделить скопированные данные è Данные è Сортировка è Установить сортировку по полю Бригада, затем по полю Артикул è Оk

3. Создать итоги: Выделить скопированные данные è Данные è Итоги èзадать параметры

a) при каждом изменении в: выбрать поле Бригада;

b) добавить итоги по:выбрать поле Стоимость обуви с наценкой

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

ЗАДАНИЯ К ЛАБОРАТОРНОЙ РАБОТЕ

Вариант 1

В таблице 1 приведенная цена того или другого товара. В таблице 2 приведенные данные о покупках товаров в магазине с несколькими равноценными отделами. Заполнить таблицы, поместив в первую из них 9, а в друге - 15 записей.

Таблица 1

Таблица 2

С помощью электронных таблиц Excel вычислить:

1. Стоимость каждой покупки.

2. Сумму налога по каждой покупке, которая составляет 20% от стоимости.

3. Стоимость каждой покупки за исключением налога.

4. Выручка каждого отдела.

5. Выручка по каждому наименованию товара.

6. Общая выручка за все проданные товары.

7. Количество наименований товара с ценой 1 единице большей, чем 10 грн.

8. Максимальное количество единиц товара, проданного в одни руки.

9. Построить диаграмму выручки в зависимости от наименования товара.

10. На отдельном листе составить отчет о покупках, в котором поместить: наименование товара, номер отдела, стоимость покупки.

Привести в порядок отчет по отделам, а внутри каждого отдела привести в порядок покупки по наименованию товара.

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

Отформатировать отчет в таком виде:

Ø шапку таблицы выделить более жирной рамкой и более большим шрифтом;

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

Вариант 2

В таблице 1 приведенная тарифная стоимость 1 минуты записи на том или ином типе кассет. В таблице 2 приведенные данные о записях альбомов на разных типах кассет. Заполнить таблицы, поместив в первую из них 7, а в друге - 14 записей.

Таблица 1

Таблица 2

С помощью электронных таблиц Excel вычислить:

1. Стоимость записи каждого альбома по тарифу.

2. Сумму НДС, что составляет 20% от стоимости записи по тарифу.

3. Цену альбома, включая НДС.

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

5. Среднюю стоимость записанного альбома по каждому типу кассет.

6. Общая выручка за все альбомы.

7. Количество альбомов с числом копий меньшим, чем 10.

8. Максимальное время звучания одного альбома.

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

10. На отдельном листе составить отчет обо всех записях, в котором поместить: исполнителя, название альбома, число копий, расхода на запись.

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

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

Отформатировать отчет таким образом:

Ø шапку таблицы набрать более жирным и более большим шрифтом;

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

Вариант 3

В таблице 1 приведенная стоимость 1 билета в зависимости от аэропорта назначения. В таблице 2 приведенные данные о продаже билетов на самолеты. Заполнить таблицы, поместив в первую из них 8, а в друге - 13 записей.

Таблица 1

Таблица 2

С помощью электронных таблиц Excel вычислить:

1. Стоимость проданных билетов на каждый рейс.

2. Стоимость непроданных билетов на каждый рейс.

3. Процент проданных билетов на каждый рейс.

4. Количество свободных мест на каждый рейс.

5. Среднюю заполняемость самолетов по аэропортам назначения.

6. Общая выручка за все проданные билеты.

7. Количество рейсов, заполненных на 100%.

8. Минимальное количество билетов, проданных на 1 рейс.

9. Построить диаграмму средней заполняемости самолета в зависимости от аэропорта назначения.

10. На отдельном листе составить ведомость о продаже билетов на самолеты, в которую поместить: номер рейса, аэропорт назначения, свободные места, выручка за проданные билеты.

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

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

Отформатировать ведомость в таком виде:

Ø шапку таблицы выделить более жирной рамкой и более большим шрифтом;

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

Вариант 4

В таблице 1 приведенная тарифная стоимость 1 минуты разговора с тем или другим городом. В таблице 2 приведенные данные о междугородных разговорах абонентов. Заполнить таблицы, поместив в первую из них 9, а в друге - 14 записей.

Таблица 1

Таблица 2

С помощью электронных таблиц Excel вычислить:

1. Стоимость каждого разговора по тарифу.

2. Сумму НДС, что составляет 20% от стоимости разговора по тарифу.

3. Стоимость каждого разговора, включая НДС.

4. Сумму к оплате для каждого абонента.

5. Среднюю продолжительность разговора с каждым городом.

6. Общую стоимость всех переговоров.

7. Количество разговоров, менее коротких, чем 5 минут.

8. Максимальную продолжительность разговора.

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

10. На отдельном листе составить ведомость по всем разговорам, в которую поместить: ПОП абонента, номер телефона, дата разговора, сумма к оплате за разговор.

Привести в порядок ведомость по абонентам, а для каждого абонента записи привести в порядок по дате разговора.

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

Отформатировать ведомость в таком виде:

Ø шапку таблицы набрать более жирным шрифтом, использовать курсив;

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

Вариант 5

В таблице 1 приведенная цена того или другого прибора. В таблице 2 приведенные данные о выпуске приборов на заводе с несколькими равноценными цехами. Заполнить таблицы, поместив в первую из них 8, а в друге - 13 записей.

Таблица 1

Таблица 2

С помощью электронных таблиц Excel вычислить:

1. Стоимость приборов, выпущенных каждым мастером.

2. Количество приборов, выпущенных каждым цехом.

3. Среднюю стоимость приборов, выпущенных мастерами каждого цеха.

4. Стоимость приборов, выпущенных каждым цехом.

5. Стоимость выпущенных приборов каждого наименования.

6. Общую стоимость всех выпущенных приборов.

7. Количество мастеров, которые выпустили не менее 10 приборов одного наименования.

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

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

10. На отдельном листе составить отчет о выпуске приборов мастерами завода, в который поместить: ПОП мастера, наименование прибора, номер цеха, стоимость выпущенных приборов.

Привести в порядок отчет по цехам, а внутри каждого цеха привести в порядок записи от ПОП мастера.

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

Отформатировать отчет в таком виде:

Ø шапку таблицы выделить более жирной рамкой и более большим шрифтом;

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

Вариант 6

В таблице 1 приведенная себестоимость 1 единице той или другой продукции. В таблице 2 приведенные данные о выпуске продукции однотипными бригадами. Заполнить таблицы, поместив в первую из них 8, а в друге - 12 записей.

Таблица 1

Таблица 2

С помощью электронных таблиц Excel вычислить:

1. Себестоимость продукции каждого вида, выпущенной каждой бригадой.

2. Сумму НДС, что составляет 20% от себестоимости выпущенной продукции.

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

4. Сумму, на которую выпущенная продукция каждой бригадой.

5. Сумму, на которую выпущенная продукция каждого наименования.

6. Общую сумму за всю выпущенную продукцию.

7. Количество наименований продукции с суммой выпуска > 100 грн..

8. Максимальное количество одновременно выпущенной продукции одного наименования.

9. Построить диаграмму суммы, на которую выпущено продукции, в зависимости от наименования.

10. На отдельном листе составить отчет о выпуске продукции, в который поместить: наименование продукции, бригада, стоимость продукции.

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

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

Отформатировать отчет в таком виде:

Ø шапку таблицы набрать более жирным и более большим шрифтом;

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

Вариант 7

В таблице 1 приведено время, необходимое для проверки того или другого прибора. В таблице 2 приведенные данные о проверке приборов мастерами ВТК. Заполнить таблицы, поместив в первую из них 7, а в друге - 13 записей.

Таблица 1

Таблица 2

С помощью электронных таблиц Excel вычислить:

1. Общее время проверки каждым мастером каждого вида приборов.

2. Сумму, заработанную каждым мастером за проверку каждого вида приборов.

3. Общую сумму, заработанную каждым мастером.

4. Общее время проверки приборов каждым мастером отдельно.

5. Общее время проверки приборов одного наименования.

6. Общую сумму, заработанную всеми мастерами за проверку всех приборов.

7. Количество видов приборов, время проверки единицы которого меньше, чем 20 минут.

8. Максимальное количество приборов одного вида, проверенных одним мастером.

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

10. На отдельном листе составить отчет о проверке приборов мастерами ВТК, в который поместить: Ф.И.О. мастера, название прибора, сумму, заработанную за проверку приборов.

Привести в порядок отчет по Ф.И.О. мастера, а у каждого мастера по названию приборов.

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

Отформатировать отчет в таком виде:

Ø шапку таблицы выделить более жирной рамкой и более большим шрифтом;

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

Вариант 8

В таблице 1 приведенная оплата за выпуск 1 детали того или другого вида. В таблице 2 приведенные данные о выпуске деталей разными рабочими. Заполнить таблицы, поместив в первую из них 8, а в друге - 15 записей.

Таблица 1

Таблица 2

С помощью электронных таблиц Excel вычислить:

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

2. Сумму штрафа по каждому виду деталей каждому мастеру.

3. Сумму к оплате каждому рабочему по каждому виду деталей за исключением штрафа.

4. Общую сумму к оплате по каждому рабочему.

5. Общее количество качественных деталей каждого наименования.

6. Общую сумму к оплате за все выпущенные детали.

7. Количество видов деталей со штрафом за 1 бракованную деталь большим, чем 50 копеек.

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

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

10. На отдельном листе составить отчет о выпуске деталей рабочими, в который поместить: Ф.И.О. рабочего, название детали, количество качественных деталей, сумма к оплате за исключением штрафа.

Привести в порядок отчет по Ф.И.О. рабочего, а для каждого рабочего записи привести в порядок по названию детали.

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

Отформатировать отчет в таком виде:

Ø шапку таблицы набрать более жирным шрифтом, использовать курсив;

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

Вариант 9

В таблице 1 приведенная стоимость выпуска того или другого изделия. В таблице 2 приведенные данные о выпуске изделий несколькими равноценными цехами. Заполнить таблицы, поместив в первую из них 9, а в друге - 14 записей.

Таблица 1

Таблица 2

С помощью электронных таблиц Excel вычислить:

1. Стоимость выпуска каждого вида изделий каждым рабочим.

2. Сумму налога по разным изделиям каждого рабочего, который составляет 20% от стоимости выпуска.

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

4. Цену изделий, выпущенных каждым цехом.

5. Сумму, на которую выпущено изделий каждого наименования.

6. Общую цену за все выпущенные изделия.

7. Количество рабочих, которые выпустили больше, чем 20 изделий одного вида.

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

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

10. На отдельном листе составить отчет о выпуске изделий рабочими, в который поместить: название изделия, Ф.И.О. рабочего, номер цеха, цена выпущенных изделий.

Привести в порядок отчет по цехам, а внутри каждого цеха привести в порядок записи по Ф.И.О. рабочего.

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

Отформатировать отчет в таком виде:

Ø шапку таблицы выделить более жирной рамкой и более большим шрифтом;

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

Вариант 10

В таблице 1 приведенные расхода материала на выпуск 1 детали того или другого вида. В таблице 2 приведенные данные о выпуске деталей равноценными бригадами. Заполнить таблицы, поместив в первую из них 8, а в друге - 13 записей.

Таблица 1

Таблица 2

С помощью электронных таблиц Excel вычислить:

1. Расхода материала на выпуск каждого вида деталей каждым рабочим.

2. Количество материала, израсходованного на выпуск бракованных изделий каждого вида каждым мастером.

3. Количество материала, израсходованного на выпуск качественных изделий каждого вида каждым рабочим.

4. Расхода материала на выпуск деталей по каждой бригаде.

Дата добавления: 2015-07-26; просмотров: 237; Нарушение авторских прав

Мы поможем в написании ваших работ!