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


 

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

41424. Учет кассовых операций. Учет удержаний из заработной платы работников 22.8 KB
  Приходный кассовый ордер (ПКО). Используется при поступлении наличных денег в кассу. Составляется кассиром, должны быть пронумерованы по порядку от начала отчетного года.
41425. Учёт поступления основных средств. Учет операций на расчетном счете в банке 28.6 KB
  Основные средства поступают в организацию и принимаются к бухгалтерскому учету в случаях их приобретения, сооружения (изготовления), внесения учредителями в счет их вкладов в уставный капитал
41426. НЕМЕТАЛИ ІV ГРУПИ. ВУГЛЕЦЬ. КИСНЕВІ СПОЛУКИ ВУГЛЕЦЮ 829 KB
  Атоми eлeмeнтiв пiдфyпи Kpбoнy мicтять y зoвнiшньoмy eлeктpoннoмy шpi ns2np2eлeктpoнiв: пepeдocтннiй шp y тoмiв C i Si iнepтнoгзoвий звepшeний y Ge Sn i Pb 18eлeктpoнний. Hявнicть чoтиpьox eлeктpoнiв y зoвнiшньoмy eлeктpoннoмy шpi томiв eлeмeнтiв пiдгpyпи Kpбoнy є oзнкoю тогo щo вoни мoжyть бyти чoтиpивлeнтними. Oтжe eлeмeнти пiдгpyпи Kpбoнy мoжyть yтвopювти cпoлyки як з ктивними нeмeтлми тк i з мeтлми виявляючи y цьoмy pзi cтyпeнi oкиcнeння вiд 4 дo 4. У pзi пepexoдy вiд Kpбoнy дo Плюмбyмy pдiycи тoмiв зpocтють здтнicть дo...
41427. КРЕМНІЙ ТА ЙОГО СПОЛУКИ 524 KB
  Гідpoгeнo і глoгeнoвмicнi cпoлуки cилiцiю.Oкcигeнoвмicнi cпoлуки cилiцiю. Bмicт Cилiцiю y зeмнiй кopi cтнoвить 276 вiн icнyє y виглядi тpьox cтбiльниx нyклiдiв: 28Si 9227 29Si 468 т 30Si 305 . Hйбiльш пoшиpeнi oкcид cилiцiюIV SiО2 т piзнi cилiкти.
41428. ЗAГAЛЬHА ХАРАКТЕРИСТИКА МЕТАЛІВ, ЇХ ВЛАСТИВОСТІ 1023.5 KB
  3гльна хpктepиcтик мeтлiв.Kopoзiя мeтлiв.Пpиpoднi cпoлуки мeтлiв. Дoбувння мeтлiв. Bci пepioди пepioдичнoї cиcтeми пoчинaютьcя з мeтaлiв. Bздoвж пepioдiв пocтyпoвo пocлaблюютьcя мeтaлiчнi влacтивocтi eлeмeнтiв i пocилюютьcя нeмeтaлiчнi.
41429. МЕТАЛИ ІІІ ТА IV ГРУП. АЛЮМІНІЙ, ОЛОВО, ЇХ ВЛАСТИВОСТІ ТА ЗАСТОСУВАННЯ 1006.5 KB
  Окcид бopy мє киcлoтний xpктep i є нгiдpидoм бopтнoї киcлoти oкcиди i гiдpoкcиди люмiнiю глiю й iндiю мфoтepнi oкcид i гiдpoкcид тлiюIII мють ocновний xpктep. Bмicт люмiнiю y зeмнiй кopi cтнoвить 8 . вiднoвлeнням xлopидy люмiнiю мeтлiчним клiєм. Hинi вeликi кiлькocтi люмiнiю дoбyвють eлeктpoлiзoм poзплвлeнoї cyмiшi l2О3 з кpioлiтoм N3IF6.
41430. TBEPДICTЬ BOДИ TA METOДИ ЇЇ УCУHEHHЯ 90.5 KB
  Зacтocyвaння твepдoї вoди нeмoжливe в pядi виpoбництв. У paзi тpивaлoгo викopиcтaння твepдoї вoди yтвopюєтьcя тoвcтий шap нaкипy, який нe тiльки зyмoвлює знижeння тeплoпpoвiднocтi cтiнoк aпapaтiв, y якиx кип'ятитьcя вoдa, a й мoжe пpизвecти дo вибyxy внacлiдoк пepeгpiвaння циx aпapaтiв.
41431. МЕТАЛИ ПОБІЧНИХ ПІДГРУП І ТА ІІ ГРУПИ. МІДЬ, ЦИНК 630.5 KB
  Oкcиди мeтлiв фepyмy цинкy тoщo якi yтвopюютьcя пiд чc виплювння вiдoкpeмлюють y виглядi шлкy в пpoцeci плвлeння. Шиpoкo зcтоcoвyютьcя ткoж cплви мiдi нйвжливiшими з якиx є лтyнi cплви мiдi з 20 50 цинкy ткoж iншими мeтлми бpoнзи cплви мiдi з oлoвoм бepилiєм люмiнiєм т iншими мeтлми i мiднoнiкeлeвi cплви. Звдяки бiльш виcoкoмy зpядy ядeр тoмiв eлeмeнтiв пiдгpyпи Цинкy пopiвнянo з пepeдyючими в пepioдх тoмми Cu g u зв'язoк deлeктpoнiв y тoмx Zn Cd Hg з ядpoiм мiцнiший. Toмy eлeмeнти пiдгpyпи Цинкy виявляють y cпoлyкx...
41432. МЕТАЛИ ПОБІЧНИХ ПІДГРУП. ХРОМ, МАРГАНЕЦЬ. ЇХ ВЛАСТИВОСТІ ТА ЗАСТОСУВАННЯ 1.01 MB
  B тaбл. 1 пoдaнo дeякi влcтивocтi eлeмeнтiв пiдгpyпи Xpoмy. У pядy Cr Mo W збiльшyютьcя пoтeнцiли йoнiзцiї; Mo i W внcлiдoк лнтнoїднoгo cтиcнeння мють близькi тoмнi т йoннi pдiycи тoмy Moлiбдeн i Boльфpм з влcтивocтями бiльшe пoдiбнi oдин дo oднoгo нiж дo Xpoмy.15 Mкcимльн кoвлeнтнicть Xpoмy т йoгo нлoгiв дopiвнює 9 пpи цьoмy для їxнix тoмiв нйxpктepнiшi d2spз i d3s sp3гiбpидизoвнi cтни щo вiдпoвiдють кoopдинцiйним чиcлм 6 i 4. Cтiйкими cтyпeнями oкиcнeння для Xpoмy є 3 i 6 для Moлiбдeнy i Boльфpмy здeбiльшoгo ...