36730

Работа с формулами в Excel

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

Информатика, кибернетика и программирование

Создайте таблицу содержащую следующие поля: № п п Фамилия Информатика История Психология Математика Иностранный язык Средний балл за сессию Средний балл группы за сессию 2. Заполните таблицу данными. Рассчитайте средний балл за сессию для всей группы В результате проделанной работы вы должны получить примерно такую таблицу как на рис. Для назначения стипендии с помощью Расширенного фильтра выберите в отдельную таблицу студентов сдавших сессию на 4 и 5.

Русский

2013-09-23

144.5 KB

21 чел.

PAGE  4

Excel

Лабораторная работа № 2

Лабораторная работа № 2

Работа с формулами  в Excel

Создание формул

! Все формулы в Excel должны начинать со знака равенства

Задание 1.

1. Создайте таблицу, содержащую следующие поля:

№ п/п

Фамилия

Информатика

История

Психология

Математика

Иностранный язык

Средний балл за сессию

Средний балл группы за сессию

2. Заполните таблицу данными. Поле Фамилия заполните, воспользовавшись функцией автозаполнения.  Для этого используйте список фамилий созданный в 1 лабораторной работе.  

3. Рассчитайте Средний балл за сессию каждого студента группы. Для этого:

  •  Выделите пустую ячейку в поле Средний балл за сессию напротив фамилии первого студента списка.
  •  Нажмите кнопку Мастер функций на стандартной панели инструментов или выполните команду меню Вставка – Функция
  •  В первом окне диалога Мастер функций выберите Статистические в списке Категория.
  •  Выберите СРЗНАЧ в списке Функций. Нажмите кнопку ОК.
  •  В появившемся диалоговом окне укажите диапазон ячеек, среди которых надо найти среднее значение и нажмите ОК.

4. Рассчитайте средний балл за сессию для всей группы

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

№ п/п

Фамилия

Информатика

История

Психология

Математика

Иностранный язык

Средний балл за сессию

Средний балл группы за сессию

1

Иванов

5

4

5

4

4

4,4

4,12

2

Анисимов

5

5

5

5

5

5

3

Попов

4

4

4

3

5

4

4

Воронова

4

5

4

4

4

4,2

5

Щербакова

4

4

4

4

4

4

6

Ворошилов

3

3

3

4

3

3,2

7

Боркут

5

5

4

4

3

4,2

8

Бореев

4

3

3

2

2

2,8

9

Балаев

3

5

5

5

4

4,4

10

Акимова

5

5

5

5

5

5

5. Для назначения стипендии с помощью Расширенного фильтра выберите в отдельную таблицу студентов сдавших сессию на 4 и 5. (В таблице Условий используйте условие >=4).

6. Добавьте к полученной таблице поля Коэффициент и Стипендия и рассчитайте стипендию для студентов группы. Для этого:

  •  Отсортируйте таблицу в порядке возрастания Среднего балла за сессию
  •  Проставьте коэффициенты студентам группы:  коэффициент 1 – средний балл = 4;

коэффициент 1,2 – средний балл > 4, но < 5;

коэффициент 1,5 – средний балл = 5;

  •  В любую ячейку, не входящую в таблицу занесите исходную сумму для расчета стипендии, например 350
  •  Для первого студента в таблице рассчитайте стипендию по формуле расчетная сумма * коэффициент, используя абсолютные и относительные ссылки на ячейки. Относительная ссылка задается адресом ячейки, абсолютная ссылка содержит в записи адреса значок  $ перед буквой столбца и цифрой строки. Например: относительная ссылка – A2, D4; абсолютная ссылка - $A$2; $D$4. В формуле для расчета стипендии относительная ссылка задается для ячейки, содержащей коэффициент, а абсолютная – для адреса ячейки с расчетной суммой.
  •  Воспользовавшись функцией автозаполнения  скопируйте полученную формулу  для расчета стипендии остальных студентов группы. Обратите внимание, что ссылка на ячейку с абсолютной адресацией при копировании не изменяется.

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

  •  Выделите пустую ячейку под последним значением столбца Стипендия
  •  Нажмите на кнопку Автосуммирование на стандартной панели инструментов
  •  Проверьте, правильно ли выделен диапазон ячеек для суммирования или выделите нужный диапазон мышью
  •  Нажмите Enter

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

 

Задание 2. Присваивание имен ячейкам и диапазонам ячеек

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

  1.  Присвойте Имя диапазону ячеек, содержащих суммы назначенной стипендии. Для этого:
    •  Выделите диапазон ячеек, содержащий суммы стипендий студентов в столбце Стипендия
    •  Выберите в меню Вставка команду Имя – Присвоить
    •  В появившемся окне Присвоение имени будет выбрано по умолчанию название столбца Стипендия (Примечание: при желании ячейкам можно присвоить другое имя)
    •  Нажмите кнопку Добавить, а затем ОК.
    •  Обратите внимание, что в Поле имени диапазону присвоилось имя Стипендия.
  2.  Рассчитайте общую сумму, потраченную на стипендию, используя в формуле суммирования имя диапазона Стипендия. Для этого, активизировав функцию Автосуммирования на панели инструментов Стандартная, вместо указания диапазона ячеек для суммирования запишите Имя диапазона. Формула должна выглядеть следующим образом: =Сумм(Стипендия). Нажмите Enter.

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

  1.  Попробуйте получить Сумму стипендии группы на других листах Excel, используя в формуле суммирования Имя диапазона.

Задание 3. Составление  платежной ведомости.

  1.  Скопируйте, полученную вами после выполнения Задания 1 таблицу на Лист 2 следующим образом:
    •  Выделите таблицу, не захватывая строку Общая сумма стипендии, выберите в меню Правка команду Копировать;
    •  Прейдите на Лист 2;
    •  Установите курсор в ячейку А1 и выполните команду Специальная вставка из меню Правка. В появившемся окне выберите пункт Значения и нажмите ОК;
  2.  Так как данные ячеек с экзаменационными оценками, средним баллом и коэффициентом в платежной ведомости не понадобятся, то скроем их. Для этого выделим эти столбцы (от столбца C до столбца I) и выполним команду меню Формат – Столбец – Скрыть.
  3.  Озаглавьте таблицу «Платежная ведомость за февраль 2002 года». Для этого добавьте одну пустую строку перед таблицей следующим образом: Выделите мышью строку 1, вызовите щелчком правой клавиши мыши контекстное меню и выберите команду Добавить ячейки.
  4.  Добавьте к таблице еще три поля: Проф. Взнос, К выдаче, Подпись.
  5.  Рассчитайте профсоюзный взнос для каждого студента, если известно, что он составляет 1 % от начисленной суммы.
  6.  Рассчитайте сумму к выдаче: Сумма начислено – Проф. взнос
  7.  Добавьте к таблице строку ИТОГО и  подсчитайте итоги по всем полям, используя Массивы.

Массивы можно использовать для создания формул, которые возвращают некоторое множество результатов. Формула массива действует на нескольких множествах значений. Диапазон массива – это блок ячеек, который имеет общую формулу массива.

Используя формулу массивов, подсчитаем сумму значений в строках для столбцов Стипендия, Проф. взнос и К выдаче Рис.3. Для этого:

  •  Выделите диапазон   J8 : L8
  •  Введите =J3:L3+J4:L4+J5:L5+J6:L6+J7:L7
  •  Нажмите Ctrl-Shift-Enter

Рис. 3

Задание 4. Подсчет промежуточных итогов

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

Фамилия

группа

10.фев

10.мар

10.апр

10.май

10.июн

Итого:

  1.  Поля, содержащие дату выплаты стипендии, заполнить, используя команды опции Прогрессия. Для этого:
    •  Внесите в ячейку C2 дату выплаты стипендии (Например: 10.02.2002).
    •  Выделите блок ячеек для заполнения C2 : G2
    •  Выполните команду Правка – Заполнить – Прогрессия
    •  Откроется диалоговое окно Прогрессия, где сделайте необходимые установки: Тип – Даты, Единицы – Месяц, Шаг – 1.
    •  Нажмите ОК
  2.  Установите формат для даты в окне Формат ячеек, вызвав его  командой меню Формат – Ячейки – вкладка Число.
  3.  Заполните таблицу данными. Поля Фамилия суммы выплат по месяцам скопируйте из таблицы, полученной в Задании 3.
  4.  Распределите студентов по группам (А, Б, В), произведите сортировку таблицы в порядке возрастания по полю Группа.
  5.  Посчитайте колонку Итого, воспользовавшись функцией Суммирования.
  6.  Подведите промежуточные Итоги в каждой группе по полю Итого. Для этого:
  •  Установите курсор мыши в пределах таблицы;
  •  Выполните команду меню Данные – Итоги,
  •  В появившемся диалоговом окне Промежуточные итоги сделайте следующие установки: в поле При каждом изменении в  выберите Группа, в поле Операция выберите Сумма, в поле Добавить итоги по: поставьте птичку напротив поля Итого
  •  Нажмите ОК
  1.  Добавьте промежуточные Итоги по полям за каждый месяц выплаты стипендии. Для этого, выполнив команду Данные – Итоги, установите птички в поле Добавить итоги по: напротив дат выплаты за каждый месяц.
  2.  Нажмите ОК.

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

Рис. 4

Структурирование рабочих листов в Excel

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

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

  1.  Создайте таблицу, подобную таблице Рис.1. Для ее создания используйте таблицы созданные в лабораторной работе № 2.

Рис. 5.

Примечание: Итоговые данные начислений стипендии по кварталам и по группам рассчитываются по формуле Суммирования.

  1.  Чтобы создать структуру для таблицы, сначала выделите ее. Затем в меню Данные выберите команду Группа и структураСоздание структуры.

В результате проделанных действий ваша таблица будет структурирована

  1.  Используя, появившиеся кнопки Скрыть детали , Отобразить детали , создайте итоговую таблицу начисления стипендий за год, как показано на Рис. 6

  1.  Excel может автоматически применять определенный набор стилей к ячейкам, находящимся на различных уровнях структуры. Для того чтобы применить стили к созданной структурированной таблице, нужно выделить ее. Затем, выбрав в меню ДанныеГруппа и структура пункт Настройка, установить флажок напротив команды Автоматические стили и нажать кнопку Создать.
  2.  Для удаления структуры в меню Данные выбирают команду Группа и структураУдалить структуру.

Сохраните результаты проделанной работы в своей папке под названием Лабораторная работа 2


 

А также другие работы, которые могут Вас заинтересовать

69914. Расчет ускорения свободного падения при помощи оборотного и математического маятников 287.5 KB
  Цель работы: экспериментально определить ускорение свободного падения с помощью физического и математического маятников. Оборудование и принадлежности: установка с физическим и математическим маятником, секундомер, линейка с миллиметровыми делениями.
69915. Исследование и расчет однофазных цепей синусоидального тока 84 KB
  Цель работы: Определение параметров последовательной схемы замещения приемников энергии. Экспериментальное исследование и расчет цепей однофазного синусоидального тока с последовательным, параллельным и смешанным соединением приемников.
69916. Основные требования, предъявляемые к конструкции деталей машин 95.5 KB
  Основные требования предъявляемые к конструкции деталей машин Совершенство конструкции детали оценивают по ее надежности и экономичности. Основные критерии работоспособности и расчета деталей машин Для того чтобы быть надежными детали прежде всего должны быть работоспособными...
69918. Постиндустриальное общество, информационная революция, глобализация и международные отношения 43.56 KB
  В сфере производства формирование международных интегрированных производств на базе транснациональных корпораций. Основной характеристикой международных отношений с момента их исторического возникновения является постоянная трансформация.
69919. Предмет и задачи информатики 102 KB
  Наскальная живопись клинопись устная речь музыкальные звуки нотные знаки для их записи алфавит телеграф радио телефон телевидение компьютеры вот лишь некоторые звенья цепи попыток совершенствовать способы получения сохранения обработки и передачи информации.
69920. История как наука 63.5 KB
  Историческая наука: предмет особенности функции. Историческая наука: предмет особенности функции. Предмет исторической науки прошлое человеческого общества Особенности исторической науки: гуманитарная наука субъективность исторического знания тесная связь истории с политикой...
69921. Предмет и задачи истории как науки 83 KB
  Превращение истории из отрасли культуры в науку представляло собой довольно длительный процесс и завершилось на рубеже XIX-XX вв. Объект изучения для истории вся совокупность фактов характеризующих жизнь общества и в прошлом и в настоящем.