Тема : Изучение пакета MS Office , Exel. (4 часа)
Цель : Изучить основы работы с электронными таблицами.
Задачи : Практически освоить принципы работы в MS Exel. Создание и сохранение электронной таблицы (рабочей книги). Изучение способов работы с данными в ячейке (форматирование содержимого ячеек, выбор диапазона ячеек и работа с ними, редактирование содержимого ячеек). Изучение возможностей автозаполнения. Создание и использование простых формул в Excel. Построение диаграмм.

Программа лабораторной работы.

  1. Выполните все задания по MS Exel.
  2. Вышлите созданные файлы преподавателю.

Задание №1. Редактирование рабочей книги.

  1. Создайте новую рабочую книгу (кнопка Создать на стандартной панели инструментов или меню Файл команда Создать как).
  2. Переименуйте текущий рабочий лист (дважды щелкните на ярлыке текущего рабочего листа и переименуйте его).
  3. Добавьте еще один рабочий лист в рабочую книгу (щелкните правой кнопкой мыши на ярлыке листа и в контекстном меню выберите команду Добавить).
  4. Сохраните созданный Вами файл под именем book.xls в своем каталоге (меню Файл, команда Сохранить).
  5. Создайте таблицу по предложенному образцу. Для этого нужно выполнить следующие действия:
    • в ячейку А1 ввести заголовок таблицы “Экзаменационная ведо­мость”;
    • в ячейку A3 ввести “№ п/п”;
    • в ячейку ВЗ ввести “Фамилия, имя, отчество”;
    • в ячейку СЗввести “№ зачетной книжки”;
    • в ячейку D3 ввести “Оценка”;
    • в ячейку Е3 ввести “Фамилия экзаменатора”.

    №п/п

    Фамилия, имя,
    отчество


    зачетной
    книжки

    Оценка

    Фамилия
    экзаменатора

    1

    Иванов И. И.

    З-2330/12

     

    Шевелев Г.Е.

    2

    Петров В. В.

    З-2331/21

     

    Огородников А.С.

    3

    Сидоров С. С.

    З-6230/09

     

    Шевелев Г.Е.

    4

    Федоров Ф. Ф.

    З-11230/23

     

    Огородников А.С.

    5

    Фролов Е.Е.

    З-6230/05

     

    Шевелев Г.Е.

    6

    Демидов Д. Д.

    З-3530/05

     

    Огородников А.С.

  6. Отформатируйте ячейки шапки таблицы:
    • выделите блок ячеек З:ЕЗ);
    • выполните из меню Формат команду Ячейки и откройте вкладку Выравнивание;
    • в диалоговом окне Выравнивание выберите опции: Горизонтальное – по центру, Вертикальное – по верхнему краю;
    • установите флажок Переносить по словам;
    • откройте вкладку Шрифт и установите шрифт Times New Roman, начертание полужирное, размер 12 пт. Аналогичные операции проделайте для ячейки A1.
  7. Измените ширину столбцов, в которые не поместились введенные данные. Для этого можно перетащить границы между строками и столбцами или навести указатель мыши на границу между заголов­ками столбцов, дважды щелкнуть основной кнопкой мыши. Для более точной настройки надо выбрать команду Строка (Столбец) из меню Формат и активизировать подходящую команду из открывающегося меню.
  8. Присвойте каждому студенту свой порядковый номер (не менее 10 студентов), используя маркер заполнения. Для этого:
    • сделайте текущей первую ячейку столбца “№ п/п” и введите в нее цифру 1;
    • затем заполните цифрой 2 следующую ячейку этого столбца;
    • выделите блок, состоящий из двух заполненных ячеек;
    • установите указатель мыши на правый нижний угол выделенного блока. Указатель мыши станет черным крестиком – это маркер запол­нения. Перетащите маркер заполнения при нажатой правой кнопке мыши вниз или выберите команду Правка—> Заполнить—>Прогрессия.
  9. Заполните столбец “Фамилия экзаменатора”. Воспользуйтесь методом автозавершения, который состоит в том, что Excel “угадывает” слово, которое собирается вводить пользователь, или заполните ячейки с помощью маркера заполнения. Для включения Автозавершения надо в меню сервис выполнить команду Параметры, открыть вкладку Правка и установить флажок Автозавершение значений ячеек.
  10. Заполните 2-ой и 3-ий столбцы таблицы данными для своей группы.
  11. Обрамите таблицу: Панель инструментов—> кнопка Обрамле­ние (Граница).
  12. Скопируйте таблицу на другой рабочий лист при помощи буфе­ра обмена. Для этого следует:
    • выделить таблицу или диапазон ячеек;
    • правой клавишей мыши вызвать контекстное меню;
    • выполнить команду Копировать;
    • затем перейти на другой лист;
    • установить курсор в первую ячейку предполагаемой таблицы;
    • выполнить команду Вставить из контекстного меню.
  13. Добавьте в новую таблицу одну строку и один столбец. Для этого нужно:
    • выделить диапазон ячеек по столбцу;
    • щелкнуть правой кнопкой мыши и в открывшемся контекстном меню выбрать команду Добавить ячейки;
    • то же самое повторить для строки.
  14. Внесите в таблицу ряд изменений:
    • очистите колонку с фамилией экзаменатора;
    • озаглавьте эту колонку “Подпись экзаменатора”.
  15. Отсортируйте в новой таблице столбцы 2 и 3 по возрастанию – Данные —> Сортировка или на Стандартной панели инструментов - кнопка Сортировать по возрастанию.

