Файл: ЛР_ 2_ЭТ.pdf

1 

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

Переименовать  Лист1,  Лист2  и  Лист3  в  Январь,  Февраль  и  Март  соответственно  .Добавить  лист  4  и  назвать 

1  квартал

Щелкните  по ярлычку  листа 

Январь

  и  введите  начиная  с  ячейки  B2  таблицу  «Выплаты  за  январь».  Аналогично  на  листах 

Февраль

 и 

Март

 введите таблицы, показанные на рис. 6.2 и 6.3. Если Вы собираетесь терпеливо вводить данные в каждую 

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

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

Автозаполнением. Введите номера 1 и 2, а остальные числа добавьте автозаполнением. Заголовки, надписи, фамилии и числа 
столбца 

Суммы

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

автоматически.  Сделайте  это  без  подсказок.  Для  этого  у  Вас  есть  знания,  а  в  таблице  все  необходимые  данные  для 
автоматических расчетов.  Не забудьте разметить таблицу линиями, название месяца выделите красным цветом, выполните 
автоподбор  ширины  столбцов.  Еще  проще  можно  создать  таблицы  рис.  6.2  и  6.3.  Видно,  что  эти  таблицы  отличаются  от 
первой  только  числами  и  названием  месяца,  поэтому  проще  выполнить  создание  этих  таблиц  копированием  созданной  на 
листе 

Январь

 таблицы на листы 

Февраль

 и 

Март

 и исправлением скопированных чисел в столбце 

Сумма 

На листе Январь 

На листе Февраль 

На листе Март 

Консолидацией

 называется сведение данных из нескольких таблиц в одну итоговую таблицу. 

Предположим, необходимо свети в таблицу на листе 

1 квартал

 общую сумму 

выплат и налогов. Чтобы создать такую таблицу скопируйте с любого другого 
листа таблицу на этот лист и уберите из таблицы-копии числовые данные. 
Для этого выделите диапазон и нажмите клавишу 

Delete

. Находясь на листе 

1 

квартал

, щелкните на верхней левой пустой числовой ячейке и выполните 

команду 

Данные/Работа с данными/Консолидация

. Появится окно, 

показанное на рис. 6.4.

Установите в нем галочку напротив опции 

Создавать связи с исходными 

данными

. Это нужно для того, чтобы изменения в исходных данных 

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

как

 консолидировать данные. Выберите в списке 

Функция:

 значение 

Сумма

. Это значит, что собираемые в таблицу 

данные нужно суммировать. 
Теперь нужно указать 

что

 следует консолидировать. Щелкните на 

кнопке справа в строке 

Ссылка

. Окно сожмется до размеров строки. 

Ничего в нее не вводите, а щелкните по ярлычку листа 

Январь

 и 

выделите весь диапазон числовых ячеек. Снова щелкните по кнопке 
строки 

Ссылка

. Окно распахнется. Щелкните в нем на кнопке 

Добавить

. В 

список диапазонов

 добавится первый диапазон. Таким 

же способом добавьте соответствующие диапазоны с листов 

Февраль

 и 

Март

. Теперь окно 

Консолидация

 примет вид, 

показанный на рис. 6.4. Щелкните на кнопке ОК. Таблица консолидации заполнится нужными суммами и примет вид, 
показанный на рис. 6.5. 

2  Нахождение наибольшего и наименьшего элементов в числовой таблице. 

Перейдите на  лист 5 , создайте таблицу 

Установите курсор в ячейку С4, введите запись «минимальное». 

Перейдите  в  ячейку  D4,  щелкните  на  панели  инструментов  по  кнопке 

.  В 

появившемся диалоговом окне выбрать функции Статические/МИН, в следующем  

в строке Число 1 ввести с клавиатуры диапазон A1:D3 или выделить его в таблице. 

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

Введите имя МаксМин.  
 

3. Логические функции 

На листе 6. 

1.Записать: в В1 – "Ф.И.О." в С1 – "Кол-во пропусков"; в  D1 –" средний 

