Лабораторная работа 6

Тема: Подведение итогов и отчетов по данным в ЭТ MS Excel

Цели работы: Освоить применение основных команд по подведению итогов и составлению отчетов при работе со списком в MS Excel’2000/2003

Содержание работы:

1Подведения итогов в списке, используя несколько способов MS Excel’2000/2003.

2Группировка и структура данных в списке.

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

Технология выполнение работы:

Задание 1. Автоматическое подведение итогов

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

1Откройте новую книгу и скопируйте таблицу с листа «Сортировка» из предыдущей работы на лист новой книги, назовите «Итоги».

2Сначала нужно упорядочить таблицу по полю Организация, второй уровень сортировки – Дата.

3Выполнить команду Данные/Итоги. В появившемся окне в первой строке выбрать Организация, в строке Операция из списка выбрать Сумма, в третьей строке выбрать поля, по которым подводить итоги, Дебет и Кредит. Посмотреть другие параметры. Нажать ОК.

4Просмотреть результаты. В левой половине экрана появились символы структуры (Значки «+» и «-»). Ознакомьтесь с их назначением.

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

6Сохранить под именем «Подведение итогов».

Задание 2 Консолидация данных (способ получения итоговой информации из разных листов, одинаковых по структуре).

1Предположим, есть три таблицы одинаковой структуры УЧЕТ ПРОДАЖИ ТОВАРА одной фирмы, имеющей три склада в разных точках города. Создадим эти таблицы. Скопируйте таблицу с листа Сортировка на новые листы, назовите их «склад1», «склад2», «склад3».

2Измените некоторые данные на этих листах, например, для организации АОЗТ «Белокуриха» для товара хлеб Количество1, Количество2 или Цену.

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

4Пусть необходимо подвести итоги о продаже хлеба организации АОЗТ «Белокуриха» в сумме в этих трех точках.

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

6Дать следующему листу имя Консолидация. Находясь на этом листе, выполнить команду Данные/Консолидация.

7В появившемся диалоговом окне выбрать функцию Сумма.

8Щелкнуть мышью в поле Ссылки, перейти на лист Склад1 и выделить итоговую сумму продажи хлеба АОЗТ«Белокуриха». Данные появятся в поле ссылки. Нажать кнопку Добавить.

9То же самое выполнить для листов Склад2, Склад3.

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

11Сохранить изменения.

Задание 3. Сводные таблицы

Команда Данные/ Сводная таблица вызывает Мастера сводных таблиц для построения сводов, т.е. итогов определенных видов на основных списков данных.

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

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

2Выполнить команду Данные/Сводная таблица.

3Выполнить первые два шага с Мастером самостоятельно.

4На 3 шаге выбрать Новый лист, Макет.., в появившемся диалоговом окне (рисунок 13.1) перетащить значки с названиями столбцов нашей таблицы следующем образом:

- информацию, которую мы хотим разместить в строках – это Организации; для этого схватить мышью значок из правого поля «Организация» и перетащить в область Строка;

- информацию, которую мы хотим разместить в столбцах – это вид товара; для этого схватить мышью значок из правого поля «Товар» и перетащить в область Столбец;

- перенести значок Дебет в область Данные.

Рисунок 13.1

5Если выполнить двойной щелчок на перенесенных значках, можно отредактировать их назначение. На значках, помещенных в окне Строка, активизировано состояние строки, в окне Столбец – столбца. Двойной щелчок на значке в области данных позволяет выполнить операцию.

6Просмотреть полученную таблицу и сравнить ее с рисунком 13.2.

Рисунок 13.2

Редактирование и изменения макета Сводной таблицы

1Скопируйте данную сводную таблицу на новый лист.

2Для редактирования данных в сводных таблицах удобно использовать панель инструментов Сводные таблицы (Вид/панель инструментов/Сводные таблицы), представленную на рисунке 13.3.

Рисунок 13.3 - Панель инструментов «Сводные таблицы»

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