Задание №2. Построение диаграмм.
На основе данных, приведенных в таблице, постройте не­сколько типов диаграмм, наглядно показывающих итоги сессии.

Средний балл по группе

Группа

Информатика

Математический
анализ

История

Экономика

З- 8530

4,2

3,8

4,5

4,3

З- 2330

4,0

4,4

4,4

4,2

З- 3530

3,9

4,0

4,0

3,9

З- 6530

4,3

4,4

4,4

4,1

З-2231

3,8

4,0

4,0

3,9

З-2232

3,3

3,9

3,9

3,6

З-11230

4,5

4,8

4,8

3,9

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

  1. На листе 1 создайте таблицу “Сведения о результатах сдачи сес­сии в представительстве ТПУ”, внесите в нее данные.
  2. Постройте диаграмму для всех групп и всех предметов на от­дельном листе типа График. Для этого следует:
    • выделить всю таблицу;
    • выполнить команду меню Вставка —> Диаграмма или воспользоваться кнопкой Мастер диаграмм на стандартной панели инструментов. 
  3. На третьем шаге построения диаграммы внесите название диа­граммы, обозначения осей, добавьте легенду.
  4. Постройте диаграммы и сравните результаты сдачи по отдельным предме­там (например, по предмету “История”). Для этого следует:
    • выделить столбцы “Группа”, “Информатика” и построить диаграмму по этому предмету;
    • выделить столбец “Группа” и, удерживая клавишу Ctrl, выделить столбец “Математический анализ” и построить диаграмму по этому предмету.
    Аналогично строятся диаграммы и для остальных предметов, столбцы которых не граничат со столбцом “Группа”.
  5. Измените результаты сдачи сессии и проверьте, как это отразилось на построенных диаграммах.

    11
    Рис. 3.2. Средний балл по группам
    для всех предметов
    Рис. 3.3. Средний балл по группам по предмету “История”
  6. Отчет о работе представьте в виде диаграмм на отдельных листах рабочей книги.

Задание № 3. Формулы в Excel.

Компьютерная фирма имеет следующие результаты своей торговой деятельности за отчетный период.

A

B

C

D

1

Наименование
продукции

Цена за ед., р.

Продано, шт.

Выручка от продажи, р.

2

 

 

 

3

Модем

1460

10

 

4

Принтер

2500

15

 

5

Монитор, 17"

5750

20

 

6

Компьютер

19899

25

 

7

Цифровая фотокамера

10900

4

 

8

Переносной накопитель, 128 Mb

1535

6

 

9

Сканер

3050

7

 

1

Жесткий диск, 80 Gb

2840

13

 

11

Ноутбук

51470

1

 

12

CD-ROM

745

4

 

13

