Методика выполнения работы

Создайте новую рабочую книгу.

Создайте таблицу из восьми столбцов, в которой содержатся сведения о семи сотрудниках НИИ: № п/п, Ф.И.О., ежемесячная зарплата (табл. 5.10).

Создайте таблицу, содержащую четыре интервала числовых значений зарплат: 1000 — 2000, 2000 — 3000, 3000 — 4000, 4000—6000 (табл. 5.11).

Чтобы определить, попадает ли значение зарплаты из столбца С в заданный интервал, нужно использовать логическую функцию ЕСЛИ с заданием сложного условия И (рис. 6.2). Для этого необходи­мо выполнить следующее:

установить курсор в ячейку D2;

щелкнуть на значке Вставка функции Стандартной панели инст­рументов;

в окне Мастера функций выбрать Категорию функции Логические, в окне Вид функции — выбрать функцию ЕСЛИ, нажать кнопку <ОК>;

в адресной строке рабочего окна в раскрывающемся списке вы­брать функцию И;

установить курсор в поле Логическое 1,

на рабочем поле Excel щелкнуть на ячейке С2;

с клавиатуры ввести >;

на рабочем поле Excel щелкнуть на ячейке А10;

установить курсор в поле Логическое 2;

на рабочем поле Excel щелкнуть на ячейке С2;

с клавиатуры ввести <;

Рис 6 2. Использование логических функций

на рабочем поле Excel щелкнуть на ячейке В10;

не закрывая окно Функции И, щелкнуть на слове Если в адресной сроке рабочего окна — откроется окно функции Если;

в поле Значение _если _истина с клавиатуры ввести <1>;

в поле Значение _если _ложь с клавиатуры ввести <0>; нажать кнопку <ОК>

Пример выполнения практической работы.

Таблица 5.10.

5. Формулу из ячейки D2 операцией автозаполнения скопировать по столбцу
D, ссылки на ячейки АЮ и В10 нужно сделать абсолютными.

6. Аналогичным образом введите формулы в столбцы Е, F, G.

7. Для подсчета числа попаданий в каждый интервал выполните
следующие действия:

выделите блок D2:D6;

нажмите кнопку Автосумма на Стандартной панели инструментов;

повторите это действие для каждого столбца.

Значения столбца Проверка получите, используя операцию Автосумма для значений блоков строк D2:G2, D3:G3 и т. д.

Значение ячейки Итого столбца Проверка должно совпадать с количеством сотрудников.

Задания для самостоятельной работы

Задание № 1

Продукцией городского молочного завода являются молоко, кефир и сметана. На производства 1 т молока, кефира и сметаны требуется соответственно 1010, 1020 и 9450 кг молока.

Прибыль от реализации 1 т молока, кефира и сметаны соответст­венно равны 300, 220 и 1360 руб. Было изготовлено молока 123 т, ке­фира 342 т, сметаны 256 т.

Требуется:

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

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

расход молока (сырья);

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

Задание № 2.

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

В первом магазине было продано словарей — 8945 экземпляров, кулинарных книг — 19865 экземпляров, пособий по вязанию — 873 экземпляра; во втором магазине было продано словарей — 9300 экзем­пляров, кулинарных книг — 21900 экземпляров, пособий по вязанию ___ 1020 экземпляра; в третьем магазине соответственно было продано 8530, 18100 и 2010 экземпляров. Требуется:

а) при помощи электронной таблицы рассчитать:

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

процент продажи каждого наименования книг в каждом магазине; количество книг, оставшихся после реализации;

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

Задание № 3

На предприятии работники имеют следующие оклады: начальник _отдела — Ю00 руб., инженер 1кат. — 86о руб., инженер — 687 руб., техник — 315 руб., лаборант — 224 руб. Предприятие имеет два фи­лиала: в средней полосе и в условиях крайнего севера. Все работники получают надбавку 10% от оклада за вредный характер работы, 25% от оклада ежемесячной премии. Со всех работников удерживают 20% подоходный налог, 3% профсоюзный взнос и 1% в пенсионный фонд. Работники филиала, расположенного в средней полосе, получают 15% районного коэффициента, работники филиала, расположенного в рай­оне крайнего севера, имеют 70% районный коэффициент и 50% север­ной надбавки от начислений.

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

Требуется:

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

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

Задание № 4

Производственная единица изготавливает изделия трех видов: П1 П2 и ПЗ. Затраты на изготовление единицы продукций П1, П2 и П3 составляют 7, 15 и 10 (руб.) соответственно.

Прибыль от реализации одного изделия данного вида соответствен­но равна 20, 16 и 25 (руб.). План производства изделий П1—200482 шт., П2—43292 шт., ПЗ—1463012 шт. В январе было изготовлено П1— 135672 шт., П2— 60712 шт., ПЗ— 1456732 шт.

Требуется:

а) при помощи электронной таблицы рассчитать в рублях и долла­рах (курс доллара — величина изменяющаяся):

плановые затраты на производство;

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

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

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

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

Задание № 5

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

Нормы расхода сырья на 1 т карамели соответственно равны (т):

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

Требуется:

а) при помощи электронной таблицы рассчитать:
расход сырья каждого вида;

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

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

б) построить диаграмму по расходу сырья каждого вида для про­изводства карамели А, В, С.

Задание № 6

фирма «Новый путь» совершила закупку новых автомобилей: ВДЗ-21093, ГАЗ-31029 и ВАЗ-2106. Автомобилей марки ВАЗ-21093 — было закуплено 35 штук по цене 2000$; автомобилей марки ВАЗ-2106 — было закуплено 21 (шт.) по цене 1600$; автомобилей марки рдЗ-31029 — было закуплено 10 (шт.) по цене 2100$.