3Изменить макет таблицы. Информацию, которую мы хотим разместить в строках – это Организации и Товар. Для этого необходимо кнопку «Товар» перенести в стоку:

1 способ: схватить ЛКМ ячейку «Товар» и перетащить в строку где стоит Организация.

2 способ: выполнить команду Сводная таблица на панели инструментов и выбрать меню Мастер сводных таблиц/Макет и перетащить кнопку Товар в строку.

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

Рисунок 13.4

4Если мы хотим посмотреть реализацию определенного вида товара, например, апельсин, то необходимо открыть список около кнопки Товар, убрать галочку Показать все и поставить галочку напротив апельсин, нажать ОК (см. пример на рисунке 13.5).

Рисунок 13.5

5Создать Сводную таблицу закупки товара по организации АОЗТ "Белокуриха", используя список около кнопки Организация.

6Создайте на новом листе Сводную таблицу по продаже всего товара для каждой организации: в строке – Товар, в столбце – Организация, данные – Сумма по кредиту.

7Приведите последнюю сводную таблицу к виду, представленному на рисунке 13.6.

Рисунок 13.6

8Предположим, нам необходима сводная таблица о продажах всех организаций по месяцам. Изменим сводную таблицу. Установить значок Организация в область строк, а значок Дата в область Столбец. В поле Данные поставить значок Кредит.

9Просмотреть полученную таблицу.

10Добавьте к полю Организация еще поле Товар и установите таблицу на новом листе.

Фильтрация сведенных данных:

1Откройте последнюю сводную таблицу.

2Перетащить в таблице поле Организация в ячейку А1.

3Щелкнуть по язычку Все и выбрать интересующую вас организацию, например к/з "Восток" (см. рисунок 13.7).

Рисунок 13.7

Структурирование сведенных данных

1Сгруппируем данные из последней таблицы задания 3.2. по месяцам. Для этого установить курсор на ячейку Дата и выбрать пункт меню Данные/Группа и структура/ Группировать по месяцам.

2Сгруппируйте данные по кварталу.

3Сгруппируйте данные по кварталу и месяцам.

4Отобразите данные только за 1 квартал.

5Постройте диаграмму по последней сводной таблице: нажмите кнопку на панели инструментов Сводных таблиц Мастер диаграмм (см. рисунок 13.8) и диаграмма готова на новом листе.

Рисунок 13.8

6Сохраните изменения.

Варианты заданий по теме MS Excel 2000/2003

Вариант №1

1Торговая фирма «Альфа» имеет в своем ассортименте следующий товар: телевизоры, видеомагнитофоны, музыкальные центры, видеокамеры, видеоплееры, аудиоплееры.

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

- сумму выручки от продаж каждого вида товара в долларах и рублях за каждый месяц;

- сумму выручки за каждый квартал;

- максимальный, минимальный и средний размер выручки в году.

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

2На торговом складе фирмы «Альфа» производится уценка хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев (300 дней), то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев (180 дней), но не достиг 10 месяцев, то — в 1,5 раза.

Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, дата приема, дата продажи, срок хранения (дата продажи- дата приема), цена 1 ед.товара до уценки, цена 1 ед. товара после уценки.

Для ячеек столбца «наименование товара» назначить следующий формат: если товар уценен в 2 раза, то текст отобразить синим цветом, если в 1,5 раза, то зеленым.

3Построить в одной системе координат при графики функций:

а) , б) , в)

4Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»;

б)Заполнить новые столбцы, используя форму;

в)Добавить столбец «Продано на сумму» и сделать расчет;

г)Осуществить сортировку по полям: «Модель HDD», «Ёмкость»;

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих шину SCSI, скорость вращения больше 3600 об/мин и время >10 мс;

е)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя РАСШИРИННЫЙ ФИЛЬТ, у которых модель HDD Seagate и емкость ³ 200 Мб;

ж)Скопировать отсортированную таблицу на новый лист и подвести общие итоги для каждой модели HDD по количеству проданного товара;

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

Вариант №2