CD-ReWriter

1550

6

 

14

Итого, сумма выручки, р.

 

 

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

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

  1. Для того чтобы иметь возможность вводить в ячейки информацию в несколько строк, необходимо выполнить Формат → Ячейка, затем выбрать закладку Выравнивание и в пункте Отображение выбрать Переносить по словам.
  2. В столбец A, начиная с ячейки A1 и до A13 ввести наименование продукции, затем ввести информацию в столбцы B и C.
  3. В ячейку D3 ввести формулу расчета выручки в следующей последовательности:
    • сделать эту ячейку активной;
    • набрать знак ‘=’;
    • щелкнуть на ячейку B3;
    • набрать знак ‘*’;
    • щелкнуть на ячейку С3.
    В результате в ячейку D3 будет записана формула: =B3*C3. После нажатия <Enter> в этой ячейке появится результат расчета по этой формуле.
  4. Теперь методом Автозаполнения получите аналогичные формулы для остальных видов продукции. Для этого установите указатель мыши на правый нижний угол ячейки D3. Указатель мыши станет черным крестиком – это маркер запол­нения. Перетащите маркер заполнения при нажатой правой кнопке мыши вниз до ячейки D13. После отпускания клавиши мыши выручка будет подсчитана для всех видов продукции фирмы.
  5. Подсчитайте сумму выручки от продажи всех видов товаров. Щелкните мышкой на ячейку D14 и нажмите кнопку Автосумма на стандартной панели инструментов (знак Σ) или воспользуйтесь кнопкой Вставка функции, расположенной также на стандартной панели. В окне Мастер функций следует выбрать СУММ из категории Математические.

В результате Вы должны получить следующие результаты расчета:
D3→ 14600 D4→37500 … D13→ 9300 D14→ 839405

Задание № 4.  

  1. Изучите создание и использование простых формул, используя тематику финансового и банковского менеджмента.
  2. Сопоставьте доходность акции по уровню дивидендов за 2002 г. по отдельным эмитентам. Исходные данные задачи представлены в таблице.

Таблица 3.4


Эмитент

Номинал
акции
р.

Цена
продажи
р.

Дивиденды,
объявленные
в расчете на год

Доходность акций
по дивиден­дам

NA 

CP 

% Div

DivR 

К номиналу DN

Фактическая
DF 

Сибирьгазбанк

10000

17780

400

 

 

 

Инкомбанк

10000

22900

400

 

 

 

Сургутнефтегаз­
банк

5000

5600

320

 

 

 

Нефтехимбанк

1000

2015

653

 

 

 

Сбербанк

1000

2482

736

 

 

 

КБ Аккобанк

1000

1000

325

 

 

 

СКВ банк

50000

27050

360

 

 

 

Промстройбанк

1000

1200

1535

 

 

 

NA – номинал акции; CP– цена продажи; Div – дивиденды в расчете на год.

  1. Визуально проанализируйте полученные результаты.

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

  1. Создайте в Excel таблицу, введите в нее исходные данные.
  2. В соответствующие столбцы введите формулы для расчета вы­ходных показателей:
    DivR(i) = NA(i)*Div(i);
    DN(i) = DivR(i)/NA(i);
    DF(i) = DivR(i)/CP(i),
    где i = [1,n], n– число рассматриваемых эмитентов.
  3. Создайте табл. 3.5.

    Таблица 3.5
    Расчетная величина

    Значение

    Средняя цена продажи акций

     

    Максимальная цена продажи акций

     

    Минимальная цена продажи акций

     

    Максимальная фактическая доходность акций

     

    Минимальная фактическая доходность акций

     

    Средняя фактическая доходность акций

     

  4. На основании исходного документа “Доходность акций по от­дельным дивидендам” рассчитайте следующие значения:
    • a) среднюю цену продажи акций по всем эмитентам – щелкнуть на соответствующую ячейку в стол­бце “Значение” табл.3.5, затем выполнить
      Вставка —> Функция—> категория Статистические—>функция = СРЗНАЧ. Убрать появившееся диалоговое окно Аргументы функции с табл. 3.4 (если оно ее закрывает). Выделить ячейки столбца “Цена продажи” со значениями исходных данных в табл. 3.4. В строке Число 1 окна Аргументы функции появятся адреса начала и конца данных этого столбца, разделенные двоеточием. Щелкните на кнопке <ОК>;
    • b) максимальную цену продажи акций по всем эмитентам – щелкнуть на соответствующую ячейку в стол­бце “Значение” табл.3.5, затем выполнить
      Вставка —> Функция—> категория Статистические—>функция = МАКС. Выделить ячейки столбца “Цена продажи” со значениями исходных данных в табл. 3.4. <OK>;
    • c) минимальную цену продажи акций - щелкнуть на соответствующую ячейку в стол­бце “Значение” табл.3.5, затем выполнить
      Вставка —> Функция—> категория Статистические—>функция = МИН. Выделить ячейки столбца “Цена продажи” со значениями исходных данных в табл. 3.4. <OK>;
    • d) максимальную фактическую доходность акций по уровню дивидендов - щелкнуть на соответствующую ячейку в стол­бце “Значение” табл.3.5, затем выполнить
      Вставка —> Функция—> категория Статистические—>функция = МАКС. Выделить ячейки столбца “Фактическая DF” со значениями исходных данных в табл. 3.4. <OK>.
    • f) минимальную фактическую доходность акций по уровню дивиден­дов – щелкнуть на соответствующую ячейку в стол­бце “Значение” табл.3.5, затем выполнить
      Вставка —> Функция—> категория Статистические—>функция = МИН. Выделить ячейки столбца “Фактическая DF” со значениями исходных данных в табл. 3.4. <OK>.
    • g) среднюю фактическую доходность акций по уровню дивиден­дов –щелкнуть на соответствующую ячейку в стол­бце “Значение” табл.3.5, затем выполнить Вставка —> Функция—> категория Статистические—>функция = СРЗНАЧ. Выделить ячейки столбца “Фактическая DF” со значениями исходных данных в табл. 3.4. <OK>.
  5. В исходной таблице отсортируйте записи в порядке возрастания фактической доходности по дивидендам (выделите столбец “Фактическая DF”, выполните команду Сортировка меню Данные).
  6. Выполните фильтрацию таблицы, выбрав из нее только тех эми­тентов, фактическая доходность которых больше средней по таблице. Алгоритм фильтрации следующий:
    • выделить данные таблицы с прилегающей одной строкой заго­ловка “Фактическая DF”;
    • выполнить команду из меню Данныe : Фильтр—>Автофилътр;
    • в заголовке столбца “Фактическая доходность” нажать кнопку рас­крывающегося списка и выбрать Условие;
    • в окне пользовательского автофильтра задать условие > “среднее значение” (“среднее значение” взять из последней строки табл.3.5).
  7. Результаты фильтрации поместите на новый рабочий лист, включив в него следующие графы:
    • эмитент;
    • номинал акции;
    • цена продажи;
    • доходность по дивидендам фактическая.
  8. Постройте на отдельном рабочем листе Excel круговую диаграм­му, отражающую фактическую доходность по дивидендам каждого эмитента в виде соответствующего сектора (выделить столбцы “Эми­тент” и “Фактическая доходность”, выполнить команду меню Вставка—>Диаграмма). На графике показать значения доходности, вывести ле­генду и название графика “Анализ фактической доходности акций по уровню дивидендов”.
  9. Постройте на новом рабочем листе Excel смешанную диаграмму, в которой представьте в виде гистограмм значения номиналов и цены продажи акций каждого эмитента, а их фактическую доходность пока­жите в виде линейного графика на той же диаграмме. Выведите легенду и название графика “Анализ доходности акций различных эмитен­тов”. Алгоритм построения смешанного графика следующий:
    • выделить столбцы “Эмитент”, “Номинал акции” и “Цена про­дажи”;
    • выполнить команду меню Вставка —>Диаграмма—>тип диаграм­мы Гистограмма;
    • для добавления линейного графика “Фактическая доходность по дивидендам” правой клавишей мыши активизировать меню Диаграмма —> Исходные данные —> во вкладке Ряд, выбрать кнопку <Добавить>, в поле Имя ввести название ряда “Доходность”, в поле Значения ввести числовой интервал, соответствующий фактической доходности по ди­видендам;
    • на полученной диаграмме курсор мыши установить на столбец, со­ответствующий значению “Доходность”, правой клавишей мыши акти­визировать контекстное меню, выбрать команду Тип диаграммы, где выбрать тип диаграммы — График.