На машины были поставлена сигнализация и врезаны люки. После чего они были проданы по цене ВАЗ-21093—45000 руб., ВАЗ-2106—39000 руб. и ГАЗ-31029— 8000 руб.

Требуется:

а) при помощи электронной таблицы рассчитать:

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

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

Задание № 7

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

Требуется:

а) при помощи электронной таблицы рассчитать в рублях и долла­рах:

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

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

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

Задание № 8

На предприятии работники имеют следующие оклады: начальник отдела — 1000 р., инженер 1кат. — 860 руб., инженер — 687 руб., техник — 315 руб., лаборант — 224 руб.

Все работники получают надбавку 10% от оклада за вредный ха­рактер работы. Все работники получают 50 % премии в том месяце когда выполняется план.

При невыполнении плана из зарплаты вычитают 10 % от начисле­ний. Со всех работников удерживают 12 % подоходный налог, 3% профсоюзный взнос и 1% к пенсии. Все удержания производятся от начислений.

Требуется:

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

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

Задание № 9

Производственная единица изготавливает изделия трех видов А, В и С. Затраты на изготовление единицы продукций А, В и С составля­ют 5, 10 и 11 (руб.) соответственно.

Прибыль от реализации одного изделия данного вида соответст­венно равны 10, 14 и 12 (руб.). План производства изделий А—148265, В—543292, С—463012. Было изготовлено А—135672, В—608712, С—456732.

Требуется:

а) при помощи электронной таблицы рассчитать:

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

общую прибыль;

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

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

в) распечатать документ.

Задание № 10

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

Требуется:

а) представить данные в виде таблицы;

б) рассчитать:

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

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

оставшуюся сумму;

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

Задание № 11

В новой рабочей книге создать таблицу (табл. 12).

Посчитать количество отличных, хороших, удовлетворительных и неудовлетворительных оценок по двум предметам и вывести резуль­тат в отдельную строку «Всего».

Посчитать процент всех оценок в сумме по двум предметам. Вы­вести процент в отдельную строку (расчет процента: числитель мень­ше, чем знаменатель; затем находим в панели инструментов формати­рования кнопку с изображением %).

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

Таблица На12

Задание № 12

Определите, какая сумма окажется на счете, если вклад размером 900 тыс. руб. положен под 9% годовых на 19 лет, а проценты начисля­ются ежеквартально.

Задание № 13

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

Задание № 14

Предполагается, что в течение первых двух лет на счет откладывается по 800 тыс. руб. в конце каждого года, а в следующие тр_и года — по 850 тыс. руб. в конце каждого года. Определить будущую стоимость этих вложений к концу пятого года, если ставка процента 11 %.

Задание № 15

Рассчитайте будущую стоимость облигации номиналом 100 тыс. руб. выпущенной на семь лет, если в первые три года проценты начис­ляются по ставке 17%, а остальные четыре года — по ставке 22% го­довых.

Задание № 16

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

Задание № 17

Допустим, рассматривается проект стоимости 100 млн. руб.; ожи­дается, что ежемесячные доходы по проекту составят 16, 25, 36, 49 млн. руб. за четыре месяца. Определите чистую текущую стоимость проекта, если годовая норма процента 195.

Задание № 18

Для покупки компании была взята ссуда 97 млн. руб. под 13% годовых. Доходы от приобретения составили 15, 18, 29, 50 млн. руб. за четыре года и были реинвестированы под 15% годовых. Найдите модифицированную внутреннюю скорость оборота инве­стиции.

Задание № 19

Допустим, проект стоимостью 9 млн. руб. будет в течение сле­дующих трех лет приносить доходы — 4,4; 3,2; 5,9 млн. руб. ежегод­но, а на четвертый год предполагается убыток в 1, 6 млн. руб. Оцените целесообразность принятия проекта, если рыночная норма процен­та 13%.

Задание № 20

Рассчитайте, какая сумма окажется на счете, если 27 тыс. руб. по­ложены на 33 года под 13,5% годовых. Проценты начисляются каждые три года.

Контрольные вопросы

Назначение электронной таблицы.

Как называется документ в программе Excel? Из чего он состоит?

Особенности типового интерфейса табличных процессоров.

Какие типы данных могут содержать электронные таблицы?

Какие данные называют зависимыми, а какие независимыми?

По какому признаку программа определяет, что введенные данные являются не
значением, а формулой?

Что в Excel используется в формулах в качестве операндов?

Что такое формула в электронной таблице и ее типы? Приведите примеры.

Что такое функция в электронной таблице и ее типы? Приведите примеры.

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

Что такое автозаполнение?

Приоритет выполнения операций в арифметических формулах Excel.

Как можно «размножить» содержимое ячейки?

Как посмотреть и отредактировать формулу, содержащуюся в ячейке?

Какой тип адресации используется в Excel по умолчанию?

В чем состоит удобство применения относительной и абсолютной адресации при
заполнении формул?

Что такое диапазон, как его выделить?

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

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

Какие способы объединения нескольких исходных электронных таблиц в одну
вам известны?

Какие особенности печати документов в Excel?

Как использовать электронную таблицу для моделирования по типу решения за­
дачи «Что будет, если...».

Как выделить смежные и несмежные блоки ячеек?

Какие вы знаете команды для работы с базами данных?

Что такое консолидация таблиц?

Что такое макросы и для чего они используются?

Какие вы знаете форматы данных?

Какие вы знаете типы аргументов функции?

Что такое Мастер функции?

Что такое Мастер диаграмм?

Какие вы знаете методы обработки и анализа данных в Excel?

Как осуществляется сортировка списков?

Как осуществляется фильтрация списков?

В каких случаях используют структурирование и группировку данных?

Как формируются итоги в списках по заданным критериям?

Не нашли, что искали? Воспользуйтесь поиском: