3. ЛР. Excel. Практикум Обработка данных средствами электронных таблиц Microsoft Excel

• Название: Практикум Обработка данных средствами электронных таблиц Microsoft Excel
• Дата: 27.12.2021
• Размер: 169.54 Kb.
• Имя файла: 3. ЛР. Excel.docx
• Тип: Практикум; #319780
• страница: 3 из 3

3. 
   В столбцы E, F, G, H, I введи­те формулы, для этого:
   
   • 
     установите курсор в первую ячейку столбца количества отличных оценок (E2) и выполните Вставка —> Функция—> категория Логические—>функция = ЕСЛИ, убрать появившееся диалоговое окно Аргументы функции с табл. 3.7 (если оно ее закрывает);
     
   • 
     в диалоговом окне Аргументы функции установите курсор в поле Лог._ выражение и щелкните мышью в рабочей области Excel на ячейке D2;
     
   • 
     появится адрес ячейки D2 и с клавиатуры введите < = 5 >, т.е. сформируется логическое выражение D2=5;
     
   • 
     в поле Значение_если_истина введите<1>;
     
   • 
     в поле Значение_если_ложъ введите< 0>;
     
   • 
     щелкните на кнопке <ОК>.
     

Методом протягивания скопируйте формулу по столбцу E - “Кол-во 5”.

4. 
   С помощью Мастера функций аналогичным способом введите формулы в столбцы “Кол-во 4“, “Кол-во 3” и т. д., изменяя соответст­венно значение поля Логическое выражение на D2 = 4, D2 = 3, D2 = 2. Для подсчета количества неявившихся на экзамен необходимо задавать логическое выражение D2=0;
   
5. 
   Чтобы подсчитать сумму всех пятерок, четверок и т. д. и резуль­таты представить в виде отдельной таблицы, нужно по каждому столб­цу “Кол-во оценок” задать имена блокам соответствующих ячеек. Для этого выполните следующие действия:
   

• 
  выделите блок ячеек Е2:Е8 столбца “Кол-во 5”;
  
• 
  выполните команду меню Вставка —> Имя —> Присвоить;
  
• 
  в диалоговом окне Присвоение имени в строке Имя введите слово Отлично и щелкните на кнопке Добавить и затем <ОК>;
  
• 
  далее выделите ячейки F2:F8 столбца “Кол-во 4” и выполните команду Вставка —> Имя —> Присвоить;
  
• 
  в диалоговом окне Присвоение имени в строке Имя введите слово Хорошо и щелкните на кнопке <Добавить> и затем <ОК>;
  
• 
  аналогичные действия выполните с остальными столбцами табл. 3.7, создав имена блоков ячеек Удовлетворительно, Неудовлетворитель­но, Неявка.
  

6. 
   Создайте таблицу Итоги сессии (табл.3.8).
   

Таблица 3.8

ИТОГИ СЕССИИ
Количество отличных оценок
Количество хороших оценок
Количество удовлетворительных оценок
Количество неудовлетворительных оценок
Неявки
ИТОГО

7. 
   Введите формулу подсчета количества полученных оценок определенного вида:
   

• 
  установите курсор в ячейку подсчета количества отличных оценок и выполните Вставка —> Функция—> категория Математические —>функция = СУММ;
  
• 
  щелкните на кнопке <ОК>;
  
• 
  в диалоговом окне Аргументы функции установите курсор в строку Число1 и выполните Вставка —> Имя—>Вставить;
  
• 
  в диалоговом окне Вставка имени выберите имя блока ячеек Отлично и щелкните на кнопке <ОК>;
  
• 
  повторите аналогичные действия для подсчета количества других оценок.
  

8. 
   Подсчитайте ИТОГО – количество всех полученных оценок, используя кнопку Автосумма на стандартной панели инструментов.
   

Кнопка Автосумма – это символ Σ на стандартной панели инструментов. Если его нет, необходимо выполнить Вид —> Панели инструментов—> поставить флажок на кнопку Стандартная.

Для подсчета ИТОГО:

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

Задание № 2. Определить, в какой из заданных интервалов попадает зарплата каждого сотрудника НИИ, представленная в табл. 3.9.

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

1. 
   Создайте новую рабочую книгу.
   
2. 
   Создайте таблицу из восьми столбцов, в которой содержатся сведения о пяти сотрудниках ОАО: № п/п, Ф.И.О., ежемесячная зар­плата (табл. 3.9).
   
3. 
   Создайте таблицу, содержащую четыре интервала числовых зна­чений зарплат: 3000 — 4000, 4000 — 5000, 5000 — 6000, 6000—8000 ( см. табл. 3.10).
   
4. 
   Чтобы определить, попадает ли значение зарплаты из столбца С в заданный интервал, нужно использовать логическую функцию ЕСЛИ с заданием сложного условия И.
   

Для этого необходи­мо выполнить следующее (для интервала 3000 – 4000):

• 
  установите курсор в ячейку D2;
  
• 
  выполните Вставка —> Функция—> категория Логические —>функция = ЕСЛИ;
  