Задание №5. Сортировка данных в списке

  1. Выполнить сортировку данных табл. 3.6 по возрастанию кода предмета, даты проведения занятия, номера группы.
  2. Выполнить сортировку данных табл. 3.6 по возрастанию, исполь­зуя сочетания признаков: код предмета и дату проведения занятия; код предмета и номер группы; номер группы и дату проведения занятия, а также сочетание всех трех признаков.

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

  1. Создайте новую рабочую книгу (меню Файл команда Создать) и сохраните ее под именем SORT.XLS в рабочем каталоге (меню Файл команда Сохранить как).
  2. Сформируйте таблицу результатов занятий.

    Таблица 3.6

    А

    В

    С

    D

    Е

    F

    G

    H

    1

    № группы

    № зачетной книжки

    Код предмета

    Табл. № препод.

    Вид занятия

    Дата

    Оценка

    2

    З-3230

    З-3230/03

    П1

    А1

    Практика

    26.05.99

    3

    3

    З-3230

    З-3230/12

    П2

    А2

    Лекция

    26.05.99

    4

    4

    З-3230

    З-3230/06

    П1

    А1

    Лекция

    11.06.99

    4

    5

    З-3330

    З-3230/08

    П1

    А2

    Лекция

    11.06.99

    5

    6

    З-3330

    З-3230/18

    П2

    А1

    Практика

    16.05.99

    2

    7

    З-6230

    З-6230/03

    П2

    A3

    Лекция

    20.05.99

    3

    8

    З-3230

    З-3230/09

    П1

    А1

    Лекция

    16.05.99

    3

    9

    З-3230

    З-3230/18

    П1

    A3

    Лекция

    16.05.99

    4

    10

    З-3330

    З-3330/03

    П1

    А2

    Лекция

    26.05.99

    4

    11

    З-3531

    З-3531/02

    П2

    А1

    Лекция

    11.06.99

    2

    12

    З-3532

    З-3532/03

    П1

    А2

    Практика

    20.05.99

    5

    13

    З-3230

    З-3230/20

    П2

    А1

    Лекция

    26.05.99

    5

  3. Отформатируйте шапку таблицы следующим образом:
    • шрифтTimes New Roman;
    • размер шрифта 12 пт., курсив;
    • выравнивание по горизонтали — По значению;
    • выравнивание по вертикали — По верхнему краю;
    • установите ключ “Переносить по словам” (выделить соответствующие ячейки и выполнить команду Формат —> Ячейки).
  4. Выполните сортировку по столбцу “Код предмета”, расположив коды предметов по возрастанию. Для этого нужно:
    • выделить таблицу с одной строкой заголовка;
    • выполнить команду меню Данные —> Сортировка;
    • в окне Сортировка диапазона в строке Сортировать по “коду предмета”.
  5. Результат сортировки скопируйте на Лист 2:
    • выделите всю таблицу, выполнить команду Правка —> Копировать;
    • затем на Листе 2 установите курсор в ячейку А1 и выполните ко­манду Правка —> Вставить.
  6. Переименуйте Лист 2, дав ему имя – Сортировка:
    • указатель мыши установите на ярлычке Лист 2;
    • правой клавишей мыши вызовите контекстное меню;
    • выполните команду Переименовать.
  7. Выполните сортировку по столбцу “Дата”, расположив данные возрастанию. Для этого следует установить курсор в любую ячейку поля “Дата” и ввести команду Сортировка из меню Данные, при этом должна выделиться вся об­ласть списка, а в окне Сортировка Диапазона в строке Сортировать по – столбец G. Если этого не произошло, то предварительно выделите весь список, а затем выполните указанную команду.
  8. Выполните сортировку по сочетанию признаков “Дата”, “№ группы”, “Код предмета”. Для этого следует выделить всю таблицу и в диалоговом окне Сортировка установить:
    • в строке Сортировать по — поле “Дата” по возрастанию;
    • в строке Затем — поле “№ группы”, по возрастанию;
    • в следующей строке Затем — поле “Код предмета” по возраста­нию;
    • установите флажок Строка меток столбцов.
  9. Результат сортировки скопировать на Лист 3 и переименовать его в Сортировка 2.

