36730

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

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

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

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

Русский

2013-09-23

144.5 KB

20 чел.

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


 

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

81987. Я – перша квіточка весни 91 KB
  Хто ж із них переможе? Цього ми поки що не знаємо... Але, напевно, кожен із нетерпінням чекає на початок конкурсу. Ведуча 2. Насамперед потрібно визначитись, в якому порядку учасниці будуть змагатися, потрібно провести жеребкування.
81988. Сценарій конкурсу «Поетична весна» 311 KB
  Голос за кулісами - Багатий на дива та містерії наш Всесвіт, прекрасний і величний у своїй таїні, щедрий легендами та зоряницями, що ніби разочки намист оповили неймовірним сяйвом його безмежність.
81989. А вже весна, а вже красна… 80 KB
  Показати, як поети і письменники засобами художнього слова розкривають багатство і красу навколишнього світу; розвивати навички виразного читання, формувати уміння робити посильні висновки з прочитаного, побаченого, почутого; збагачувати лексичний словник учнів...
81990. Бринить струною гілочка весни. (Весна у природі) 61.5 KB
  Закріплення елементарних уявлень про найхарактерніші ознаки весни в живій і неживій природі, які можна виявити в процесі спостережень, а саме: з пробудженням рослин, з поведінкою перелітних птахів; показати, як зміни в неживій природі впливають на живу природу; поповнювати знання учнів...
81991. Зігріємо землю своєю любов’ю, для наших нащадків її збережемо 231 KB
  Мета. Поглиблювати знання учнів про природу, її красу та багатства, сприяти розумінню необхідності захищати і берегти навколишнє середовище, виховувати любов і повагу до рідної землі, трепетне ставлення до всього живого на ній.
81992. РАЗРАБОТКА СИСТЕМЫ БЮДЖЕТИРОВАНИЯ НА БАЗЕ 1С:ПРЕДПРИЯТИЕ 8.0: ОБМЕН ИНФОРМАЦИЕЙ С БУХГАЛТЕРСКОЙ КОНФИГУРАЦИЕЙ, РАЗРАБОТКА БИЗНЕС-ПРОЦЕССОВ 888 KB
  Созданы обработки для обмена данными между разрабатываемой конфигураций и стандартной конфигурацией 1С:Бухгалтерия, разработаны бизнес-процессы, необходимые для формирования бюджета.
81993. Стежинами рідного міста 155 KB
  Познайомити учнів з головними історичними подіями в процесі розвитку рідного міста. Розвивати зв’язне мовлення, пізнавальний інтерес, уміння робити висновки. Виховувати патріотичні почуття, бажання набувати нові знання.
81994. Дзеркало людської душі 46.51 KB
  На початку виховної години для розвитку креативного мислення проводиться мозковий штурм Вихователь пропонує дітям відгадати що в неї в подарунковому пакеті пропонуючи підказки з історії виникнення дзеркала його форми і де воно зустрічається в літературі.
81995. ЛЮБОВ – ЦЕ ДАР. І БОГ САМ ВИБИРА, ХТО ЗАСЛУЖИВ ОЦЕ ПІЗНАТИ ДИВО 42.5 KB
  Мета: поспілкуватися з учнями про кохання, про те, що вважається природним і що є небажаним у взаєминах молоді; зорієнтувати учнів на толерантне ставлення до вираження почуттів протилежними статями; допомогти учням розібратися у собі, підготувати до майбутнього сімейного життя.