балл  модулей  %,  в  Е1-Результат.;  в      В2:В7  –  вымышленный  список 

фамилий;    С1:С7  –  числа  в  диапазоне  от  0  до10;          D1:D7  -  числа  в 

диапазоне от 10 до100;  

1.

УСЛОВИЕ:  Считать  допущенными  к  сессии  студентов  имеющих 

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

больше 70%. 

Выполнение: 

Набрать 

в 

Е2 

формулу:     

=ЕСЛИ(И(С2<3;D2>70%);"Допущен";  "Не  допущен").  Скопировать  еѐ  в 

Е2:Е7.    Данная  формула  возвратит  текст  "Допущен",  если  средний  балл 

больше 70% или "Не допущен, если студент имеет меньше 3 пропусков занятий без уважительных причин. 
 

2 

4

Построение графика функции

. 

Перейти на следующий лист. Протабулировать функцию y = 2x

2

-4x-6 на отрезке [-5;5] с шагом 1.. Построить график 

функции. Результат на рисунке 3.

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

1.

. Заполнить первый столбец значениями переменной 

Х, второй значениями функции Y (используя 
автозаполнение). В ячейке В2 ввести формулу:

= 2*A2^2-

4*A2-6

 . 

2.

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

Для этого. Щелкните по ячейке вне таблицы Вставка-
График 

3.

В появившемся (пустом )окне щелкнуть ПКМ и 

Выбрать данные 

4.

Установить курсор в Диапазоны данных для 

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

5.

 В окне Подписи горизонтальной оси (категории) щелкнуть по кнопке Изменить, и выделить значения аргумента. Ок. Ок 

6.

Для введения заголовка и подписи осей диаграммы выбрать МАКЕТ 1

7.

  В полученном образце изменить НАЗВАНИЕ ДИАГРАММЫ на График функции и изменить Название оси на Значения 

аргумента и Значения функции

5. Именованные диапазоны, массивы и объемные диаграммы

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

для x = -0,5; -0,4; -0,3; …; 0,4; 0,5 и y = -0,6; -0,5; -0,4; …; 0,3; 0,4, а также построить график этой функции.   Постоим 
таблицу  в виде массива: по строке отложить значения переменной x, а по столбцу – переменной y, а вычисленные значения 
функции – в ячейках на пересечении соответствующих значений аргументов. Это компактный способ представления 
данных. Действительно, если бы вычисления велись привычным способом, то потребовалось бы 363 ячейки, в то время как 
представление массивом потребует всего 144 ячейки.  В окончательном виде такой массив показан на рис. . 

1.

Сначала создадим диапазон ячеек для аргумента x. Его расположим в строке 1 в ячейках B1-L1. Внесите в ячейку B1 

число -0,5; в ячейку C1 – число –0,4. Выделите их, затем разом занесите числа в остальные ячейки автозаполнением.  

2.

Снова выделите ячейки этого диапазона (если они по какой-либо причине выделены). Выполните команду меню: 

Формулы-Присвоить имя В открывшемся окошке дайте диапазону 
имя xx. 

3.

 Нажмите кнопку Добавить и закройте окошко. Теперь этот 

диапазон имеет имя.  

4.

Аналогично внесите числа в диапазон столбца A2-A12. Это 

значения переменной y Дайте этому диапазону имя yy.  

5.

Теперь выделите ячейки прямоугольного диапазона B2-L12. 

Внесите в строку ввода (она над таблицей) формулу =xx^4-4*yy^5. 
Нажмите клавиши Ctrl+Shift+Enter. Весь массив ячеек B2-L12 будет 
заполнен вычисленными значениями функции.  

6.

Приведите таблицу в порядок, установив шрифт размером 9, 

по четыре знака в дробной части массива и по одному знаку в 
диапазонах аргументов, выполните автоподбор ширины столбцов. 
Таблица примет вид, показанный на рис. 5.10.  

Теперь построим график функции. Для этого будем использовать 

вычисленные значения массива ячеек. 

Построить самостоятельно. Вставка-Диаграммы-Другие диаграммы –Поверхность.