Задание №6. Фильтрация записей.

  1. Создайте новую рабочую книгу с названием “Фильтрация”.
  2. Скопируйте в новую рабочую книгу таблицу (табл. 3.6).
  3. Переименуйте Лист1, присвоив ему имя “Автофильтр №1”.
  4. Чтобы применить Автофильтрацию, установите курсор в об­ласть шапки таблицы и выполните команду Данные—>Фильтр—>Автофильтр.
  5. Сформируйте условия отбора: для преподавателя А1 выбрать сведения о сдаче экзамена на положительную оценку, вид занятий – Лекция. Для этого выполните следующие действия:
    • в столбце Таб препод. нажмите кнопку Фильтр, из списка усло­вий отбора выберите А1;
    • в столбце Оценка нажмите кнопку Фильтр, из списка условий отбора выберите Условие и в диалоговом окне сформируйте условие отбора >2;
    • в столбце Вид занятий нажмите кнопку Фильтр, из списка условий отбора выберите Лекция.
  6. Результат фильтрации скопируйте на новый лист, присвоив ему имя “Автофильтр №2”.
  7. На листе “Автофильтр №1” результат автофильтрации отмени­те, установив указатель мыши в область списка и выполнив команду Данные —> Фильтр —> Автофильтр.
  8. Сформулируйте выборку: для группы З-3230 получите сведения о сдаче экзамена по предмету П1 на оценки 3 и 4.
  9. Результат сохраните на новом листе, присвоив ему имя “Авто­фильтр №3”.
  10. Скопируйте исходную таблицу на новый рабочий лист, пере­именовав его в Форма данных.
  11. Установите курсор в область шапки таблицы и выполните команду Дан­ные —>Форма.
  12. В окне Форма данных просмотрите записи списка и внесите не­обходимые изменения по своему усмотрению с помощью кнопок <Предыдущая> и <Следующая>.
  13. С помощью кнопки <Создатъ> добавьте новые записи.
  14. В окне Форма данных сформируйте условия отбора записей.
    Для этого нажмите кнопку <Критерии>, название которой поменяется на <Правка>. В пустых строках имен полей списка введите критерии:
    • в строку Табл. № препод. введите А1;
    • в строку вид занятия введите Лекция;
    • в строку оценка введите условие > 2.
  15. Просмотрите отобранные записи нажатием на кнопку <Предыдущая> или <Следующая>.
  16. По аналогии сформулируйте условия отбора записей, указанные в п.