• 
  щелкните на кнопке <ОК>;
  
• 
  в открывшемся окне Аргументы функции в поле Лог._выражение введите следующее логическое выражение: И(C2>$A$10;C2<=$B$10);
  
• 
  в поле Значение_если_истина введите <1>;
  
• 
  в поле Значение_если_ложь введите < 0>;
  
• 
  щелкните на кнопке <ОК>.
  

Для остальных интервалов аналогично, только будут другие номера ячеек A и B– А11, B11 и т. д. (см. табл.3.10).

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

Таблица 3.9

	A	B	C	D	E	F	G	H
1	№п/п	Ф.И.О.	Зарплата	1 ин.	2 ин.	3 ин.	4 ин.	Проверка
2	1	Кузнецов	7896	0	0	0	1	1
3	2	Свиридов	5990	0	0	1	0	1
4	3	Молотов	4098	0	1	0	0	1
5	4	Иванов	3980	1	0	0	0	1
6	5	Петров	4346	0	1	0	0	1
7	ИТОГО			1	2	1	1	5

Таблица 3.10

		А	B
		Интервалы
10	1 ин.	3000		4000
11	2 ин.	4000		5000
12	З ин.	5000		6000
13	4 ин.	6000		8000

5. 
   В ячейке D2 находится формула ЕСЛИ(И(C2>$A$10;C2<=$B$10);1;0). Эту формулу операцией автозаполнения скопировать по столбцу Dот D2 до D6 для остальных сотрудников НИИ.
   
6. 
   Подобные формулы ввести в столбцы E, F,G.
   
7. 
   Для подсчета числа попаданий в каждый интервал выполните
   следующие действия:
   
   • 
     выделите блок D2:D6;
     
   • 
     нажмите кнопку Автосумма на Стандартной панели инструментов;
     
   • 
     повторите это действие для каждого столбца.
     

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

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

Задание № 1

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

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

Требуется:

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

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

общей суммы;

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

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

Задание № 2.

На книжную базу поступили 3 наименования книг: словари, книги по кулинарии и пособия по вязанию. Они были распределены по трем магазинам: “Книжный мир”, “Дом книги” и “Глобус”.

В “Книжный мир” поступило словарей – 10400 экземпляров, кулинарных книг – 23650 экземпляров, пособий по вязанию – 1500 эк­земпляров;

В “Дом книги”– 10300 словарей, 22950 кулинарных книг и 1990 пособий по вязанию;

В “Глобус” – соответственно 9100, 23320 и 2500 экземпляров.

В первом магазине было продано словарей – 8945 экземпляров, кулинарных книг – 19865 экземпляров, пособий по вязанию – 873 экземпляра.

Во втором магазине было продано словарей – 9300 экзем­пляров, кулинарных книг – 21900 экземпляров, пособий по вязанию – 1020 экземпляра.

В третьем магазине соответственно было продано 8530, 18100 и 2010 экземпляров.

Требуется:

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

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

книжную базу;

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

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

Задание № 3

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

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

Требуется:

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

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

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

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

Требуется:

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

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

б) построить диаграмму по прибыли каждого вида изделия.
Задание № 5
На предприятии работники имеют следующие оклады: начальник отдела – 9000 р., инженер 1кат. – 7000 р., инженер – 5000 р., техник – 3000 р., лаборант – 2000 р.

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

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

Требуется:

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

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

.

Контрольные вопросы и задания

1. 
   Каково назначение электронной таблицы?
   
2. 
   Как называется документ в программе Excel? Из чего он состоит?
   
3. 
   Каковы особенности типового интерфейса табличных процессоров?
   
4. 
   Какие типы данных могут содержать электронные таблицы?
   
5. 
   Какие данные называют зависимыми, а какие независимыми?
   

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

6. 
   Что в Excel используется в формулах в качестве операндов?
   
7. 
   Что такое формула в электронной таблице и ее типы? Приведите примеры.
   
8. 
   Что такое функция в электронной таблице и ее типы? Приведите примеры.
   
9. 
   Поясните, для чего используются абсолютные и относительные адреса ячеек.
   
10. 
    Что такое автозаполнение?
    
11. 
    Каков приоритет выполнения операций в арифметических формулах Excel?
    
12. 
    Как можно “размножить” содержимое ячейки?
    
13. 
    Как посмотреть и отредактировать формулу, содержащуюся в ячейке?
    
14. 
    Какой тип адресации используется в Excel по умолчанию?
    
15. 
    В чем состоит удобство применения относительной и абсолютной адресации при заполнении формул?
    
16. 
    Что такое диапазон, как его выделить?
    

18. 
    Как защитить содержимое ячеек электронной таблицы от несанкционированного доступа и внести изменения?
    
19. 
    Укажите, какие Вы знаете типы диаграмм, используемых для интерпретации дан­ных электронной таблицы. Поясните, когда следует или не следует использовать каждый из них.
    
20. 
    Какие способы объединения нескольких исходных электронных таблиц в одну Вам известны?
    
21. 
    Как использовать электронную таблицу для моделирования по типу решения за­дачи “Что будет, если...”?