1Продукцией городского молочного завода являются: молоко, кефир и сметана. На производство 1 тонны молока, кефира и сметаны требуется соответственно 1010, 1020 и 9450 литров молока. Прибыль от реализации 1 тонны молока, кефира и сметаны соответственно равны: 300, 220 и 1360 рублей. Было изготовлено молока 123 тонны, кефира 342 тонны, сметаны 256 тонн. Требуется рассчитать:

- прибыль от реализации каждого вида изделий, общую прибыль;

- долю (в процентах) прибыльности каждого вида изделий от общей суммы;

- найти максимальный, минимальный и средний объем расхода молока.

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

2При температуре воздуха зимой до -20°С потребление угля тепловой станцией составляет N тонн в день. При температуре воздуха от -30°С до -20°С дневное потребление увеличивается на 5 тонн, если температура воздуха ниже -30°С, то потребление увеличивается еще на 7 тонн. Составить ведомость потребления угля тепловой станцией за неделю.

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

3Построить в одной системе координат при графики функций:

а) , б) , в)

4Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»;

б)Заполнить новые столбцы, используя форму;

в)Добавить столбец «Продано на сумму» и сделать расчет;

г)Осуществить сортировку по полям: «Модель HDD», «Шина»;

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих шину AT, скорость вращения 3600 об/мин и время доступа 25 мс;

е)Скопировать таблицу на новый лист и осуществить поиск винчестеров, используя РАСШИРИННЫЙ ФИЛЬТ, у которых шина SCSI и ёмкость меньше 150 Мб;

ж)Скопировать отсортированную таблицу на новый лист и подвести общие итоги для каждой модели HDD по сумме, на которую было продано товара;

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

Вариант №3

1Сопоставьте доходность акций по уровню дивидендов за 1999 год по отдельным банкам. Заполнить таблицу не менее 5 записей.

где NA – номинал акции; CP – цена продажи; Div – дивиденды, объявленные в расчете на год; DivR=NA*Div; DN=Div; DF=DivR/CP. Требуется рассчитать:

­ среднюю цену продажи акций по всем банкам;

­ максимальную цену продажи акций по всем банкам, минимальная цена продажи акций по всем банкам;

­ минимальную фактическую доходность акций по уровню дивидендов.

Построить диаграмму по данным столбца «Фактическая DF».

2Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: k₁ рублей за 1 кВт/ч за первые 500 кВт/ч; k₂ рублей за 1 кВт/ч, если потребление свыше 500 кВт/ч, но не превышает 1000 кВт/ч; k₃ рублей за 1 кВт/ч, если потреб­ление свыше 1000 кВт/ч. Составить ведомость оплаты за электроэнергию каждым клиентом.

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

3Построить в одной системе координат при графики функций:

а) , б) , в)

4Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»;

б)Заполнить новые столбцы, используя форму;

в)Добавить столбец «Продано на сумму» и сделать расчет;

г)Осуществить сортировку по полям: «Скорость передач», «Модель HDD»;

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих шину SCSI, скорость передачи >300 и <2000;

е)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя РАСШИРИННЫЙ ФИЛЬТ, у которых модель HDD Seagate и скорость вращения <3500 об/мин;

ж)Скопировать отсортированную таблицу на новый лист и подвести общие итоги для каждой модели HDD по количеству проданного товара;

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

Вариант №4

1На книжную базу поступили три наименования книг: словари, книги по кулинарии и пособие по вязанию. Они были распределены по трем магазинам: «Книжный мир»: 10400 экземпляров словарей, кулинарных книг – 23650 экз., пособий по вязанию 1500 экз.; «Дом книги»: 10300 экземпляров словарей, кулинарных книг – 22950 экз., пособий по вязанию1990 экз., «Глобус»: 9100 экземпляров словарей, кулинарных книг –23320 экз., пособий по вязанию 2500 экз.

В первом магазине было продано словарей – 8945 экз по цене за 1ед 350 руб, кулинарных книг – 19865 экз. по цене за 1ед 256 руб, пособий по вязанию 843 экз. по цене за 1ед 245 руб; во втором магазине: экземпляров словарей- 9300 по цене за 1ед 360 руб, кулинарных книг – 21900 экз. по цене за 1ед 246 руб, пособий по вязанию 1020 экз по цене за 1ед 195 руб; во третьем магазине: экземпляров словарей- 1200 по цене за 1ед 385 руб, кулинарных книг – 22900 экз. по цене за 1ед 230 руб, пособий по вязанию 1120 экз по цене за 1ед 200 руб.

Требуется:

­ рассчитать оставшееся количество книг каждого наименования поступивших на книжную базу;

­ рассчитать на какую сумму было продано каждого вида книг на каждую базу;

­ найти среднюю сумму продажи;

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

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

2Билет на пригородном поезде стоит 5 монет, если расстояние до станции не больше 20 км; 13 монет, если расстояние больше 20 км, но не превышает 75 км; 20 монет, если расстояние больше 75 км. Составить ведомость, содержащую следующие све­дения: пункт назначения, расстояние, стоимость билета. Выяснить сколько станций находится в радиусе 50 км от города.

3Построить в одной системе координат при графики функций:

а) , б) , в)

4Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»;

б)Заполнить новые столбцы, используя форму;

в)Добавить столбец «Продано на сумму» и сделать расчет;

г)Осуществить сортировку по полям: «Скорость передач», «Модель HDD»;

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих скорость вращения больше 3500 об/мин и время доступа 10.35 мс;

е)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя РАСШИРИННЫЙ ФИЛЬТ, у которых модель HDD Seagate и скорость вращения >3500 об/мин;

ж)Скопировать отсортированную таблицу на новый лист и подвести общие итоги для каждой модели HDD по сумме, на которую было проданного товара;

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

Вариант №5

1Годовой доход семьи из 4-х человек составляет в среднем 150000 ден. ед. Основные затраты состоят из: коммунальные услуги – 13700 (ден. ед.); плата за телефон – 9600 (ден. ед.); питание – 64000 (ден. ед.); плата за детские учреждения – 5800 (ден. ед.). Остальная сумма тратится, исходя из нужд и потребностей семьи. Требуется рассчитать:

­ среднемесячный доход семьи,

­ сумму основных затрат,

­ оставшуюся сумму.

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

Построить диаграмму, отображающую долю каждой строки расходов (в процентах) от общей суммы.

2В сельскохозяйственном кооперативе работают 10 сезонных рабочих. Собирают овощи. Оплата труда производится по количеству собранных овощей. Дневная норма сбора составляет k килограммов. Сбор 1 кг овощей стоит M рублей. Сбор каждого килограмма сверх нормы оплачивается в 2 раза дороже.

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

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

3Построить в одной системе координат при графики функций:

а) , б) , в)

4Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»,

б)Заполнить новые столбцы, используя форму.

в)Добавить столбец «Продано на сумму» и сделать расчет.

г)Осуществить сортировку по полям: «Модель HDD», «Скорость вращения».

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих скорость вращения больше 3600 об/мин и время доступа >10 мс.

е)Скопировать таблицу на новый лист и осуществить поиск винчестеров, используя РАСШИРИННЫЙ ФИЛЬТ, у которых шина SCSI и емкость <100 Мб.

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

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

Вариант №6

1Производственная единица изготавливает изделия трех видов А, В, С. Затраты составляют соответственно 5, 10, 11 руб. Прибыль от реализации одного изделия каждого данного вида соответственно равны 10, 14, 12 руб. План производства изделий равен соответственно 148265 шт., 543292 шт., 463012 шт., Было изготовлено соответственно изделий 135672 шт., 608712шт., 456732 шт. Требуется рассчитать:

­ прибыль от реализации каждого вида изделий,

­ общую прибыль,

­ процент выполнения плана по каждому виду изделия.

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

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

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

210 спортсменов-многоборцев принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает определенное количество очков. Спортсмену присваивается звание мастера, если он набрал в сумме не менее k очков. Сколько спортсменов получило звание мастера?

3Построить в одной системе координат при графики функций:

а) , б) , в)

4Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»;

б)Заполнить новые столбцы, используя форму;

в)Добавить столбец «Продано на сумму» и сделать расчет;

г)Осуществить сортировку по полям: «Модель HDD», «Скорость вращения»;

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих шину SCSI, скорость вращения больше 3600 об/мин;

е)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя РАСШИРИННЫЙ ФИЛЬТ, у которых шина MFM и ёмкость ³ 200 мб.;

ж)Скопировать отсортированную таблицу на новый лист и подвести общие итоги для каждой модели HDD по сумме, на которую было продано товара;

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

Вариант №7

1На предприятии работники имеют следующие оклады: начальник отдела 1000 руб., инженер 1 кат. 860 руб., инженер 687, техник 315 руб., лаборант 224 руб. Предприятие имеет 2 филиала: ср. полосе и условиях крайнего севера. Все работники получают 10 % от оклада за вредный характер работы, 25% % от оклада ежемесячной премии, со всех работников удерживают 25 % подоходный налог и 3% проф. взнос, 1% пенсионный фонд.

Работники филиала расположенные в ср. полосе 15 % районного коэффициента. Работники филиала расположенные крайнего севера 70 % районного коэффициента, и 50% северной надбавки от начисления. Требуется рассчитать:

­ суммы, к получению каждой категории работников;

­ заработанную плату для каждого филиала в отдельности;

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

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

2В доме проживают 10 жильцов. Компания по снабжению электроэнергией взимает плату с жильцов по тарифу: k рублей за 1 Квт/ч и m рублей за каждый Квт/ч сверх нормы. Норма составляет 50 Квт/ч. Таблица должна включать в себя следующую информацию: Ф.И.О. жильца, количество киловатт, сумма оплаты.

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

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

3 Построить в одной системе координат при графики функций:

а) , б) , в)

4 Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»;

б)Заполнить новые столбцы, используя форму;

в)Добавить столбец «Продано на сумму» и сделать расчет;

г)Осуществить сортировку по полям: «Модель HDD», «Скорость передач»;

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих скорость вращения <= 3600 об/мин и время доступа 25 мс.;

е)Скопировать таблицу на новый лист и осуществить поиск винчестеров, используя РАСШИРИННЫЙ ФИЛЬТ, у которых модель HDD Seagate и скорость передачи <2000 об/мин.;

ж)Скопировать отсортированную таблицу на новый лист и подвести общие итоги для каждой модели HDD по количеству проданного товара;

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

Вариант №8

1Часовой завод изготовил в январе часы вида А- 150 шт., В- 230 шт., С-180 шт. В феврале производство продукции выросло соответственно на 5%, 3% и 2%. В марте соответственно 1,5%, 1,6% и 2%. Затраты на изготовление 1 единицы продукции соответственно составляет 85 руб., 73 руб. и 84 руб. Продажная стоимость 1 единицы составляет соответственно 120 руб., 100 руб. и 110 руб. Требуется рассчитать:

­ количество часов изготовлено за каждый месяц;

­ количество часов изготовленных каждого вида;

­ общее количество изготовленных часов;

­ ежемесячные затраты на производство каждого вида изделия;

­ прибыль (стоимость – затраты) от реализации каждого вида изделий за каждый месяц.

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

2В доме проживают 10 жильцов. Компания по снабжению электроэнергией взимает плату с жильцов по тарифу k рублей за 1 Квт/ч, причем некоторые жильцы являются пенсионерами и имеют льготу – 50% скидки при оплате. Таблица должна включать в себя следующую информацию: Ф.И.О. жильца, количество киловатт, льгота, сумма оплаты.

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

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

3Построить в одной системе координат при графики функций:

а) , б) , в)

4Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»;

б)Заполнить новые столбцы, используя форму;

в)Добавить столбец «Продано на сумму» и сделать расчет;

г)Осуществить сортировку по полям: «Шина», «Скорость передач»;

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих модель HDD с ёмкостью ³ 200 и <700 Мб.;

е)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя РАСШИРИННЫЙ ФИЛЬТ, у которых шина SCSI, скорость передачи >300 и время доступа 10 мс.;

ж)Скопировать отсортированную таблицу на новый лист и подвести общие итоги для каждой модели HDD по количеству проданного товара;

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

Вариант №9

1Фирма «Новый путь» закупила новые автомобили: ВАЗ-21093 –35 шт. по цене $2000, ГАЗ-31029- 10шт по цене $2100, ВАЗ-2106- 21 шт. по цене $1600). На машины были поставлены сигнализации и врезаны люки. После чего они проданы по цене ВАЗ-21093 – 45000 руб., ГАЗ-31029 - 48000 руб., ВАЗ-2106 - 39000 руб. Требуется рассчитать:

­ в руб. и долларах сумму затрат на покупку каждой марки автомобиля;

­ наименьшую и наибольшую сумму затрат;

­ общую сумму затрат на покупку всех автомобилей.

Построить диаграмму по объему продаж всех марок.

2Покупатели магазина пользуются 10% скидками, если по­купка состоит более, чем из пяти наименований товаров или стоимость покупки превышает k рублей.

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

Для ячеек «стоимость покупки» назначить следующий формат: если стоимость покупки превышает или равна k руб, то содержимое ячейку залить красным цветом и начертание для шрифта полужирное, а если нет, то содержимое ячейки залить зеленым.

3Построить в одной системе координат при графики функций:

а) , б) , в)

4 Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»;

б)Заполнить новые столбцы, используя форму;

в)Добавить столбец «Продано на сумму» и сделать расчет;

г)Осуществить сортировку по полям: «Модель HDD», «Время доступа»;

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих шину SCSI и время доступа >10 м.;

е)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя РАСШИРИННЫЙ ФИЛЬТ, у которых модель HDD Seagate и скорость вращения >3500 об/мин;

ж)Скопировать отсортированную таблицу на новый лист и подвести общие итоги для каждой модели HDD по сумме, на которую было проданного товара;

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

Вариант №10

1На предприятии работники имеют следующие оклады: начальник отдела – 1000р., инженер 1 кат.-860 руб., инженер –687 руб., техник – 315 руб., лаборант-224 руб. Все работники получают надбавку 10% от оклада за вредный характер работы и 50 % премии в том месяце, когда выполняется план. При невыполнении плана из зарплаты вычитают 10% от начислений. Со всех работников удерживают 12% подоходный налог, 3% профсоюзный взнос и 1% к пенсии. Все удержания производятся от начислений.

Требуется рассчитать суммы к получению каждой категории работников по месяцам (не менее 5 месяцев).

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

2Телефонная компания взимает плату за услуги телефонной связи по следующему тарифу: 370 мин в месяц оплачиваются как абонентская плата, которая составляет 200 монет. За каж­дую минуту сверх нормы необходимо платить по 2 монеты. Составить ведомость оплаты услуг телефонной связи для 10 жильцов за один месяц, учитывающую Ф.И.О. жильца, количество минут, оплата.

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

3Построить в одной системе координат при графики функций:

а) , б) , в)

4Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»;

б)Заполнить новые столбцы, используя форму;

в)Добавить столбец «Продано на сумму» и сделать расчет;

г)Осуществить сортировку по полям: «Модель HDD», «Скорость передачи»;

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих модель HDD, которая начинается с буквы «S», и скорость передачи >200, но <7000;

е)Скопировать таблицу на новый лист и осуществить поиск винчестеров, используя РАСШИРИННЫЙ ФИЛЬТ, у которых шину SCSI, скорость вращения больше 3600 об/мин.;

ж)Скопировать отсортированную таблицу на новый лист и подвести общие итоги для каждой модели HDD по количеству проданного товара;

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

Вариант №11

1Кондитерская фабрика для производства трех видов карамели А, В и С используют три вида сырья: сахарный песок, патоку, фруктовое пюре. Норма расхода сырья на 1 т. карамели указаны в таблице:

Общее количество сырья каждого вида равна 1500, 900 и 300 тонн. За месяц фабрика изготовила карамели вида А 820 тонн, В 900 тонн и вида С 400 тонн. Требуется рассчитать:

­ расход сырья каждого вида;

­ количество оставшегося сырья;

­ количество карамели вида А, на производство которого хвати оставшегося сахара.

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

2На звероферме содержится некоторое число пушных зверьков (норка, песец, соболь, лиса) разного возраста и веса. Каждому пушному зверьку в возрасте от 1-го до 2-х месяцев полагается дополнительный стакан молока в день, если его вес меньше 3 кг. Количество зверьков, возраст и вес каждого известны. Выяснить сколько литров молока в месяц необходимо для зверофермы, если один стакан молока составляет 0,2 литра.

Для ячеек «вес» назначить следующий формат: если вес зверька меньше 3 кг, то для шрифта задать начертание полужирное, коричневый цвет и размер 16.

3 Построить в одной системе координат при графики функций:

а) , б) , в)

4 Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»;

б)Заполнить новые столбцы, используя форму;

в)Добавить столбец «Продано на сумму» и сделать расчет;

г)Осуществить сортировку по полям: «Модель HDD», «Ёмкость»;

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих модель HDD, название шины у которой начинается с буквы «S», и скорость передачи >2000, но <5000;

е)Скопировать таблицу на новый лист и осуществить поиск винчестеров, используя РАСШИРИННЫЙ ФИЛЬТ, у которых модель HDD SEAGATE и время доступа от 15 мс и выше;

ж)Скопировать отсортированную таблицу на новый лист и подвести общие итоги для каждой модели HDD по сумме, на которую было проданного товара;

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

Вариант №12

1Производственная единица изготавливает изделия трех видов: П1, П2, П3. Затраты на изготовление единицы продукций П1, П2, П3 составляют 7, 15, 10 (руб.) соответственно. Прибыль от реализации одного изделия данного вида составляет 20, 16, 25 (руб.). План производства изделий П1-200482 шт., П2-43292 шт., П3-1463012 шт. В январе было изготовлено П1-135672 шт., П2-60712шт., П3-1456732 шт. Требуется:

- рассчитать в рублях и долларах (курс доллара - величина изменяющаяся): плановые затраты на производство;

- прибыль о реализации каждого вида изд.;

- прибыль, полученную предприятием в январе;

- процент выполнения плана в январе по каждому виду изд.

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

2 На звероферме содержится некоторое число пушных зверьков (норка, песец, соболь, лиса) разного возраста и веса. Если вес пушного зверька в возрасте от 6-ти до 8-ми месяцев превышает 7 кг, то необходимо снизить дневное потребление витаминного концентрата на 125 г. Количество зверьков, воз­раст и вес каждого известны. Выяснить на сколько килограм­мов в месяц снизится потребление витаминного концентрата.

Для ячеек «вес» назначить следующий формат: если вес зверька превышает 7 кг, то для шрифта задать размер 16 и ячейку залить зеленым цветом.

3 Построить в одной системе координат при графики функций:

а) , б) ,

в)

4 Заполнить таблицу, содержащую сведения о винчестерах и отформатировать по вашему усмотрению:

ПАРАМЕТРЫ ВИНЧЕСТЕРОВ

Выполнить следующие задания:

а)Добавить следующие столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт.»;

б)Заполнить новые столбцы, используя форму;

в)Добавить столбец «Продано на сумму» и сделать расчет;

г)Осуществить сортировку по полям: «Модель HDD», «Время доступа»;

д)Скопировать отсортированную таблицу на новый лист и осуществить поиск винчестеров, используя АВТОФИЛЬТР, имеющих любую модель HDD кроме SEAGATE и скорость передачи >1500, но <3000;

е)Скопировать таблицу на новый лист и осуществить поиск вин

Дата добавления: 2014-10-14; просмотров: 1191; Нарушение авторских прав

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