Задание № 7. Использование логических функций.

  1. Подсчитайте количество отличных, хороших и т. д. оценок на основании зачетной ведомости, представленной в табл. 3.7.
  2. Произведите расчет, используя операцию “Присвоение имени блоку ячеек”.

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

  1. На новом листе рабочей книги создайте таблицу по образцу табл.3.7.
  2. Заполните данными столбцы A, B, C, D.

    Таблица 3.7

     

    А 

    В 

    С 

    D 

    E 

    F 

    G 

    H 

    I 

    1


    п/п

    Фам., имя, отчество

    № зач. книжки

    Оцен-
    ка

    Кол-во
    5

    Кол-во
    4

    Кол-во
    3

    Кол-во
    2

    Неявка

    2

    1

    Демидов М.И.

    З-3230/04

    5

     

     

     

     

     

    3

    2

    Иванов И. П.

    З-3230/05

    4

     

     

     

     

     

    4

    3

    Кукушкин В. Л.

    З-3230/07

    3

     

     

     

     

     

    5

    4

    Орлов А. П.

    З-3230/11

    4

     

     

     

     

     

    6

    5

    Петров К.Н.

    З-3230/13

    5

     

     

     

     

     

    7

    6

    Сидоров В.О.

    З-3230/15

    2

     

     

     

     

     

    8

    7

    Фролов В А.

    З-3230/18

    0

     

     

     

     

     

  3. В столбцы E, F, G, H, I введи­те формулы, для этого:
    • установите курсор в первую ячейку столбца количества отличных оценок (E2) и выполните Вставка —> Функция—> категория Логические—>функция = ЕСЛИ, убрать появившееся диалоговое окно Аргументы функции с табл. 3.7 (если оно ее закрывает);
    • в диалоговом окне Аргументы функции установите курсор в поле Лог._ выражение и щелкните мышью в рабочей области Excel на ячейке D2;
    • появится адрес ячейки D2 и с клавиатуры введите < = 5 >, т.е. сформируется логическое выражение D2=5;
    • в поле Значение_если_истина введите<1>;
    • в поле Значение_если_ложъ введите< 0>;
    • щелкните на кнопке <ОК>.
  4. Методом протягивания скопируйте формулу по столбцу E - “Кол-во 5”.
  5. С помощью Мастера функций аналогичным способом введите формулы в столбцы “Кол-во 4“, “Кол-во 3” и т. д., изменяя соответст­венно значение поля Логическое выражение на D2 = 4, D2 = 3, D2 = 2. Для подсчета количества неявившихся на экзамен необходимо задавать логическое выражение D2=0;
  6. Чтобы подсчитать сумму всех пятерок, четверок и т. д. и резуль­таты представить в виде отдельной таблицы, нужно по каждому столб­цу “Кол-во оценок” задать имена блокам соответствующих ячеек. Для этого выполните следующие действия:
    • выделите блок ячеек Е2:Е8 столбца “Кол-во 5”;
    • выполните команду меню Вставка —> Имя —> Присвоить;
    • в диалоговом окне Присвоение имени в строке Имя введите слово Отлично и щелкните на кнопке Добавить и затем <ОК>;
    • далее выделите ячейки F2:F8 столбца “Кол-во 4” и выполните команду Вставка —> Имя —> Присвоить;
    • в диалоговом окне Присвоение имени в строке Имя введите слово Хорошо и щелкните на кнопке <Добавить> и затем <ОК>;
    • аналогичные действия выполните с остальными столбцами табл. 3.7, создав имена блоков ячеек Удовлетворительно, Неудовлетворитель­но, Неявка.
  7. Создайте таблицу Итоги сессии (табл.3.8).

    Таблица 3.8

    ИТОГИ СЕССИИ

    Количество отличных оценок

     

    Количество хороших оценок

     

    Количество удовлетворительных оценок

     

    Количество неудовлетворительных оценок

     

    Неявки

     

    ИТОГО

     

  8. Введите формулу подсчета количества полученных оценок определенного вида:
    • установите курсор в ячейку подсчета количества отличных оценок и выполните Вставка —> Функция—> категория Математические —>функция = СУММ;
    • щелкните на кнопке <ОК>;
    • в диалоговом окне Аргументы функции установите курсор в строку Число1 и выполните Вставка —> Имя—>Вставить;
    • в диалоговом окне Вставка имени выберите имя блока ячеек Отлично и щелкните на кнопке <ОК>;
    • повторите аналогичные действия для подсчета количества других оценок.
  9. Подсчитайте ИТОГО – количество всех полученных оценок, используя кнопку Автосумма на стандартной панели инструментов.
    Кнопка Автосумма – это символ Σ на стандартной панели инструментов. Если его нет, необходимо выполнить Вид —> Панели инструментов—> поставить флажок на кнопку Стандартная.
    Для подсчета ИТОГО:
    • щелкнуть на ячейку, где должен быть результат ИТОГО;
    • щелкнуть на кнопку Автосумма;
    • выделить группу ячеек с количеством всех оценок;
    • <Enter>.

Задание № 8. Определить, в какой из заданных интервалов попадает зарплата каждого сотрудника НИИ, представленная в табл. 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>;
    • щелкните на кнопке <ОК>.
  5. Для остальных интервалов аналогично, только будут другие номера ячеек 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

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