Новости

Основы создания документов в табличном процессоре Microsoft EXCEL

Работа добавлена:






Основы создания документов в табличном процессоре Microsoft EXCEL на http://mirrorref.ru

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

«ОРЕНБУРГСКИЙ ГОСУДАРСТВЕННЫЙ АГРАРНЫЙ УНИВЕРСИТЕТ»

Кафедра ИТ в экономике

Н.В. Андреева, В.В. Кучерова, А.А. Попов

основы создания документов

в ТАБЛИЧНОМ ПРОЦЕССОРЕMicrosoftEXCEL

Учебно-практическое пособие для студентов экономических специальностей очной формы обучения

Оренбург

Издательский центр

2010

УДК 002

А - 65

Рекомендовано к печати редакционно-издательским советом Оренбургского государственного аграрного университета (председатель совета – профессорВ.В. Каракулев).

Рассмотрено и рекомендовано к печати на заседании методической комиссии экономического факультета (протокол № 2 от 19 октября 2009 г.).

РЕЦЕНЗЕНТЫ:

доктор технических наук, профессор, зав. кафедрой информационных

систем и технологий ОГУВ.И. Чепасов;

кандидат технических наук, доцент, зав. кафедрой информатики и информационного обеспечения ОГАУА.С. Боровский

Андреева, Н.В.

Основы создания документов в табличном процессоре MicrosoftExcel: учебно-практическое пособие для студентов экономических специальностей очной формы обучения / Н.В. Андреева, В.В. Кучерова, А.А. Попов. – Оренбург: Издательский центр ОГАУ, 2010. – 77 с.

Учебно-практическое пособие предназначено для приобретения практических навыков при создании табличных документов с помощьюMicrosoft Excel. Предлагаемый материал содержит теоретические вопросы и практические задания, которые снабжены подробными указаниями для исполнения. Для закрепления и проверки полученных навыков практикум содержит дополнительные задания.

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

Н.В. Андреева, В.В. Кучерова, А.А. Попов, 2010

Издательский центр ОГАУ, 2010

СОДЕРЖАНИЕ

Введение.4

  1. Структура электронной таблицы.5
  2. Создание и заполнение таблицы постоянными данными и

формулами19

  1. Построение, редактирование и форматирование диаграмм26
  2. Применение стандартных функций в таблице36
  3. Связанные таблицы. Расчет промежуточных итогов в

электронных таблицах45

  1. Подбор параметра. Организация обратного расчета55
  2. Задачи оптимизации (поиск решения)60
  3. Связи между файлами и консолидация данных66
  4. Сводные таблицы73
  5. Комплексное использование приложенийMicrosoftOffice

для создания документов76

Коротко о главном79

Литература81

ВВЕДЕНИЕ

Информационная технология (ИТ) – это процесс, включающий совокупность способов сбора, хранения, обработки и передачи информации на основе применения средств вычислительной техники. В качестве инструментария ИТ выступают такие программные средства, как текстовый процессор, электронные таблицы, системы управления базами данных, издательские системы.

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

Табличный процессор (ТП) – прикладная программа, позволяющая автоматизировать труд экономистов, бухгалтеров и др., которым приходится работать с информацией, представленной в виде разнообразных таблиц.

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

Задача: освоить базовые технологии работы в табличном процессореMicrosoftExcel корпорацииMicrosoft для персональныхIBM-совместимых компьютеров.

Знать: архитектуру современного компьютера и принципы его работы, основные элементы программного обеспечения.

Уметь: создавать и редактировать таблицы, производить расчеты по формулам, представлять данные в виде диаграмм, осуществлять структурирование данных, делать выборку из больших таблиц, создавать консолидированные таблицы.

Владеть: специальной компьютерной терминологией и лексикой, навыками самостоятельного овладения новыми знаниями

Практическая работа1

Тема:Структура электронной таблицы

Цель работы:познакомиться с основными понятиями электронной таблицы. Освоить основные приемы заполнения и редактирования таблицы. Научиться загружать и сохранять таблицы.

Общие сведения

ЗапускEXCEL можно осуществить одним из следующих способов.

  • В меню «Пуск» выберите пункт «Создать документMicrosoftOffice». В открывшемся окне выберите вкладку «Общие» и дважды щелкните на пиктограмме «Новая книга».
  • В меню «Пуск» выберите пункт «Программы», «MicrosoftExcel».
  • В меню «Пуск» выберите пункт «Выполнить». В открывшемся окне «Запуск программы» в строке «Открыть» наберитеExcel и щелкните по кнопкеOK.
  • Дважды щелкните мышью на пиктограмме «MicrosoftExcel» на рабочем столеWindows.
  • ЗапуститьExcel можно с одновременным открытием файла, с которым недавно работали. В меню «Пуск» выберите пункт «Документы», укажитеимя файла, который нужно открыть.

ПунктДокументы служит для вызова меню, в котором находится 15 имен документов, открывавшихся и редактировавшихся пользователем при работе сWindows.

Первоначальные сведения о табличном процессореExcel

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

Первым табличным редактором, получившим широкое распространение, сталLotus 1-2-3, разработанный в 1982г. для компьютеров типаIBM. Этот табличный процессор интегрировал в своем составе не только набор обычных инструментов, но и графику, а также возможность работы с системами управления базами данных.

В 1987г. появляется табличный процессорExcel фирмыMicrosoft. Эта программа предложила более простой графический интерфейс в комбинации с ниспадающими меню, значительно расширив при этом функциональные возможности пакета и повысив качество выходной информации. Разработчики максимально облегчили пользователю освоение программы и работу с ней. Благодаря этомуExcel завоевала популярность среди широкого круга пользователей.

Электронная таблица – компьютерный эквивалент обычной таблицы, в клетках (ячейках) которой записаны данные различных типов: тексты, даты, формулы, числа.

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

Электронные таблицы используются для:

  • подготовки табличных документов, обработки сводок, составления реестров и прочих документов;
  • проведения анализа с использованием механизмов поиска и сортировки данных;
  • проведения однотипных расчетов над большими наборами данных;
  • создания баз данных;
  • автоматизации итоговых вычислений;
  • построения диаграмм и графиков по имеющимся данным и т.д.

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

Файлы, хранящие электронные таблицы, имеют расширениеxls. Один файл может хранить многотабличнуюкнигу, содержащую несколько листов-таблиц, а также листов-диаграмм.

Структура электронной таблицы

Строка заголовка содержит название документа и кнопки управления окном.

Строка меню содержит основные команды управления электронной таблицей и представляет собой иерархическую систему команд.

Панель инструментовсодержит пиктограммы для вызова наиболее часто выполняемых команд. Среди них есть команды как характерные для многих приложенийWindows, так и специфические дляMSExcel.

Строка формул предназначена для отражения вводимых в текущую ячейку данных. Адрес текущей ячейки отражен в левой части строки (отдельное окошко). В этой строке можно просмотреть и отредактировать хранимую в ячейке формулу; в самой ячейке пользователь при этом может видеть результат вычисления по формуле.

Рабочее поле содержит главную часть электронной таблицы – ячейки.

Табличный курсор выделяет текущую ячейку. ВMSExcel максимальный номер строки равен 65536, а последний столбец имеет имяIV (всего 256 столбцов).

Строка состояния отражает текущий режим работы табличного процессора. Здесь же выводятся сообщения пользователю о возможных действиях при данном состоянии таблицы. Например, когдаEXCEL ожидает ввода данных, то находится в режиме «ГОТОВО» и индикатор режима показываетГОТОВО.

Вертикальная и горизонтальная полосы прокрутки предназначены для перемещения окна по электронной таблице.

Рисунок 1.1 Интерфейс стандартной рабочей книги с листами

Настройка экрана. Работа с меню

Чтобы войти в меню, необходимо нажать клавишу <Alt> или <F10>. После этого одно слово в меню будет выделено подсветкой. При помощи клавиш <> и <> выбирается нужный пункт в меню. При нажатии клавиши <Enter> раскрывается подменю. Перемещение по подменю осуществляется клавишами <> и <>, а выбор команды фиксируется нажатием <Enter>. Выход из меню осуществляется нажатием клавиши <Esc>.

С менюExcel удобно работать при помощи мыши. Выбрав необходимый пункт, нужно навести на него курсор и сделать щелчок по левой кнопке мыши.

Щелчком по левой клавише мыши выбираются необходимые команды подменю и раскрываются вкладки, а также устанавливаются флажки.

Щелчок мыши за пределами меню приводят к выходу из него и закрытию подменю.

Выполните следующие действия.

1. Щелкните по кнопкеРазвернутьв зоне заголовка, если после вызова программы ее окно не занимает всего экрана. Окно документа также должно быть полноэкранным.

2.В менюВидвыберите командуПанели инструментов. В раскрывшемся диалоговом окне установите флажки рядом с названиями панелей инструментовСтандартная иФорматирование, если они отсутствуют.

Установка и снятие флажка производится щелчком мыши в соответствующем окне.

Для вывода панелей инструментов вы можете также использовать контекстно-зависимое меню, которое появляется после щелчка правой кнопкой мыши в зоне панелей инструментов.

3. В менюСервис выберите командуПараметры.

  • Раскройте вкладкуВид. Проверьте, установлены ли флажки следующих параметров:

в полеОтображать: строку формул; строку состояния;

в полеПараметры окна: горизонтальная полоса прокрутки; вертикальная полоса прокрутки; ярлычки листов; заголовки строк и столбцов; сетка.

Если нет, то произведите соответствующую установку.

Замечание: установка флажка на строкуФормулы обозначает режим отображения формул (т.е. вместо результата в таблице можно видеть введенную формулу).

  • Раскройте вкладкуОбщие. Установите следующие параметры:

стиль ссылок: А1, т.е. нет флажка.

Далее в тексте подобные действия по работе с меню будут описываться в краткой форме:

Меню/Сервис/Параметры/Вид/Строка формул.

Основные понятия электронных таблиц. Строки, столбцы, ячейки

Рабочее поле электронной таблицы состоит из строк и столбцов. Максимальное количество строк равно 65536, столбцов – 256.

Каждое пересечение строки и столбца образуют ячейку, в которую можно вводить данные (текст, число или формулы).

Номер строки определяет ряд в электронной таблице. Он обозначен на левой границе рабочего поля.

Буквы столбца определяют колонку в электронной таблице. Буквы находятся на верхней границе рабочего поля. Имена столбцов обозначаются в следующем порядке:AZ, затемAAAZ, затемBABZ и т.д.

Ячейка – первичный элемент таблицы, содержащий данные. Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки. Например, адрес В3 определяет ячейку на пересеченииСтолбца В иСтроки номера 3.

Указатель ячейки – светящийся прямоугольник, определяющий текущую ячейку. Указатель можно перемещать по таблице как при помощи клавиатуры, так и мышью.

Текущая ячейка помечена указателем. Ввод данных и некоторые другие действия по умолчанию относятся к текущей ячейке.

Блок (область) ячеек

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

Адрес блока состоит из координат противоположных углов, разделенных двоеточием, например,B13:C19,A12:D27.

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

Рабочий лист, книга

Окно с заголовком Книга1 состоит из нескольких рабочих листов (по умолчанию таких листов 3). При открытии рабочей книги автоматически загружаются все ее рабочие листы. На экране виден только один лист – верхний. Нижняя часть листа содержит ярлычки других листов. Щелкая мышью на ярлычках листов, можно перейти к другому листу.

Выделение столбцов, строк, блоков, таблицы

Для выделения с помощью мыши:

столбца – щелкнуть мышью на букве – имени столбца;

нескольких столбцов – не отпуская кнопку после щелчка, протянуть мышь;

строки – щелкнуть мышью на числе – номере строки;

несколько строк – не отпуская кнопку после щелчка, протянуть мышь;

блока – щелкнуть мышью на начальной ячейке блока и, не отпуская кнопку, протянуть мышь на последнюю ячейку;

рабочего листа – щелкнуть мышью на пересечении имен столбцов и строк (левый верхний угол таблицы).

Для выделения блока с помощью клавиатуры, необходимо, удерживая клавишу <Shift>, нажимать на соответствующие клавиши перемещения курсора или, нажав клавишу <F8>, войти в режим выделения и произвести выделение при помощи клавиш перемещения курсора.

<Esc> выход из режима выделения.

Для выделения нескольких несмежных блоков необходимо:

  • выделить первую ячейку или блок ячеек;
  • нажать и удержать клавишу <Ctrl>;
  • выделить следующую ячейку или блок и т.д.;
  • отпустить клавишу <Ctrl>.

Для снятия выделения достаточно щелкнуть мышью по любому невыделенному участку рабочего листа. Новое выделение снимает предыдущее.

Выполните следующие действия.

1. Сделайте ячейкуD4 текущей при помощи мыши.

2. Вернитесь в ячейку А1 при помощи клавиш перемещения курсора.

3. Сделайте текущим лист 3.

4. Вернитесь к листу 1.

5. Выделите строку 3.

6. Снимите выделение.

7. Выделите столбецD.

8. Выделите блокA2:E13 при помощи мыши.

9. Выделите столбцыA,B,C,D.

10. Снимите выделение.

11. Выделите блокC4:F13 при помощи клавиатуры.

12. Выделите рабочий лист.

13. Снимите выделение.

14. Выделите одновременно следующие блоки:F5:G10,H15:I15,C18:F20,H20.

Данные в ячейках таблицы

В работе с электронными таблицами можно выделить три основных типа данных:текст, числоиформула.С числом и текстом всё понятно: помещаете курсор в ячейку и вводите их с клавиатуры. Число, в отличие от текста, может участвовать в вычислительных операциях. Так, например, можно число 6 умножить на число 3 и получить результат вычислений. Но если вы попробуете из текста «Начисления» вычесть текст «Налоги», то получите сообщение об ошибке.

Числа разделяются на целые и вещественные. Вещественные числа можно записать двумя способами: в форме с фиксированной запятой и в экспоненциальной форме (в форме с плавающей запятой). Числовая константа в экспоненциальной форме трактуется как мантисса, умноженная на 10 в степени, равной порядку. Например, число 1000000 можно записать как 1Е+6, 0,0001 – 1Е-4

ЧисловMicrosoftExcel может состоять только из следующих символов: 0 123456789 + -(). Все другие комбинации, состоящие из цифр и нецифровых символов, интерпретируются как текст. Если перед числом стоит знак плюс (+), он игнорируется. Перед отрицательным числом необходимо ввести знак минус (-) или заключить его в круглые скобки (). В качестве десятичного разделителя используют запятую.

По умолчанию числа выравниваются в ячейке по правому краю. Это объясняется тем, что при размещении чисел друг под другом (в столбце таблицы) удобно иметь выравнивание по разрядам (единицы под единицами, десятки под десятками и т.д.).

ТекстомвMicrosoftExcel является любая последовательность, состоящая из цифр, пробелов и нецифровых символов, например, приведённые ниже записи обрабатываются как текст: 987$$78, 100 рублей.

По умолчанию текст выравнивается в ячейке по левому краю. Это объясняется традиционным способом письма (слева направо).

Формулы.Подформулойв электронной таблице понимают выражение, состоящее из операндов и операций. Формулы строятся как выражение для вычисления нового значения. Тип значения, полученного в результате вычисления по формуле, определяется типом операндов выражения. Формула всегда начинается с символа равно (=).

В качествеоперандовиспользуются:

  • числа;
    • тексты (вводятся в двойных кавычках, например, «Неявка»);
    • логические значения (например, ИСТИНА и ЛОЖЬ, условия типа А23=А45 и т.д.);
    • значения ошибки (типа #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!);
    • ссылки — адреса ячеек. При перечислении ссылки разделяются точкой с запятой, например:А4; С5; С10: Е20;
    • встроенные функцииExcel.

Операнды в формулах соединяются с помощью символовопераций:

  • арифметических операций: + (сложение), — (вычитание), / (деление), * (умножение), ^(возведение в степень);
  • операций отношения: > (больше),>=(не меньше), < (меньше), <= (не больше), = (равно), <> (не равно).

Формулы можно копировать в другие ячейки. При этом в зависимости от типа ссылок, входящих в копируемую формулу, осуществляется их настройка: автоматическая (для относительных ссылок) или полуавтоматическая (для частично абсолютных ссылок). Различают следующие типы ссылок:

  • относительныессылки, напримерА2илиС23,которые всегда изменяются так, чтобы отобразить правило их вхождения в формулу относительно ее нового местоположения;
  • абсолютныессылки, которые перед именем столбца и номером строки имеют символ$.Назначение абсолютной ссылки производится следующим образом: в строке ввода перед ссылкой устанавливается курсор и нажимается клавиша <F4>, например,$А$4.Можно сделать то же самое, вводя символ$с клавиатуры. При копировании абсолютные ссылки остаются неизменными;
  • частично абсолютныессылки, которые при копировании корректируются частично. Символ$стоит или перед именем столбца, или перед номером строки ($R2,F$5). Например, при копировании формулы, содержащей$F5,сохранится имя столбцаF,а номер строки будет изменен;
  • имена блоков,например,ЦЕНА.Имя связывается с данными блока, а не с его местоположением. Можно блок перенести в другое место, что не повлияет на его имя.

Формулы можно копировать в другие ячейки. При этом в зависимости от типа ссылок, входящих в копируемую формулу, осуществляется их корректировка: автоматическая (для относительных ссылок) или полуавтоматическая (для частично абсолютных ссылок).

Пример.На рис. 1.2 представлен результат копирования формул из одной ячейки в другую для трех вариантов ссылок: относительных, абсолютных, частично абсолютных.

A

B

C

D

E

F

G

1

10

10

10

6

2

100

100

100

7

3

110

110

110

8

4

0

5

6

5

4

9

1

5

9

2

3

0

3

0

3

6

8

110

11

Рисунок 1.2 – Иллюстрация правила изменения ссылок при копировании формул из одной ячейки в другую

Ввод данных

Для того, чтобы вести в ячейкучислоилитекст:

  • выделите ячейку, в которую необходимо ввести данные;
  • наберите число или текст;
  • нажмите клавишу{Enter}.

Следует иметь в виду, что в процессе набора в ячейке возникает мигающий текстовый курсор. Для того, чтобы выйти из текстового режима (в котором недоступны многие операции), необходимо зафиксировать данные одним из способов:

  • нажать клавишу{Enter};
  • щёлкнуть мышью другую ячейку;
  • воспользоваться стрелками управления курсором;
  • выбрать кнопку строки формул.

Если размер текста превышает размер ячейки, то:

  • текст занимает соседнюю ячейку (справа или слева, в зависимости от выравнивания текста), если она пустая;

  • на экране отображается только часть текста, помещающаяся в ячейке, если соседняя ячейка заполнена.

Для того, чтобы ввестиформулу, нужно:

  • выделить ячейку, в которую необходимо ввести формулу;
  • набрать формулу, начав набор со знака равенства (=);
  • нажать клавишу{Enter}.

В отличие от ввода текста и чисел, фиксировать формулу можно только двумя способами (вместо четырёх):

  • нажать клавишу{Enter};
  • выбрать кнопкустроки формул.

Это объясняется тем, что в процессе ввода формулы вместо набора адреса ячейки можно выполнить щелчок по этой ячейке и адрес автоматически пропишется в формулу. Так же действует и использование стрелок управления курсором.

Задание 1.1

1. Выделите ячейку А1 и введите любое число.

2. Нажмите {Enter} или стрелку вниз (оказались в ячейке А2).

3. Введите следующее число и повторите пункт 2. Таким образом введите пять чисел.

4. В ячейку А6 введите формулу для суммирования введённых чисел: = А1+А2+АЗ+А4+А5 и нажмите {Enter}. В ячейке А6 находится число, но если встать на эту ячейку, то в строке формул будет находиться сама формула.

Суммирование лучше выполнять по-другому, с привлечением стандартных функций Excel.

5. В ячейки B1:B5 скопируйте числа с ячеек А1:А5.

6. Выделите ячейки B1:B5 (это будет область суммирования). Нажмите кнопку суммирования (сигма) на панели инструментов.

Если перейти на любую из ячеек с В1 по В5 и ввести другое число, то сумма в ячейке В6 тут же изменится (после того, как закончен набор числа, необходимо нажать {Enter}, любую стрелку или щёлкнуть мышью по любой другой ячейке).

Если программа не в состоянии разобраться с формулой (например, вы пытаетесь разделить число на текст), появляется сообщение типа: «#ЗНАЧ!».

Задание 1.2

1. Создайте таблицу по образцу (рис. 1.3.).

2. В ячейку СЗ введите обмениваемую сумму.

3. В ячейку С4 введите формулу «=С2*СЗ» для вычисления суммы в рублях следующим образом:

Рисунок 1.3 – Таблица расчета курса в рублях

  • выделите ячейку С4 (ячейку, в которой будет размещён результат

вычислений по формуле);

  • введите с клавиатуры знак равенства «=»;
  • щёлкните по ячейке С2 (первый множитель), в формуле появится ссылка на эту ячейку (её адрес);
  • введите с клавиатуры знак умножения «*»;
  • щёлкните по ячейке СЗ (второй множитель), в формуле появится ссылка на эту ячейку (её адрес). В этот момент
  • в ячейке С4 набрана вся формула «=С2*СЗ»;
  • зафиксируйте формулу нажатием клавиши {Enter}. В ячейке С4 вместо введённой формулы появится число.

Измените число сдаваемых долларов, для этого:

  • выделите ячейку С3;
  • введите новое число;
  • зафиксируйте данные нажатием клавиши {Enter}. Проверьте, что сумма в рублях изменилась автоматически.

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

Для того, чтобы закончить оформление таблицы, осталось применить обрамление ячеек. Выделив блок ячеек В2:С4, выберите в менюФормат командуЯчейки и вкладкуГраница. Установите рамки «Внешние» и «Внутренние». Сравните результат.

Для того, чтобы очистить содержимое ячейки, воспользуйтесь клавишей {Delete}.

Чтобы полностью удалить содержимое ячейки (блока ячеек), достаточно выделить ячейку (блок ячеек) и нажать клавишу {Delete}.

Для удаления данных из таблицы с сохранением пустого места необходимо воспользоваться командами Правка, Очистить.

Форматирование символов вExcel

Для форматирования символов вExcel используются те же приёмы, что и в работе с редакторомWord. Можно выбирать шрифт и размер шрифта, оформлять символы полужирным стилем начертания и курсивом, применять подчёркивание, изменять цвет символов. Применять форматирование можно как к отдельным символам, так и к целой ячейке или диапазону ячеек. Всё зависит от того, какую область выделили перед форматированием. Для форматирования символов используют вкладкуШрифтменюФормат, Ячейки.

Форматирование ячеек

Знакомые вам кнопки выравнивания абзацев  используются и вExcel, только выравнивание происходит относительно ячейки. Набор команд менюФормат, Ячейкипозволяет осуществлять (рис. 1.4):

  • выравнивание —способ выравнивания данного в ячейке по горизонтали (по левому или правому краю, по значению, по центру выделения, по центру, по ширине, с заполнением) или по вертикали (по нижнему или верхнему краю, по центру или высоте);

Рисунок 1.4. – Формат ячеек

  • отображение —определяет, можно ли переносить в ячейке текст, по словам, разрешает или запрещает объединение ячеек, задает автоподбор ширины ячейки.
  • ВкладкаШрифтизменяет шрифт, начертание, размер, цвет, подчеркивание и эффекты текста в выделенных ячейках;
  • вкладкаГраницасоздает рамки (обрамление) вокруг выделенного блока ячеек;
  • вкладкаВидпозволяет задать закраску ячейки (цвет и узор);
  • вкладкаЗащитауправляет скрытием формул и блокировкой ячеек (запрет редактирования данных ячеек). Устанавливать защиту можно в любой момент, но действовать она будет только после того, когда введена защита листа или книги с помощью командыСервис, Защитить лист.

Задание 1.3

  1. В ячейке А1 напишите «Фамилия» (полужирный шрифт).
  2. В ячейку А2 поместите фамилию «Иванов», в ячейку В2 «Петров», в С2 «Сидоров».
  3. Выделите блок ячеек А1 – С1,начиная с ячейки А1.
  4. В диалоговом окнеФормат, Ячейки выберите вкладкуВыравнивание.
  5. В раскрывающемся спискеВыравнивание по горизонталивыберитеПо центруи установитеОбъединение ячеек(щёлкните мышью по соответствующему квадратику).
  6. Выделите ячейку А2.
  7. В диалоговом окнеФормат, Ячейкивыберите вкладкуВыравнивание.
  8. УстановитеОриентацию(-90) градусов.
  9. По аналогии отформатируйте текст в ячейкахB2 иC2 согласно рисунку.
  10. Выделите всю таблицу.
  11. В диалоговом окнеФормат, Ячейкивыберите вкладкуГраница.
  12. Установите тип линии и щёлкните по клавишамВнешниеиВнутренние.

Формат числа

В зависимости от решаемой задачи возникает необходимость применять различный формат числа. В каких-то ситуациях мы имеем дело с процентами. Оперируя крупными денежными единицами, удобно разделить числа на разряды (1 000 000), а возможно и установить наименование (р.) или определить число десятичных знаков (2,00). По умолчаниюExcel использует для чиселОбщий формат.

Общий числовой форматявляется числовым форматом по умолчанию для всех ячеек нового листа. В этом форматеMicrosoftExcel для вывода чисел использует целочисленный формат (1964), десятичный дробный (19,64) и экспоненциальный (1,64Е+03, т.е. 1,64*103), если числа длиннее ширины ячейки. Числа в общем формате выровнены по правому краю.

Таблица 1.1 Выбор формата числа

Категория

Результат отображения числа

Пример

Общий

Без использования специальных средств. Применяется для отображения как текстовых, так и числовых данных. Текст выравнивается по левому краю, число по правому

1245,56

1,23Е+11

Числовой

С разделителями групп разрядов, с выровненными разрядами и специальным выделением для отрицательных чисел

45789

-46789

Денежный

С выровненными разрядами, символами валют и специальным выделением для отрицательных чисел

34 45,45 р.

34 458,4 р.

Финансовый

С выровненными разрядами (по десятичному разделителю) и символами валют

34 56,8р.

67,9$

Дата

Как дата, время суток или время суток и даты одновременно

16.04.07

16 апр 06

Время

Как время суток

22:45

Процентный

В процентах от 1

45,6%

Дробный

С дробями

¾

Экспоненциальный

В научной нотации Е+

1,33Е+11

Текстовый

Как текст или обрабатывать как текст. Данные выглядят точно так же, как вводятся

987654

65,234

Дополнительный

Как почтовый индекс, телефонный или табельный номер. Удобен для работы с базами данных

113425

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

Задание 1.4

  1. Поместите в ячейку А1 число (-46738), в ячейки А2 и A3 число 765,676, в А4 - 15.06 (через точку), в А5 -0,3, в А6 - 0,5, в А7 - 4682920.
  2. Установите в ячейке А1Числовойформат так, чтобы отрицательные числа окрашивались красным цветом, знак минуса не ставился.
  3. В ячейке А2 установите форматДенежный,валюта $, количество знаков после запятой 0.
  4. В A3 то же самое, что и в А2, только валюта рубли.
  5. В А4формат Дата,месяц пишется прописью (15 июн).
  6. В А5Процентныйформат.
  7. В А6Дробный.
  8. В А7Дополнительный,как номер телефона.

Маркер заполнения

ВExcel существует интересная функция автозаполнения, которая позволяет вводить различные типовые последовательности (арифметическую и геометрическую прогрессии, даты, дни недели, месяца, года и т.д.).

Excel позволяет вводить также некоторые не типовые последовательности, если удается выделить какую-либо закономерность.

Задание 1.5.

1. В ячейку G10 занесите год – 1990.

2. В ячейку H10 занесите год – 1991.

3. Выделите блок G10:H10.

4. Укажите на маленький квадратик в правом нижнем углу ячейки H10 (экранный курсор превращается в маркер заполнения (черный крестик).

5. Нажмите левую клавишу мыши и, не отпуская ее, двигайте мышь вправо, пока рамка не охватит ячейки G10:M10. В результате ячейки заполнятся годами  с 1990 по 1996.

6. Введите в ячейки G11:M11 дни недели, начиная с понедельника.

7. Ведите в ячейки G12:M12 месяцы, начиная с января.

8. Введите в ячейки G13:M13 даты, начиная с 12 декабря.

Завершение работы вExcel

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

  • Сохранить – сохраняет рабочую книгу на диске для последующего использования.
  • Сохранить как… аналогичнаСохранению, но при этом позволяет поменять имя файла или записать его на другой диск.
  • Закрыть– убирает документ с экрана.
  • Создать – создает новую рабочую книгу (пустую или на основе указанного шаблона).
  • Открыть – возвращает рабочую книгу с диска на экран.

ДействияСоздать,Открыть,Сохранить закреплены за тремя первыми кнопками панелей инструментовСтандартная.

Для выхода изExcel можно воспользоваться одним из четырех способов:

  • Меню/Файл/Выход.
  • Системное меню – Команда Закрыть.
  • Клавиатура - <Alt>+<F4>.
  • Щелчок по кнопкев строке заголовка.

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

В результате выполнения работы №1 вы должны познакомиться с основными понятиями электронных таблиц и приобрести первые навыки работы сExcel.

Проверьте:

Знаете ли вы, что такое:

Умеете ли вы:

элементы окнаExcel;

строка;

столбец;

ячейка;

лист;

книга;

контекстно-зависимое меню;

панели инструментов.

работать с меню;

вводить текст, числа, формулы;

редактировать данные;

изменять размер строк и столбцов;

перемещать;

копировать;

заполнять и удалять;

сохранять таблицу;

закрывать, открывать;

производить настройку окнаExcel.

Если нет, то еще раз внимательно перечитайте соответствующие разделы работы.

Практическая работа 2

Тема: СОЗДАНИЕ И ЗАПОЛНЕНИЕ ТАБЛИЦЫ ПОСТОЯННЫМИ ДАННЫМИ И ФОРМУЛАМИ

Цель занятия:изучение информационной технологии использования встроенных вычислительных функцийExcel для финансового анализа.

Задание 2.1.Создать таблицу финансовой сводки за неделю, провести расчеты, построить диаграмму изменения финансового результата, выполнить фильтрацию данных.

Исходные данные представлены на рис. 2.1.

Порядок работы

1. Запустите редактор электронных таблицMicrosoftExcel и создайте новую электронную книгу.

Рисунок 2.1 Финансовая сводка за неделю.

  1. Введите заголовок таблицы «Финансовая сводка за неделю», начиная с ячейки А1.
  2. Для оформления шапки таблицы выделите ячейки на третьей строкеA3:D3 и создайте стиль для оформления. Для этого выполните командуФормат/Стильи в открывшемся окнеСтиль(рис. 2.2) наберите имя стиля «Шапка таблиц» и нажмите кнопкуИзменить.В открывшемся окне на вкладкеВыравниваниевыберите горизонтальное и вертикальное выравнивание — по центру (рис. 2.3), на вкладкеЧислоукажите формат —Текстовый.После этого нажмите кнопкиОК/Добавить/ОК.

4. На третьей строке введите названия колонок таблицы — «Дни недели», «Доход», «Расход», «Финансовый результат», далее заполните таблицу исходными данными согласно заданию 2.1.

Установите ширину столбцов таблицы в соответствии с рис. 2.1. Для этого:

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

Рисунок 2.2 – Создание стиля оформления шапки таблицы

Рисунок 2.3 – Форматирование ячеек – задание переноса по словам

Краткая справка.Можно изменить ширину столбца или строки иначе, если уже введен текст. Двойной щелчок левой кнопкой мыши на границе клетки с именем столбца (строки), в результате которого ширина столбца установится равной количеству позиций в самом длинном слове этого столбца.

5. Произведите расчеты в графе «Финансовый результат» по следующей формуле:

Финансовый результат = Доход - Расход,

для этого в ячейкеD4 наберите формулу:= В4-С4.

Краткая справка. Введите расчетную формулу только для расчета по строке «Понедельник», далее произведите автокопирование формулы (так как в графе «Расход» нет незаполненных данными ячеек, можно производить автокопирование двойным щелком мыши по маркеру автозаполнения в правом нижнем углу ячейки).

6. Для ячеек с результатом расчетов задайте формат «Денежный» с выделением отрицательных чисел красным цветом (рис. 2.4)(Формат/Ячейки/вкладкаЧисло/формат Денежный/отрицательные числакрасные.Число десятичных знаков задайте равное 2).

Обратите внимание, как изменился цвет отрицательных значений финансового результата на красный.

7. Рассчитайте средние значения дохода и расхода, пользуясь мастером функций (кнопкаfx).Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические». Для расчета функции СРЗНАЧ дохода установите курсор в соответствующей ячейке для расчета среднего значения (В11), запустите мастер функций(Вставка/Функция/КатегорияСтатистические/СРЗНАЧ)(рис. 2.5). В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В4:В10.

Аналогично рассчитайте «Среднее значение» расхода.

Рисунок 2.4. – Задание формата отрицательных чисел красным цветом

Рисунок 2.5 – Выбор функции расчета среднего значения

8. В ячейкеD13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкойАвтосуммирования() на панели инструментов или функцией СУММ (рис. 2.6). В качестве первого числа выделите группу ячеек с данными для расчета суммы —D4:D10.

Рисунок 2.6 – Задание интервала ячеек при суммировании функцией СУММ

Рисунок 2.7 – Таблица расчета финансового результата

9. Проведите форматирование заголовка таблицы. Для этого выделите интервал ячеек от А1 доD1, объедините их кнопкой панели инструментовОбъединить и поместить в центреили командой менюФормат/Ячейки/вкладка Выравнивание/отображениеОбъединение ячеек).Задайте начертание шрифта полужирное; цвет по вашему усмотрению.

Конечный вид таблицы приведен на рис. 2.7.

10. Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с использованием мастера диаграмм.

Для этого выделите интервал ячеек с данными финансового результата и выберите командуВставка/Диаграмма.На первом шаге работы с мастером диаграмм выберите тип диаграммы линейчатая; на втором шаге на вкладкеРядв окошкеПодписи осиХ укажите интервал ячеек с днями недели А4:А10 (рис. 2.8).

Рисунок 2.8 – Задание подписи осиX при построении диаграммы

Рисунок 2.9 – Конечный вид диаграммы

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

11. Произведите фильтрацию значений дохода, превышающих 4000 руб.

Краткая справка. В режиме фильтра в таблице видны только те данные, которые удовлетворяют некоторому критерию, при этом остальные строки скрыты.

Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командойДанные/Фильтр/Автофильтр.В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся значений этого поля. Выберите команду для фильтрацииУсловие.

В открывшемся окнеПользовательский автофильтрзадайте условие «Больше 4000» (рис.2.10). Произойдет отбор данных по заданному условию.

Проследите, как изменились вид таблицы (рис. 2.11) и построенная диаграмма.

12. Сохраните созданную электронную книгу в своей папке.

Рисунок 2.10 – Пользовательский автофильтр

Рисунок 2.11 – Вид таблицы после фильтрации данных

Дополнительные задания

Задание 1

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

Таблица 2.1 – Исходные данные для дополнительного задания

Задание 2

Заполнить таблицу 2.2, произвести расчеты, выделить максимальную, минимальную и среднюю суммы брака, а также средний процент брака; произвести фильтрацию данных по условию процента брака <8%, построить график отфильтрованных значений изменения суммы брака по месяцам. (Сумма брака = Процент брака* Сумма зарплаты).

Таблица 2.2 – Исходные данные для дополнительного задания

Задание 3

Заполнить таблицу 2.3, произвести расчеты, выделить максимальную и минимальную продажи (количество и сумму); произвести фильтрацию по цене, превышающей 9000 руб., построить гистограмму отфильтрованных значений изменения выручки по видам продукции.

Всего= Безналичные платежи + Наличные платежи

Выручка от продажи = Цена * Всего

Таблица 2.3 – Исходные данные для дополнительного задания

Задание 4

Построить таблицу 2.4, определить средний балл экзаменационной сессии по курсам (сумма произведений количества оценок и соответствующего балла, деленная на количество оценок) и по факультету. Полученные результаты округлить до одного десятичного знака после запятой с помощью функции =ОКРУГЛ и поместить в таблицу 2. Определить рейтинг каждого курса.

Таблица 2.4 Сведения о результатах экзаменационной сессии

1 курс

2 курс

3 курс

4 курс

5 курс

Балл

Кол-во оценок

Балл

Кол-во оценок

Балл

Кол-во оценок

Балл

Кол-во оценок

Балл

Кол-во оценок

5

23

5

27

5

32

5

28

5

34

4

57

4

60

4

58

4

63

4

62

3

18

3

14

3

10

3

9

3

6

2

7

2

9

2

4

2

1

2

Таблица 2.4 Средний балл по курсам и факультету

Курс

Средний балл

Ранг

Первый

Второй

Третий

Четвертый

Пятый

По факультету

Задание 5.

Рассчитать таблицу 2.5.

Введите произвольно случайные числа от 0 до 10 в диапазон А2:А20, для чего в ячейку А2 введите формулу = ЦЕЛОЕ(СЛЧИС()*10).

Таблица 2.5 – Исходные данные для дополнительного задания

Практическая работа 3

Тема:ПОСТРОЕНИЕ, РЕДАКТИРОВАНИЕ И ФОРМАТИРОВАНИЕ ДИАГРАММ

Цель: построение диаграмм различных типов, редактирование элементов диаграммы, форматирование области диаграммы и области построения диаграммы.

Общие сведения

Диаграммы –это графические представления данных. Они используются для анализа и сравнения данных, представления их в наглядном виде.

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

Различают два вида диаграмм:

внедренные диаграммы –сохраняются на рабочем листе вместе с данными;

диаграммные листы –диаграмма в формате полного экрана на новом листе.

Диаграмма создается с помощьюМастера диаграмм,вызываемого командойВставка, Диаграммаиликнопкой      на панелиСтандартнаялибо кнопкой         на панелиДиаграмма.

Мастер диаграммпозволяет строить диаграммы 14 стандартных типов плоскостного и объемного представления (собластями, линейчатая, гистограмма, график, кольцевая, лепестковая, точечная, пузырьковая, поверхностнаяи др.) и 22 нестандартных типа. Некоторые типы диаграмм представлены на рис. 3.1.

аб

вг

Рисунок 3.1 Примеры различных типов диаграмм Ехсеl: аобъемный вариант обычной гистограммы; б – лепестковая диаграмма; в – линейчатая диаграмма; гсмешанная диаграмма

Мастер диаграммосуществляет построение новой диаграммы в интерактивном режиме за четыре шага только для выделенногоблока ячеек – диапазона (области) данных для построения диаграммы.

Примечание.Блок ячеек может быть выделен как до вызоваМастера диаграмм,так и после его вызова.

Э т а п 1.Выбор типа и формата диаграммы. На этом этапе необходимо выбрать тип диаграммы (рис. 3.2) и задать (в окне справа) формат (вид), который делает ее более выразительной. После выбора надо нажать кнопку <Далее> и перейти на следующий этап.

Э т а п 2.Выбор и указание диапазона данных для построения диаграммы.На этом этапе задаётся диапазон данных, для которого будет построена диаграмма. Для этого в таблице с помощью переключателя (см. рис. 3.3) укажите расположение данных: по строкам или столбцам будет строится выбранный тип диаграммы. Далее с помощью мыши выделите необходимый блок ячеек, адрес которого автоматически отобразится в строкеДиапазон(рис. 3.3). В окне образца будет отображаться выбранный тип диаграммы для заданного диапазона данных.

Рисунок 3.2 – Диалоговое окноМастер диаграмм для выбора типа и формата диаграммы (этап 1)

Рисунок 3.3 – Диалоговое окноМастер диаграммдля задания диапазона данных диаграммы (этап 2)

Примечание.Указание диапазона данных, расположенных в несмежных рядах или столбцах, должно производиться при нажатой клавише <Сtrl>. В строкеДиапазонадреса несмежных интервалов данных будут разделяться точкой с запятой.

Э т а п 3.Задание параметров диаграммы

Задание параметров диаграммы осуществляется в окнах вкладокМастера диаграмм,представленного на рис. 3.4.

На вкладкеЗаголовкивводятся поочередно в соответствующую строку название диаграммы, название осиХ, название осиY,название осиZ.

На вкладкеОсиустанавливаются переключатели выбора вида обозначения меток осей.

На вкладкеЛиниисеткиустанавливаются переключатели отображения сетки на диаграмме.

На вкладкеЛегендауказывается место расположения легенды.

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

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

Рисунок 3.4 Диалоговое окноМастер диаграммдля задания параметров диаграммы (этап 3)

Э т а п 4.Размещение диаграммы.Созданную диаграмму можно разместить на том же листе, где находится таблица с исходными данными, либо на отдельном листе. В диалоговом окнеМастер диаграммна этапе 4 (рис.3.5) для этого надо установить соответствующий переключатель и нажать кнопку <Готово>.

Рисунок 3.5 Диалоговое окноМастер диаграммдля выбора места размещения диаграммы (этап 4)

Задание 3.1.Для таблицы на рис. 3.6 постройте два вида диаграмм – внедренную на лист с исходными данными и на отдельном листе.

Порядок работы

1. Создайте новую рабочую книгу командойФайл, Создать;

  • сохраните в выбранной папке созданную книгу под именемDiag командойФайл, Сохранить как;
  • переименуйтеЛист1 на Успеваемость (правой кнопкой мыши вызвать контекстное меню и выполнить командуПереименовать).

Рисунок 3.6 – Таблица успеваемости к заданию 3.1

Рисунок 3.7 – Диаграмма типаГистограммадля задания 3.1

2. Создайте таблицу согласно рис. 3.6. Рассчитайте средний балл по факультету, используя функциюСРЗНАЧ(категория –Статистические).

3. Постройте внедренную диаграмму. Для этого нажмите кнопкуМастер диаграммили выполните командуВставка, Диаграмма.

Э т а п 1. Выбор типа и формата диаграммы:

  • на вкладкеСтандартныевыберите тип диаграммыГистограммаи вид диаграммы – номер 1;
  • щелкните по кнопке <Далее>.

Э т а п 2. Выбор и указание диапазона данных для построения диаграммы:

  • на вкладке Диапазон данныхустановите переключательРяды в столбцах;
  • установите курсор в строкеДиапазон и выделите диапазон данных В2:С6;
  • в том же диалоговом окне щелкните по вкладке Ряд;
  • в окнеРядвыделена строка с названиемРяд1,установите курсор в строкеИмяи щелкните в ячейкеВ1сназваниемИнформатика;
  • в окнеРядщелкните по названиюРяд2,установите курсор в строкеИмяи щелкните в ячейке С1сназваниемВысшая математика;
  • для создания подписей по осиХ щелкните в строкеПодписи оси Хи выделите данные первого столбца таблицы, т.е. диапазонА2:А6;
  • щелкните по кнопке <Далее>.

Э т а п 3. Задание параметров диаграммы:

  • на вкладкеЗаголовкивведите названия в соответствующих строках:

  • на вкладкеЛегендапоставьте флажокДобавить легендуи переключательСправа;
  • щелкните по кнопке <Далее>.
  • Э т а п 4. Размещение диаграммы:
  • установите переключательПоместить диаграммуна имеющемся листе и выберите из списка листУспеваемость;
  • щелкните по кнопке <Готово>;
  • в результате на рабочем листе будет создана внедренная диаграмма. Сравните результат с рис. 3.7.

Внимание!Для изменения размера диаграммы установите курсор мыши в поле диаграммы и один раз щелкните левой кнопкой на контуре диаграммы. На контуре появятся выделенные черные метки (квадраты). Установите курсор мыши на эти метки. Курсор мыши изменит свое начертание на черную тонкую двустороннюю стрелку . Удерживая нажатой левую кнопку, протащите мышь для изменения размеров поля диаграммы.

4. Постройте диаграмму другого типа на отдельном листе. Для этого выполните действия, аналогичные описанным в п.3, но на четвертом шаге установите переключательНа отдельном листе.

Редактирование диаграмм

Редактирование диаграмм выполняется как с помощью контекстного меню, так и с помощью команд управляющего (главного) менюДиаграмма.

Вызов контекстного меню осуществляется путем установки указателя мыши в пустое место диаграммы (т.е. в один из четырех ее углов) и нажатия правой клавиши мыши. Контекстное меню имеет вид, представленный на рис. 3.8.

Пункты меню 2 – 5 предоставляют пользователю возможность вернуться к любому из четырех этапов создания диаграммы и осуществить необходимые изменения, т.е.:

  1. изменить тип и формат диаграммы;
  2. изменить исходные данные:
    • переопределить исходный интервал ячеек, на

основании которых построена диаграмма;

  • переопределить ориентацию рядов и их название;
  • изменить данные, используемые для подписей оси Х;
    1. изменить параметры диаграммы (заголовки, оси, линии сетки, легенду, подписи данных);
    2. изменить размещение диаграммы.

Задание 3.2.Отредактируйте построенную ранее диаграмму в соответствии с заданием.

Порядок работы

1.Скопируйте всю область диаграммы:

  • выделите внедренную диаграмму, щелкнув левой клавишей мыши в области диаграммы один раз. Появятся метки на контуре области диаграммы;
  • выполните командуПравка, Копировать;
  • переместите курсор в новое место на рабочем листе;
  • выполните командуПравка, Вставить.

2.Добавьте в исходную таблицу новый столбецФилософиясразличными оценками.

3.Измените формат диаграммы, сделав ее объемной. Для этого:

  • установите курсор мыши во внутренней незаполненной области диаграммы и, щелкнув правой кнопкой, вызовите контекстное меню диаграммы;
  • выполните командуТип диаграммы и выберите на вкладкеСтандартныетипГистограмма,последний из представленных форматов (3-мерная гистограмма);
  • нажмите кнопку <ОК> и убедитесь в изменении формата диаграммы.

4.Вставьте в диаграмму столбцы, отражающие успеваемость по философии. Для этого:

  • установите курсор мыши во внутренней незаполненной области диаграммы и, щелкнув правой кнопкой, вызовите контекстное меню диаграммы;
  • выполните командуИсходные данные и измените параметры;
  • во вкладке Диапазон данныхв строкеДиапазонукажите весь диапазон данныхА2:D6,включив информацию столбцаФилософия;
  • установите переключательРядыв положениестроках;
  • на вкладкеРядв окнеПодписи оси Хвведите диапазон ячеек В1:D1;
  • нажмите кнопку <ОК>.

5.Измените параметры диаграммы:

  • установите курсор мыши во внутренней незаполненной области диаграммы и, щелкнув правой кнопкой, вызовите контекстное меню диаграммы;
  • выполните командуПараметры диаграммы;
  • укажите на вкладкеЗаголовки:

  • на вкладкеЛегендауберите флажокДобавить легенду;
  • Нажмите кнопку <ОК>.

6.Активизируйте контекстное меню диаграммы и выполните командуРазмещение.Установите переключательПоместить диаграмму на листев положениеотдельноми нажмите кнопку <ОК>. Сравните свой результат с рис.3.9.

Рисунок 3.9 Итоговый результат задания по редактированию диаграмм

Форматирование диаграмм

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

Созданную диаграмму с использованием предложенногоExcel формата можно отформатировать нужным образом, выбрав соответствующую цветовую гамму и шрифт, сделав акцент на важных элементах, убрав лишние детали.

Задание 3.3. Отформатируйте построенную объемную диаграмму согласно заданию.

Порядок работы

1.Измените настройки параметров диаграммы:

  • активизируйте внедренную диаграмму (рис. 3.9), щелкнув правой кнопкой мыши в пустой области диаграммы;
  • в появившемся меню выберите командуОбъемный вид;
  • в появившемся диалоговом окне установите следующие параметры:

  • нажмите кнопку <ОК>.

2.Проведите форматирование области диаграммы и области построения диаграммы:

  • активизируйте внедренную диаграмму, щелкнув правой кнопкой мыши в любом из 4-х углов диаграммы, и в появившемся меню выберите командуФормат области диаграммы;
  • задайте на вкладках диалогового окна установки:

  • выйдите из диалогового окна нажатием кнопки <ОК>;
  • установите курсор в области построения диаграммы и выделите ее, щелкните правой кнопкой мыши для вызова контекстного меню;
  • в контекстном меню выберите командуФормат области построения;
  • задайте в диалоговом окне «Вид» установки:

  • выйдите из диалогового окна нажатием кнопки <ОК>.

3.Проведите форматирование рядов данных и их элементов:

  • установите указатель мыши наряде 1 (Группа 101)и нажмите правую кнопку мыши. В контекстном меню выполните командуФормат рядов данных;
  • на вкладкеПараметрыпроизведите настройку:

  • на вкладкеПорядок рядовв окне установите курсор на названиеГруппа 103и щелкните по кнопке <Вниз>;
  • на вкладке Подписи данныхустановите переключатель Значения;
  • на вкладке Видустановите параметры:

  • нажмите клавишу <ОК>;
  • повторите установку параметров на вкладкеВиддля остальных рядов диаграммы: дляряда 2 –желтый,ряда 3 –зеленый,ряда4 – белый,ряда 5 –голубой цвета заливки.

4.Проведите форматирование осей диаграммы:

  • выделите осьХ,установив на ней курсор мыши и щелкнув один раз правой кнопкой. В контекстном меню выполните командуФормат оси и установите параметры на вкладках:

  • выделите осьY и выполните ее форматирование:

  • выделите осьZи выполните ее форматирование:

5.Проведите форматирование сетки, стен и основания:

  • установите указатель мыши в один из четырех углов диаграммы и нажмите правую кнопку. В контекстном меню выберите командуПараметры диаграммы. Во вкладке Линии сеткиустановите параметры:

  • установите указатель мыши в область стен диаграммы и нажмите правую кнопку мыши. В появившемся меню выберите командуФормат стенок. Во вкладкеВидвыберите светло-желтый цвет заливки;
  • установите указатель мыши в область основания диаграммы и нажмите правую кнопку мыши. В появившемся меню выберите командуФормат основания. Во вкладкеВидвыберите светло-желтый цвет заливки.

6.Проведите форматирование легенды:

  • установите указатель мыши в один из четырех углов диаграммы и вызовите контекстное меню нажатием правой кнопки мыши. В меню выберите командуПараметры диаграммы и во вкладке Легендапоставьте флажок Добавить легендуи переключательСправа;
  • установите указатель мыши на созданное окно легенды и щелкните правой кнопкой. Выполните командуФормат легенды и установите следующие параметры:

7.Сравните созданную вами диаграмму с образцом на рис.3.10.

Рисунок 3.10 Диаграмма после выполнения задания 3 по форматированию её элементов

Практическая работа 4

Тема:ПРИМЕНЕНИЕ СТАНДАРТНЫХ ФУНКЦИЙ В ТАБЛИЦЕ

Цель: произвести расчеты в таблице, используя стандартные функцииExcel.

Общие сведения

В любых версияхExcel для различных типов вычислений имеется большое число встроенных функций: математических, статистических, логических, текстовых, информационных и др.

Функции вводятся обычным набором с клавиатуры или более предпочтительным способом с помощьюМастера функций(рис.4.1), диалоговое окно которого вызывается командойВставка, Функцияили кнопкой

Рисунок 4.1 – Диалоговое окно для выбора категории и вида функции

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

Для каждой категории функций справа в окне(см.рис. 4.1) показан их состав. Выбирается категория функции (слева), имя функции (справа), внизу дается краткий синтаксис функции. Если функция использует несколько однотипных аргументов, указан символ многоточия (...).

После нажатия кнопки <ОК> появляется следующее диалоговое окно (пример окна приведен на рис. 4.2) и осуществляется построение функции, т.е. указание ее аргументов. Каждый аргумент вводится в специально предназначенную для него строку, например, так, как показано на рис. 4.2.

Рисунок 4.2 – Пример диалогового окна для задания аргументов логической функции ЕСЛИ

Формулу вводят в ячейку. Для вставки в формулу других функций в строке ввода, которая находится в верхней части окна над рабочим полем (рис. 4.3), предусмотрена кнопка вызова функций.

Рисунок 4.3 – Использование кнопки вызова функции

Правила построения формул с помощьюМастера функций:

  • состав аргументов функций, порядок задания и типы значений фиксированы и не подлежат изменению;
  • аргументы вводятся в специальных строках ввода, например, так, как изображено на рис. 4.2;
  • для ввода имени блока ячеек используется командаВставка, Имя, Вставить с выбором имени блока;
  • для построения ссылки следует установить курсор в поле ввода, а затем перевести указатель мыши на требуемый рабочий лист для выделения ячейки или блока;
  • абсолютные ссылки формируются при установке курсора перед адресом ячейки в строке ввода и нажатии клавиши <F4>.

Задание 4.1.Сформируйте структуру таблицы и заполните ее постоянными значениями числами, символами, текстом.

В качестве  примера  таблицы  рассматривается экзаменационная ведомость (рис. 4.4). Для каждой группы создаются типовые ведомости, которые содержат списки студентов (фамилия, имя, отчество, № зачетной книжки) и полученные ими оценки на экзамене.

Рисунок 4.4 – Форма экзаменационной ведомости для задания 4.1

Порядок работы

1. Запустите редактор электронных таблицMicrosoftExcel и создайте новую электронную книгу.

2. Создайте таблицу «Экзаменационная ведомость» в соответствии с рис. 4.4.

3. Заполните ячейки данными о студентах учебной группы, приблизительно 10 — 15 строк. Отформатируйте данные.

4. После списка студентов в нижней части таблицы согласно рис. 4.4. произведите расчет итоговых строк:Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Неявка, ИТОГО.Для расчета используется функция СЧЕТЕСЛИ (категория – логические).

Рисунок 4.5 Пример диалогового окна для задания аргументов логической функции СЧЕТЕСЛИ

5.Переименуйте текущий лист:

  • установите курсор на имени текущего листа и вызовите контекстное меню;
  • выберите параметрПереименовать и введите новое имя, напримерЭкзамен 1.

6. Скопируйте текущий листЭкзамен 1 - 2 раза. Переименуйте листы в Экзамен 2 и Экзамен 3. Измените данные  в ведомостях: название дисциплины, оценки и подпись преподавателя.

Задание 4.2. Подготовьте для каждой группы ведомость (рис. 4.6) назначения студентов на стипендию по результатам экзаменационной сессии.

Порядок работы

  1. Создайте новый листСтипендия.
  2. Введите  тексты заголовка и шапки таблицы в соответствии с рис. 4.6. Укажите размер минимальной стипендии в ячейке D3;
  3. Введите формулу вычисления среднего балла студента в ячейку С6 для первого студента. Для этого:
    • установите курсор в ячейке С6;
    • щелкните по кнопке <Мастер функций> на панелиСтандартная ивыберите в диалоговом окне параметры:

Категория: Статистические

Имя: СРЗНАЧ

  • щелкните по кнопке <ОК>, появится панель ввода аргументов функции СРЗНАЧ;
  • установите курсор в 1-й строке (имяЧисло 1) панели ввода аргументов функции, щелкните на названии листаЭкзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;

Рисунок 4.6 – Электронная таблица «Ведомость назначения на стипендию»

  • установите курсор во 2-й строке (имяЧисло 2), щелкните на названии листаЭкзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
  • установите курсор в 3-й строке (имяЧисло 3), щелкните на названии листаЭкзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
  • щелкните по кнопке <ОК>;
  • в ячейке С6 появится значение, рассчитанное по формуле:

=СРЗНАЧ(’Экзамен 1’!D6;’Экэамен 1(2)’!D6;’Экзамен 1(3)’!D6).

  • Скопируйте формулу по всем ячейкам столбца.
    1. Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок. При этом технология ввода будет аналогична описанной в п.3:
      • установите курсор в ячейке D6;
      • щелкните по кнопке <Мастер функций> на панелиСтандартная и выберите в диалоговом окне параметры:

Категория: Статистические

Имя: СЧЕТ

  • щелкните по кнопке <ОК>, появится панель ввода аргументов функции СЧЕТ;
  • установите курсор в 1-й строке (имяЗначение 1) панели ввода аргументов функции, щелкните на названии листаЭкзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
  • установите курсор во 2-й строке (имяЗначение 2), щелкните на названии листаЭкзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
  • установите курсор в 3-й строке (имяЗначение 3), щелкните на названии листаЭкзамен 1(3) и выберите ячейку D6 с оценкой того же студента по третьему экзамену;
    • щелкните по кнопке<ОК>;
    • в ячейке D6 появится значение, рассчитанное по формуле:

=СЧЕТ(‘Экзамен 1‘!D6;‘Экзамен 1(2)‘!D6;‘Экзамен 1(3)‘!D6).

5. Скопируйте формулу по всем ячейкам столбца D.

6. Введите формулу для вычисления размера стипендии студента в ячейку Е6. Эта формула должна иметь следующий вид:

=ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(С6>=3;D6=3);$D$3;0))

7. Технология ввода формулы будет аналогична описанной в п. 3 и 4:

  • установите курсор в ячейке Е6;
  • щелкните по кнопке <Мастер функций> на панелиСтандартная и выберите в диалоговом окне параметры:

Категория: Логические

Имя: ЕСЛИ

  • щелкните по кнопке <ОК>, появится панель ввода аргументов функции ЕСЛИ;
  • курсор будет находиться в 1-й строке (имяЛогическое выражение) панели ввода аргументов функции;
  • нажмите кнопку вызова функции в строке ввода, выберите категориюДругиефункции и функциюИ, нажмите кнопку <ОК>;
  • появится второе окно ввода аргументов функцииИ, курсор автоматически будет установлен в строкеЛогическое 1;
  • щелкните в ячейке С6, где показан средний балл этого студента, и наберите с клавиатуры условие >=4,5. В результате в этой строке должно быть выражение:
  • С6>=4,5
  • установите курсор на второй строкеЛогическое выражение 2 и аналогично сформируйте выражение, которое указывает необходимое количество сданных экзаменов (в данном примере это число 3):
  • D6=3
  • щелкните по кнопке <ОК>. В результате в строке ввода должно появиться выражение:
  • =ЕСЛИ(И(С6>=4,5;D6=3)
  • щелкните мышью на кнопкеfxстроки ввода, появится первое окно ввода аргументов для функцииЕСЛИ;
    • установите курсор во 2-й строке (имяЗначение_ если истина), щелкните в ячейке D3 и нажмите клавишу <F4>. Появится символ $ перед именем столбца и номером строки. Введите выражение *1,5. В результате в этой строке будет выражение:
    • $D$3*1,5
    • установите курсор в 3-й строке (имяЗначение_ если ложь) и по аналогичной технологии введите оставшуюся часть формулы
    • ЕСЛИ(И(С6>=3;D6=3);$D$3;0)
    • после окончания формирования формулы нажмите кнопку <ОК>.

8. Скопируйте эту формулу в другие ячейки столбца Е.

9. Рассчитайте «Итого стипендиальный фонд по группе» с помощью<Мастера  функций>.

10. Проверьте работоспособность таблицы:

  • введите другие оценки в экзаменационные ведомости;
  • измените минимальный размер стипендии.
    1. Сохраните рабочую книгу командойФайл, Сохранить.
    2. Закройте рабочую книгу командойФайл, Закрыть.

Краткая справка:

  1. В структуре формулы имеются вложенные функции И(...), ЕСЛИ(...). Для ввода этих функций надо воспользоваться кнопкой вызова функции (см. рис. 4.2), находящейся в строке ввода под панелями.
  2. При наборе формулы автоматически расставляются круглые скобки и разделительный символ — точка с запятой.
  3. В процессе набора формулы постоянно сравнивайте ее с выражением, которое приведено в этом пункте выше.
  4. В числах для отделения целой части от дробной используется либо точка, либо запятая, что зависит от установок Excel.
  5. Если после ввода формулы появится синтаксическая ошибка, то следует проверить количество скобок, наличие разделителя (точки с запятой), заменить в числе точку на запятую или наоборот.

Дополнительные задания

Задание 1.Заполнить таблицу 4.1, определить, кто из студентов допущен к экзамену, используя логическую функцию =ЕСЛИ().

Таблица 4.1 – Исходные данные для дополнительного задания

ФИО

ТГП

КП

ИГП

АП

Средний балл

Допуск

Петров

5

5

5

5

?

?

Иванов

5

4

5

4

?

?

Сидоров

3

5

4

5

?

?

Пашин

4

2

4

3

?

?

Семенов

3

4

2

2

?

?

Итого допущено:

?

Задание 2.Заполнить таблицу 4.2, рассчитать НДС (если цена до 8000 руб., то НДС – 10%, если цена от 8000 до 10000 руб., то НДС – 15%, если цена больше 10000 руб., то НДС 20%). Подсчитать количество товаров, у которых цена с учетом НДС превышает 10000 руб.

Биржевой курс 1$=28,35 руб.

Таблица 4.2 – Исходные данные для дополнительного задания

Товар

Цена в $

Цена в руб.

НДС

Цена с учетом НДС

Телевизор

410

?

?

?

Магнитофон

195

?

?

?

Холодильник

625

?

?

?

Комбайн

156

?

?

?

Пылесос

174

?

?

?

Компьютер

601

?

?

?

Микроволновая печь

190

?

?

?

Задание 3.Заполнить таблицу 4.3, рассчитать сумму возврата кредита при условии: если дата возврата фактическая не превышает договорную, то сумма возврата увеличивается на 40% от суммы кредита , в противном случае сумма возврата увеличивается на 40% плюс 1 % за каждый просроченный день.

Таблица 4.3 – Исходные данные для дополнительного задания

Наименование организации

Дата получения кредита

Сумма кредита (млн. руб.)

Дата возврата (по договору)

Дата возврата (фактическая)

Сумма возврата (млн. руб.)

АО «Роника»

05.12.98

200

04.03.99

22.02.99

?

СП «Изотоп»

25.01.99

500

24.04.99

15.05.99

?

ООО «Термо»

03.02.99

100

02.06.99

22.06.99

?

АОЗТ «Чипы»

21.11.98

300

20.05.99

18.05.99

?

АО «Медицина»

12.05.99

50

11.07.99

20.09.99

?

АО «Колос»

08.04.99

150

07.10.99

12.10.99

?

Задание 4.Заполнить таблицу 4.4. Рассчитать процент скидки по каждому наименованию продукции, исходя из того, что процент скидки назначается в зависимости от последней цифры номенклатурного номера:

1-1,5%;2-7%; 3-10%; 4-15%; 5-20%.

Для расчета использовать функцию ПРОСМОТР (или ЕСЛИ), а для определения последней цифры номенклатурного номера функцию ОСТАТ.

Таблица 4.4 – Исходные данные для дополнительного задания

Но-

менк-

латур-

ныи

номер

Наимено-

вание про-

дукции

Коли-

чество

(шт.)

Цена

(тыс.

руб.)

Сумма

(тыс.

руб.)

%

скид-

ки

Сумма

скидки

(тыс.

руб.)

Стои-

мость с

учетом

скидки

(тыс.руб.

202

Монитор

5

12

?

?

?

?

201

Клавиатура

25

0,25

?

?

?

?

403

Дискета

100

0,02

?

?

?

?

105

Принтер

2

10

?

?

?

?

204

Сканер

1

8

?

?

?

?

Практическая работа 5

Тема: СВЯЗАННЫЕ ТАБЛИЦЫ.РАСЧЕТ ПРОМЕЖУТОЧНЫХ ИТОГОВ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ.ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИИ

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

Задание 5.1.Создать таблицы ведомости начисления заработной платы за два месяца на разных листах электронной книги, произвести расчеты, форматирование, сортировку и защиту данных. Исходные данные представлены на рис. 5.1.

Порядок работы

1. Запустите редактор электронных таблицMicrosoftExcel и создайте новую электронную книгу.

2. Создайте таблицу расчета заработной платы по образцу (см. рис. 5.1). Введите исходные данные — Табельный номер, ФИО и Оклад, % Премии= 27%, %Удержания = 13%.

Рисунок 5.1 Ведомость начисления заработной платы

При расчете премии используется формула:

Премия =Оклад *%Премии.

В ячейкеD5 наберите формулу= С5 * $D$4(ячейкаD4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено»:

Всего начислено = Оклад + Премия.

При расчете удержания используется формула:

Удержание = Всего начислено * % Удержания,

для этого в ячейкеF5 наберите формулу = $F$4 * Е5.

Формула для расчета столбца «К выдаче»:

К выдаче = Всего начислено - Удержания.

  1. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче»(Вставка/Функция/категорияСтатистические функции).
  2. Переименуйте ярлычок листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командойПереименоватьконтекстного меню ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены на рис 5.2.

Краткая справка. Каждая рабочая книгаExcel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того, чтобы хранить большие последовательные наборы данных на одном листе.

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист(Правка/Переместить/ Скопировать лист).Можно воспользоваться командойПереместить/Скопироватьконтекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошкеСоздавать копию(рис. 5.3).

Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).

6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените  значение премии на 32%. Убедитесь, что программа произвела пересчет формул.

  1. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата»(Вставка/Столбец)и рассчитайте значение доплаты по формуле Доплата = Оклад * % Доплаты. Значение доплаты примите равным 5%.

Рисунок 5.2 Результаты начисления заработной платы

  1. Измените формулу для расчета значений колонки «Всего начислено»:

Всего начислено = Оклад + Премия + Доплата.

Рисунок 5.3 – Копирование листа электронной книги

Рисунок 5.4 – Условное форматирование данных

  1. Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 5000 и 7000 зеленым цветом шрифта; меньше 5000 красным; больше или равно 7000 синим цветом шрифта(Формат/Условное форматирование)(рис. 5.4).
  2. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент таблицы А5:G18), выберите менюДанные/Сортировка,сортировать по —Столбец В(рис. 5.6).
    1. Поставьте к ячейкеD3 комментарии «Премия пропорциональна окладу»(Вставка/Примечание),при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания.
    2. Защитите лист «Зарплата ноябрь» от изменений(Сервис/Защита/Защитить лист).Задайте пароль на лист (рис. 5.5), сделайте подтверждение пароля.

Рисунок 5.5 – Защита листа электронной книги

Рисунок 5.6 – Сортировка данных

Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа{Сервис/Защита/Снять защиту листа).

  1. Самостоятельносделать примечания к двум-трем ячейкам.

14. Выполнить условное форматирование премии за ноябрь месяц:

до 1000 руб. желтым цветом заливки; от 1000 до 2000 руб. зеленым цветом шрифта; свыше 2000 руб. малиновым цветом заливки.

15.Защитить лист зарплаты за октябрь от изменений.

Проверьте защиту. Убедитесь в неизменяемости данных. Снимите защиту со всех листов электронной книги «Зарплата».

16.Построить круговую диаграмму начисленной суммы к выдаче всех сотрудников за ноябрь месяц.

Задание 5.2.Рассчитать зарплату за декабрь и построить диаграмму. Создать итоговую таблицу ведомости квартального начисления заработной платы, провести расчет промежуточных итогов по подразделениям.

Порядок работы

  1. Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги(Правка/Переместить/Скопировать лист).Не забудьте для копирования поставить галочку в окошкеСоздавать копию.
  2. Присвойте скопированному листу название «Зарплата декабрь». Исправьте название месяца в ведомости на декабрь.

Рисунок 5.7Ведомость зарплаты за декабрь

  1. Измените значение премии на 46 %, доплаты — на 8%.Убедитесь, что программа произвела пересчет формул (рис. 5.7).
  2. По данным таблицы «Зарплата декабрь» постройте гистограмму доходов сотрудников (столбец «К выдаче»). В качестве подписей оси Х выберите фамилии сотрудников. Проведите форматирование диаграммы. Конечный вид гистограммы приведен на рис. 5.8.

Рисунок 5.8 Гистограмма зарплаты за декабрь

  1. Перед расчетом итоговых данных за квартал проведите сортировку по фамилиям валфавитном порядке (по возрастанию) в ведомости начисления зарплаты за октябрь.
  2. Скопируйте содержимое листа «Зарплата октябрь» на новый лист{Правка/Переместить/ Скопировать лист).Незабудьте для копирования поставить галочку в окошкеСоздавать копию.
  3. Присвойте скопированному листу название «Итоги за квартал». Измените название таблицы на «Ведомость начисления заработной платы за 4 квартал». Отредактируйте лист «Итоги за квартал» согласно образцу на рис. 5.9.

9. Произведите расчет квартальных начислений, удержания и суммы к выдаче как сумму начислений за каждый месяц (данные по месяцам располагаются на разных листах электронной книги, поэтому к адресу ячейки добавится адрес листа).

Краткая справка. Чтобы вставить в формулу адрес или диапазон ячеек с другого листа, следует во время ввода формулы щелкнуть по закладке этого листа и выделить на нем нужные ячейки. Вставляемый адрес будет содержать название этого листа.

В ячейкеD5 для расчета квартальных начислений «Всего начислено» формула имеет вид:

='Зарплата декабрь'!F5 + 'Зарплата ноябрь'!F5 + 'Зарплата октябрь'!Е5.

Аналогично произведите квартальный расчет «Удержания» и «К выдаче».

Рисунок 5.9 Таблица для расчета итоговой квартальной заработной платы

Примечание. При выборе начислений за каждый месяц делайте ссылку на соответствующую ячейку из таблицы соответствующего листа электронной книги «Зарплата». При этом произойдет связывание информации соответствующих ячеек листов электронной книги.

  1. Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений — по фамилиям. Таблица примет вид, как на рис. 5.10.

Рисунок 5.10 Вид таблицы после сортировки

11. Подведите промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу и выполните командуДанные/Итоги(рис. 5.11). Задайте параметры подсчета промежуточных итогов:

при каждом изменении в Подразделение;

операция Сумма;

добавить итоги по: Всего начислено, Удержания, К выдаче.

Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».

Рисунок 5.11 Окно задания параметров расчета промежуточных итогов

Примерный вид итоговой таблицы представлен на рис. 5.12.

Рисунок 5.12 Итоговый вид таблицы расчета квартальных итогов по зарплате

12. Изучите полученную структуру и формулы подведения промежуточных итогов, устанавливая курсор на разные ячейки таблицы. Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «-»).

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

13. Сохраните файл «Зарплата» с произведенными изменениями (Файл/Сохранить).

Задание 5.3.Исследовать графическое отображение зависимостей ячеек друг от друга.

Порядок работы

Скопируйте содержимое листа «Зарплата октябрь» на новый лист. Копии присвойте имя «Зависимости». Откройте панель «Зависимости»{Сервис/Зависимости/Панель зависимостей)(рис. 5.13). Изучите назначение инструментов панели, задерживая на них указатель мыши.

Устанавливайте курсор на ячейку в каждом столбце и вызывайте зависимости кнопкамиВлияющие ячейкииЗависимые ячейкипанели «Зависимости». Появятся стрелки, указывающие на зависимость ячейки от других ячеек и ее влияние на другие ячейки. Примерный вид таблицы с зависимостями приведен на рис. 5.14. Сохраните файл «Зарплата» с произведенными изменениями.

Рисунок 5.13 Панель зависимостей

Рисунок 5.14 Зависимости в таблице расчета зарплаты

Дополнительное задание

Заполнить таблицы вMSExcel.

В таблице 5.1 рассчитать значение графы «Процент выполнения плана». Результат округлить до одного знака после запятой.

Таблица 5.1 – Исходные данные для дополнительного задания

Наименование калькуляционных статей расходов

Утверждено на 1999 г. (млн. руб.)

Выполнено в 1999 г. (млн. руб.)

Процент выполнения плана

1

Сырье и материалы

3017

3121

?

2

Полуфабрикаты

26335

26334

?

3

Топливо и энергия

341

353

?

4

Зарплата производственных рабочих

3670

3448

?

5

Цеховые расходы

1738

1634

?

6

Общезаводские расходы

2926

3109

?

7

Прочие расходы

276

444

?

Итого

38303

38443

?

Заполнить таблицу 2 и рассчитать графу 3 по формуле:

гр.3 табл.2 = гр.3 табл.1*1,1 (если значение гр.5 табл.1<100);

гр.3 табл.2 = гр.3 табл.1*1,05 (если значение гр.5 табл.1>100, но <105);

гр.3 табл.2 = гр.3 табл.1*1,01 (если  значение гр.5 табл.1>105).

Результат округлить до целого значения.

Таблица 5.2 – Расчет выполнения плана

Наименование калькуляционных статей расходов

Запланировано на 2000 г.

Сумма (млн. руб.)

Процент к итогу

1

2

3

4

1

Сырье и материалы

3277

?

2

Полуфабрикаты

27651

?

3

Топливо и энергия

371

?

4

Зарплата производственных рабочих

4037

?

5

Цеховые расходы

1912

?

6'

Общезаводские расходы

2955

?

7

Прочие расходы

279

?

Итого

40482

?

гр.4 = гр.3 табл.2/гр.3 табл.1*100. Результат округлить до целого значения.

Практическая работа 6

Тема: ПОДБОР ПАРАМЕТРА. ОРГАНИЗАЦИЯ ОБРАТНОГО РАСЧЕТА

Цель занятия: изучение технологии подбора параметра при обратных расчетах.

Задание 6.1. Используя режим подбора параметра, определить, при каком значении % премии общая сумма заработной платы за октябрь будет равна 250 000 руб. (на основании файла «Зарплата», созданного в практических работах 4... 5).

Краткая справка. К исходным данным этой таблицы относятся значения оклада и % премии, одинаковые для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов. Использование операции «Подбор параметра» вMSExcel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра и по этому значению подбирается некоторое удовлетворяющее заданным условиям значение исходного параметра расчета.

Порядок работы

  1. Запустите редактор электронных таблицMicrosoftExcel и откройте файл «Зарплата».
  2. Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги{Правка/Переместить/Скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию.Присвойте скопированному листу имя «Подбор параметра».
  3. Осуществите подбор параметра командойСервис/Подбор параметра(рис. 6.1).

В диалоговом окнеПодбор параметрана первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейкаG19), на второй строке наберите заданное значение 200000, на третьей строке укажите адрес подбираемого значения % Премии (ячейкаD4), затем нажмите кнопкуОК.В окнеРезультат подбора параметрадайте подтверждение подобранному параметру нажатием кнопкиOK (рис. 6.2).

Рисунок 6.1 – Задание параметров подбора параметра

Рисунок 6.2 – Подтверждение результатов подбора параметра

Произойдет обратный пересчет % Премии. Результаты подбора (рис. 6.3): если сумма к выдаче равна 200000 р., то % Премии должен быть 194 %.

Рисунок 6.3 подбор значения % премии для заданной общей суммы заработной платы, равной 200000 руб.

Задание 6.2.Используя режим подбора параметра, определить штатное расписания фирмы. Исходные данные приведены на рис. 6.4.

Краткая справка. Известно, что в штате фирмы состоит:

6 курьеров;

8 младших менеджеров;

10 менеджеров;

3 заведующих отделами;

1 главный бухгалтер;

1 программист;

1 системный аналитик;

1 генеральный директор фирмы.

Общий месячный фонд зарплаты составляет 100000 руб. Необходимо определить, какими должны быть оклады сотрудников фирмы.

Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата =Аi*х +Bi, гдех оклад курьера;АiиBi коэффициенты, показывающие:

Аi— во сколько раз превышается значениех;

Biна сколько превышается значениех.

Порядок работы

  1. Запустите редактор электронных таблицMicrosoftExcel.
  2. Создайте таблицу штатного расписания фирмы по приведенному образцу (см. рис. 6.4). Введите исходные данные в рабочий лист электронной книги.
  3. В ячейкуD3 временно введите произвольное число.

Рисунок 6.4 Исходные данные для задания 6.2

  1. В столбцеD введите формулу для расчета заработной платы по каждой должности. Например, для ячейкиD6 формула расчета имеет следующий вид:= В6 * $D$3 + С6 (ячейкаD3 задана в виде абсолютной адресации).

Далее скопируйте формулу из ячейкиD6 вниз по столбцу автокопированием.

В столбцеF задайте формулу расчета заработной платы всех работающих в данной должности. Например, для ячейкиF6 формула расчета имеет вид: =D6 * Е6.

Далее скопируйте формулу из ячейкиF6 вниз по столбцу автокопированием.

В ячейкеF14 автосуммированием вычислите суммарный фонд заработной платы фирмы.

5. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, равной 100 000 руб. Для этого в менюСервисактивизируйте командуПодбор параметра.

В полеУстановить в ячейкепоявившегося окна введите ссылку на ячейкуF14, содержащую формулу расчета фонда заработной платы;

в полеЗначениенаберите искомый результат 100000;

в полеИзменяя значение ячейкивведите ссылку на изменяемую ячейкуD3, в которой находится значение зарплаты курьера, и щелкните по кнопкеОК.Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 руб.

6. Присвойте рабочему листу имя «Штатное расписание 1». Сохраните созданную электронную книгу под именем «Штатное расписание» в своей папке.

Анализ задач показывает, что с помощьюMSExcel можно решать линейные уравнения. Задания 6.1 и 6.2показывают, что поиск значения параметра формулы — это не что иное, как численное решение уравнений. Другими словами, используя возможности программыMSExcel, можно решать любые уравнения с одной переменной.

Дополнительное задание

Используя режим подбора параметра и таблицу расчета штатного расписания (см. задание 6.2),определить заработные платы сотрудников фирмы для ряда заданных значений фонда заработной платы.

Порядок работы

1. Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2».Выберите коэффициенты уравнений для расчета согласно табл. 6.1(один из пяти вариантов расчетов).

Таблица 6.1 – Исходные данные для дополнительного задания

2. Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100000, 150000, 200000, 250000, 300000, 350000, 400000 руб. Результаты подбора значений зарплат скопируйте в табл. 6.2. в виде специальной вставки.

Таблица 6.2 – Расчет заработной платы сотрудников

Краткая справка. Для копирования результатов расчетов в виде значений необходимо выделить копируемые данные, произвести запись в буфер памяти(Правка/Копировать),установить курсор в соответствующую ячейку таблицы ответов, задать режим специальной вставки(Правка/Специальная вставка),отметив в качестве объекта вставки значения(Правка/Специальная вставка/вставитьзначения)(рис. 6.5).

Рисунок 6.5 Специальная вставка значений данных.

Специальная вставка информации в виде значений позволяет копировать значения, полученные в результате расчетов, без дальнейшей их зависимости от пересчета формул.

Практическая работа 7

Тема: ЗАДАЧИ ОПТИМИЗАЦИИ (ПОИСК РЕШЕНИЯ)

Цель занятия:изучение технологии поиска решения для задач оптимизации (минимизации, максимизации).

Задание 7.1.Минимизация фонда заработной платы фирмы.

Пусть известно, что для нормальной работы фирмы требуется 5...7 курьеров, 8...10 младших менеджеров, 10 менеджеров, 3 заведующих отделами, главный бухгалтер, программист, системный аналитик, генеральный директор фирмы.

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

В качестве модели решения этой задачи возьмем линейную модель. Тогда условие задачи имеет вид:

N1 * А1 *х +N2 * (А2 *х +В2) + . . . +N8 * (А8 *х +В8) = Минимум,

гдеNi количество работников данной специальности;х зарплата курьера; Аi- и Вi коэффициенты заработной платы сотрудников фирмы.

Порядок работы

1. Запустите редактор электронных таблицMicrosoftExcel и откройте созданный в практической работе 6 файл «Штатное расписание».

Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 3».

  1. В менюСервисактивизируйте командуПоиск решения(рис.7.1). Если командаПоиск решения не активизирована, выполните следующие действия: в меню Сервис, Надстройки установите флажок Поиск решения.
  2. В окнеУстановить целевую ячейкуукажите ячейкуF14, содержащую модель суммарный фонд заработной платы.

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

В окнеИзменяя ячейкиукажите адреса ячеек, в которых будет отражено количество курьеров и младших менеджеров, а также зарплата курьера $E$6;$E$7;$D$3 (при задании ячеек Е6, Е7 иD3 держите нажатой клавишу [Ctrl]).

Рисунок 7.1 Задание условий для минимизации фонда заработной платы

Рисунок 7.2 Добавление ограничений для минимизации фонда заработной платы

Используя кнопкуДобавитьв окнахПоиск решенияиДобавление ограничений,опишите все ограничения задачи: количество курьеров изменяется от 5 до 7, младших менеджеров от 8 до 10, а зарплата курьера > 1400 (рис. 7.2). Ограничения наберите в виде:

$D$3 > = 1400

$Е$6> = 5

$Е$6 < = 7

$Е$7 > = 8

$Е$7 < = 10.

Активизировав кнопкуПараметры,введите параметры поиска, как показано на рис. 7.3.

Окончательный вид окнаПоиск решенияприведен на рис. 7.1.

Запустите процесс поиска решения нажатием кнопкиВыполнить.В открывшемся диалоговом окнеРезультаты поиска решениязадайте опциюСохранить найденное решение(рис. 7.4).

Рисунок 7.3 Задание параметров поиска решения по минимизации фонда заработной платы

Рисунок 7.4 Сохранение найденного при поиске решения

Решение задачи приведено на рис. 7.5. Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месячный фонд заработной платы.

Рисунок 7.5 Минимизация фонда заработной платы

Задание 7.2.Составление плана выгодного производства.

Фирма производит несколько видов продукции из одного и того же сырья А, В и С. Реализация продукции А дает прибыль 10 руб., В 15 руб. и С 20 руб. на единицу изделия.

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

Нормы расхода сырья на производство продукции каждого вида приведены в таблице 7.1.

Таблица 7.1 – Исходные данные для задания 7.2

Сырье

Нормы расхода сырья

Запас сырья

Сырье 1

18

15

12

350

Сырье 2

6

4

8

200

Сырье 3

5

3

3

100

Прибыль

10

15

20

Порядок работы

  1. Запустите редактор электронных таблицMicrosoftExcel и создайте новую электронную книгу.
  2. Создайте расчетную таблицу, как на рис. 7.6.

Рисунок 7.6 Исходные данные для задания 7.2

Введите исходные данные и формулы в электронную таблицу. Расчетные формулы имеют такой вид:

Расход сырья 1 = (количество сырья 1) * (норма расхода сырья А) + (количество сырья 1) * (норма расхода сырья В) + (количество сырья 1) * (норма расхода сырья С).

Значит, в ячейкуF5 нужно ввести формулу = В5 * $В$9 + С5 * $С$9 +D5 * $D$9.

Обратите внимание, что значения количества сырья каждого вида пока не известны и будут подобраны в процессе решения задания (ячейкиB9:D9 пока пустые).

(Общая прибыль по А) = (прибыль на ед. изделий А) * (количество А), следовательно, в ячейку В10 следует ввести формулу = В8 * В9.

Итоговая общая прибыль = (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С), значит, в ячейку Е10 следует ввести формулу =CУMM(B10:D10).

Рисунок 7.7 Задание условий и ограничений для поиска решений

3. В менюСервисактивизируйте командуПоиск решенияи введите параметры поиска, как указано на рис. 7.7.

В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль» (Е10), в качестве изменяемых ячеек ячейки количества сырья: (B9:D9).

Не забудьте задать максимальное значение суммарной прибыли и указать ограничения на запас сырья:

расход сырья 1 < = 350; расход сырья 2 < = 200; расход сырья 3 < = 100, а также положительные значения количества сырья А, В, С> = 0.

Установите параметры поиска решения (рис. 12.8). Для этого кнопкойПараметрыоткройте диалоговое окноПараметры поиска решения,установите параметры по образцу, задайте линейную модель расчета(Линейность модели) иНеотрицательные значения.

Рисунок 7.8 – Задание параметров поиска решения

Рисунок 7.9 – Найденное решение

максимизации прибыли при заданных ограничениях

  1. КнопкойВыполнитьзапуститеПоиск решения.Если вы сделали все верно, то решение будет, как на рис. 7.9.
  2. Сохраните созданный документ под именем «План производства».

Выводы. Из решения видно, что оптимальный план выпуска предусматривает изготовление 5,56 кг продукции В и 22,22 кг продукции С. Продукцию А производить не стоит. Полученная прибыль при этом составит 527,78 руб.

Практическая работа 8

Тема: СВЯЗИ МЕЖДУ ФАЙЛАМИ И КОНСОЛИДАЦИЯ ДАННЫХ

Цель занятия:изучение технологии связей между файлами и консолидации данных вMSExcel.

Задание 8.1.Задать связи между файлами.

Порядок работы

  1. Запустите редактор электронных таблицMicrosoftExcel и создайте новую электронную книгу.
  2. Создайте таблицу «Отчет о продажах за 1 квартал» по образцу рисунка 8.1. Введите исходные данные (Доходы и Расходы):

Доходы = 234,58 руб.; Расходы = 75,33 руб.

и проведите расчет прибыли: Прибыль = Доходы - Расходы. Сохраните файл под именем «1 квартал».

3. Создайте таблицу «Отчет о продажах 2 квартал» по образцу рис. 8.1 в виде нового файла. Для этого создайте новый документ(Файл/Создать)и скопируйте таблицу отчета о продаже за первый квартал, после чего исправьте заголовок таблицы и измените исходные данные:

Доходы = 452,6 руб.; Расходы = 125,8 руб.

Обратите внимание, как изменился расчет прибыли. Сохраните файл под именем «2 квартал».

4. Создайте таблицу «Отчет о продажах за полугодие» по образцу рисунка 8.1 в виде нового файла. Для этого создайте новый документ{Файл/Создать)и скопируйте таблицу отчета о продаже за первый квартал, после чего подправьте заголовок таблицы и в колонке В удалите все значения исходных данных и результаты расчетов. Сохраните файл под именем «Полугодие».

5. Для расчета полугодовых итогов свяжите формулами файлы «1 квартал» и «2 квартал».

Рисунок 8.1 Задание связей между файлами

Краткая справка. Для связи формулами файловExcel выполните действия:

откройте эти файлы (все три файла);

начните ввод формулы в файле-клиенте (в файле «Полугодие» введите формулу для расчета «Доход за полугодие»).

Формула для расчета:

Доход за полугодие = Доход за 1 квартал + Доход за 2 квартал.

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

Полный адрес ячейки состоит из названия рабочей книги в квадратных скобках, имени листа, восклицательного знака и адреса ячейки на листе.

В ячейке ВЗ файла «Полугодие» формула для расчета полугодового дохода имеет следующий вид:

= '[1 квартал-xls] Лист1'!$В$3 + '[2 квартал.х1s]Лист1'!$В$3.

Аналогично рассчитайте полугодовые значения расходов и прибыли, используя данные файлов «I квартал» и «2 квартал». Результаты работы представлены на рис. 8.1. Сохраните текущие результаты расчетов.

Примечание. Если файл-источник данных закрыт, в формуле, которая на него ссылается, будет указан весь путь для этого файла.

Задание 8.2.Обновить связи между файлами.

Порядок работы

  1. Закройте файл «Полугодие» предыдущего задания.
  2. Измените значения «Доходы» в файлах первого и второго кварталов, увеличив их на 100 руб.:

Доходы 1 квартала - 334,58 руб.; Доходы 2 квартала = 552,6 руб.

Сохраните изменения и закройте файлы.

3. Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи (рис. 8.2). Для обновления связей нажмите кнопкуДа.Проследите, как изменились данные файла «Полугодие» (величина «Доходы» должна увеличиться на 200 руб. и принять значение 887,18 руб.).

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

  1. Изучим процесс ручного обновления связи. Сохраните файл «Полугодие» и закройте его.
  2. Вновь откройте файлы первого и второго кварталов и измените исходные данные доходов, увеличив значения на 100 руб.:

Доходы 1 квартала = 434,58 руб.; Доходы 2 квартала = 652,6 руб.

Сохраните изменения и закройте файлы.

  1. Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи, нажмите кнопкуНет.Для ручного обновления связи в менюПравкавыберите командуСвязи,появится окно, как на рис. 8.3. В окне перечислены все файлы, данные из которых используются в активном файле «Полугодие».

Рисунок 8.2 Окно предложения обновления связи

Рисунок 8.3 Ручное обновление связей между файлами

Расположите его так, чтобы были видны данные файла «Полугодие», выберите файл «1 квартал» и нажмите кнопкуОбновить, проследите, как изменились данные файла «Полугодие». Аналогично выберите файл «2 квартал» и нажмите кнопкуОбновить.Проследите, как вновь изменились данные файла «Полугодие».

Примечание. При изменении данных в нескольких исходных файлах обновление связи производится для каждого файла.

Задание 8.3.Консолидация данных для подведения итогов по таблицам данных сходной структуры.

Краткая справка. ВExcel существует удобный инструмент для подведения итогов по таблицам данных сходной структуры, расположенных на разных листах или разных рабочих книгах, —Консолидация данных.При этом одна и та же операция (суммирование, вычисление среднего и др.) выполняется по всем ячейкам нескольких прямоугольных таблиц, и все формулыExcel строит автоматически.

Порядок работы

  1. Откройте все три файла задания 8.2 и в файле «Полугодие» в колонке В удалите все численные значения данных. Установите курсор в ячейку ВЗ.
  2. Выполните командуДанные/Консолидация(рис. 8.4). В появившемся окне Консолидациявыберите функцию «Сумма».

В строке «Ссылка» сначала выделите в файле «1 квартал» диапазон ячеек ВЗ:В5 и нажмите кнопкуДобавить,затем выделите в файле «2 квартал» диапазон ячеек ВЗ:В5 и опять нажмите кнопкуДобавить(см. рис. 8.4). В списке диапазонов будут находиться две области данных за первый и второй кварталы для консолидации. Далее нажмите кнопкуОК,произойдет консолидированное суммирование данных за первый и второй кварталы. Вид таблиц после консолидации данных приведен на рис. 8.5.

Рисунок 8.4 Консолидация данных

Рисунок 8.5 Таблица «Полугодие» после консолидированного суммирования

Задание 8.4.Консолидация данных для подведения итогов по таблицам неоднородной структуры.

Порядок работы

  1. Запустите редактор электронных таблицMicrosoftExcel и создайте новую электронную книгу. Наберите отчет по отделам за третий квартал по образцу (рис. 8.6). Произведите расчеты и сохраните файл с именем «3 квартал».
  2. Создайте новую электронную книгу. Наберите отчет по отделам за четвертый квартал по образцу (рис. 8.7). Произведите расчеты и сохраните файл с именем «4 квартал».
  3. Создайте новую электронную книгу. Наберите название таблицы «Полугодовой отчет о продажах по отделам».

Рисунок 8.6 Исходные данные для третьего квартала задания 8.4

Рисунок 8.7 Исходные данные для четвертого квартала задания 8.4

Установите курсор на ячейку A3 и проведите консолидацию за третий и четвертый кварталы по заголовкам таблиц. Для этого выполните командуДанные/Консолидация.В появившемся окне консолидации данных сделайте ссылки на диапазон ячеек АЗ:Е6 файла «3 квартал» иA3:D6 файла «4 квартал» (рис. 8.8). Обратите внимание, что интервал ячеек включает имена столбцов и строк таблицы.

В окнеКонсолидацияактивизируйте опции (поставьте галочку):

подписи верхней строки;

значения левого столбца;

создавать связи с исходными данными (результаты будут не константами, а формулами).

После нажатия кнопкиОКпроизойдет консолидация (рис. 8.9). Сохраните все файлы в папке вашей группы.

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

Рисунок 8.8 Консолидация неоднородных таблиц

Рисунок 8.9 Результаты консолидации неоднородных таблиц

Дополнительное задание

Построить таблицы вMSExcel по приведенным ниже формам. Присвоить каждому листу имя, соответствующее названию месяца. В таблицах 8.1 и 8.2 рассчитать значение графы «Выплачено пенсионеру» по формуле: гр.5=гр.З гр.4.

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

Таблица 8.1 Свод лицевых счетов пенсионеров за январь

№ лицевого счета

Фамилия

Сумма причитающейся пенсии (руб.)

Удержания по исполнительным документам

Выплачено пенсионеру

1

2

3

4

5

И1212

Иванов

900

125

?

А1245

Антонов

1200

200

?

П1270

Петров

560

25

?

Д1645

Дубовицкий

456

0

?

С 1767

Сидоров

304

100

?

Таблица 8.2 Свод лицевых счетов пенсионеров за февраль

№ лицевого счета

Фамилия

Сумма причитающейся пенсии (руб.)

Удержания по исполнительным документам

Выплачено пенсионеру

1

2

3

4

5

И1212

Иванов

950

130

?

А 1245

Антонов

1250

210

?

П1270

Петров

610

30

?

Д1645

Дубовицкий

506

5

?

С 1767

Сидоров

374

100

?

Таблица 8.3 Свод лицевых счетов пенсионеров за январь и февраль

№ лицевого счета

Фамилия

Сумма причитающейся пенсии (руб.)

Удержания по исполнительным документам

Выплачено пенсионеру

1

2

3

4

5

И1212

Иванов

?

?

?

А1245

Антонов

?

?

?

П1270

Петров

?

?

?

Д1645

Дубовицкий

?

?

?

С 1767

Сидоров

?

?

?

Практическая работа 9

Тема:СВОДНЫЕ ТАБЛИЦЫ

Цель занятия: подвести итоги на основании данных списков, внешних баз данных.

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

Порядок работы

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

Рисунок 9.1 Список «Продукты»

2. Для создания сводной таблицы, выполните командуДанные/Сводная таблица. На экране появится диалоговое окноМастер сводных таблиц и диаграмм – шаг 1 из 3 (рис. 9.2). Данное окно предлагает создать сводную таблицу на основе списка.

Рисунок 9.2 – Диалоговое окно Мастера сводных таблиц

Рисунок 9.3 – Второе диалоговое окно Мастера сводных таблиц

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

4. Нажмите кнопкуДалее. Появится диалоговое окно (рис. 9.4), в котором требуется указать расположение сводной таблицы и щелкнуть по кнопкеМакет. Откроется диалоговое окно Мастера сводных таблиц, в котором создается макет будущей сводной таблицы (рис. 9.5). В правой части окна расположены кнопки, соответствующие полям списка. Эти кнопки с помощью мыши можно перетаскивать на белые поляСтрока, Столбец, Данные.

Рисунок 9.4 Третье диалоговое окно Мастера сводных таблиц

Рисунок 9.5 – Общий макет таблицы

Рисунок 9.6 – Макет сводной таблицы к списку Продукты

  1. Перетащить мышью кнопкуНазвание на полеСтрока, Дата – на полеСтолбец, Стоимость – на полеДанные(рис. 9.6). Щелкнуть на кнопкуOK.

Рисунок 9.7 Сводная таблица к списку Продукты

6. На экран вернется третье диалоговое окно Мастера сводных таблиц. Щелкнуть на кнопкуГотово. На экран выведется сводная таблица, в которой будут отражены суммарные расходы на продукты по трем дням и на каждый продукт в отдельности. (рис 9.7).

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

7. Выполнить автоформатирование полученной сводной таблицы командойФормат/Автоформат.

8. Внесите изменения в исходные данные и выполните командуДанные/Обновить данные.

Практическая работа 10

Тема: КОМПЛЕКСНОЕ ИСПОЛЬЗОВАНИЕ ПРИЛОЖЕНИЙMICROSOFTOFFICE ДЛЯ СОЗДАНИЯ ДОКУМЕНТОВ

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

Порядок работы

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

Задание 10.1.Создать таблицу расчета прибыли фирмы, произвести расчеты суммарных доходов, расходов (прямых и прочих) и прибыли; произвести пересчет прибыли в условные единицы по курсу (рис. 10.1). Выясните, при каком значении зарплаты прибыль будет равна 500000 руб. (используйте режимПодбор параметра).

Краткая справка. Формулы для расчета:

Расходы: всего = Прямые расходы + Прочие расходы;

Прибыль = Доходы: всего Расходы: всего;

Прибыль (у. е.) = Прибыль * Курс 1 у. е.

Рисунок 10.1 Исходные данные для задания 10.1

Задание 10.2.Создать «Акт о порче товарно-материальных ценностей».

Текстовую часть документа создайте в текстовом редактореMSWord, таблицу расчета стоимости товарно-материальных ценностей (ТМЦ) для списания создайте вMSExcel, проведите расчеты и скопируйте в текстовый документ.

Наименование организации________                                 «Утверждаю»

Отдел___________________________                   Руководитель организации

«_____» ______________200__г.

АКТ

О ПОРЧЕ ТОВАРНО-МАТЕРИАЛЬНЫХ ЦЕННОСТЕЙ

от «_____»_______________________200___г.

Комиссия в составе: председатель __________________, члены комиссии _________________________________________________ на основании приказа от _______ № _________ составила настоящий акт в том, что указанные ниже ценности пришли в негодность и подлежат списанию.

Наимено-вание

Ед.

измер.

Кол-

во

Стоимость

Причины и

характер

порчи и дата

заключения

Розничная

Оптовая

Цена

Стои- мость

Цена

Стои-мость

11.

Стол

Шт.

15

250

?

№ 7 от 15.03.04

22.

Стулья

Шт.

28

45

?

№ 2 от 15.02.04

33.

Скатерти

Шт.

45

20

?

№ 1 от 15.01.04

44.

Шторы

Шт.

10

75

?

№ 5 от 1.03.04

55.

Двери

Шт.

12

120

?

№ 8 от 5.03.04

66.

Компьютер

Шт.

1

5200

?

№ 9 от 15.04.04

77.

Калькулятор

Шт.

5

100

?

№ 11 от 15.04.04

88.

Телевизор

Шт.

1

4300

?

№ 12 от 15.04.04

Итого по акту  ___________ наименований на сумму ______руб. ______коп.___________________________________________________________________(прописью по розничным ценам и по ценам приобретения)

Председатель комиссии_________________________________________

Задание 10.3.Создать по образцу бланк счета для аренды автомобиля вMSExcel.

Автомобиль использовался с 12.10.04 00:00 до 14.10.04 16:40.

Тарифная ставка «120 р./ч.»

Краткая справка. Для ввода даты используйте функцию «Сегодня». При вводе периода аренды автомобиля используйте формат «Дата», в котором присутствуют дата и время.

Рисунок 10.5 Исходные данные для задания 10.5

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

Порядок работы

Для подсчета количества часов аренды автомобиля установите в ячейке «Итого» числовой формат,  рассчитайте разницу дат пользования (Дата по: — Дата с:). Вы получите количество дней пользования автомобилем. Для перевода количества дней пользования автомобилем в часы произведите умножение на 24.

Расчет суммы счета сделайте по следующей формуле:

Всего = «Тариф за час» * Итого.

Коротко о главном

Табличный процессорMSExcel входит в состав пакета прикладных программMicrosoftOffice, работающего под управлением операционной системыWindows.

Современные табличные процессоры ориентированы на выполнение следующих функций:

  • табличные вычисления;
  • деловая графика;
  • возможность работы с таблицей, как с базой данных;
    • многовариантность выполнения операций, т.е. практически все операции обработки могут быть выполнены несколькими способами, задача пользователя – выбрать для себя наиболее удобный;
    • быстрый доступ к наиболее часто используемым командам и функциям;
    • возможность оформления и создания шаблонов рабочих листов, которые используются для создания бланков писем, факсов, различных фирменных документов;
    • возможность построения различного рода диаграмм, придав им самое разное оформление.

С помощьюMSExcel создается документ, который называется электронной таблицей.

Электронная таблица популярное средство информационных технологий, предназначенное для организации табличных вычислений на ЭВМ.

Основные режимы работы: готовность, ввод, правка; в исходном состоянии ЭТ находится в режиме отображения значений.

Основные типы данных: числа, текст, формулы, «дата», логические значения.

Диаграммыэто графическое представление данных. Они используются для анализа и сравнения данных, представления их в наглядном виде.

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

Диаграмма создается с помощьюМастера диаграмм,вызываемого командойВставка, Диаграммаиликнопкой           на панелиСтандартнаялибо кнопкой         на панелиДиаграмма.

Список данных – это однотабличная база данных, создаваемая в средеMSExcel. Оформляя список данных, следует придерживаться некоторых правил:

  • размещать только один список на рабочем листе;
  • отделять дополнительную информацию пустой строкой или столбцом;
  • исключать пустые строки и столбцы в самом списке.

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

Если требуется отсортировать записи по нескольким полям, то следует выполнить командуДанные, Сортировка. В открывшемся окнеСортировка диапазона можно указать до трех полей, по которым возможна одновременная сортировка.

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

Кроме фильтрации списка по одному критерию возможна фильтрация по нескольким критериям. Для этого необходимо применить пользовательский фильтр. С помощью пользовательского фильтра можно задавать два условия по одному полю или последовательно вводить условия в нескольких полях.

Сводная таблица – это таблица, объединяющая множество из одного или нескольких списков и подводящая итоги по заданным полям.MSExcel строит сводные таблицы с помощью Мастера сводных таблиц и диаграмм.

ЛИТЕРАТУРА

Основная

  1. Советов Б.Я., Цехановский В.В. Информационные технологии: учебник для вузов. – 3-е изд., стер. – М.: Высш. шк., 2006.
  2. Макарова Н.В. Информатика: практикум. – 3-е перераб. изд. – М.: Финансы и статистика, 2008.
  3. Семакин И.Г., Хеннер Е.К. Информационные системы и модели. Эффективный курс: учебное пособие. – М.:БИНОМ. Лаборатория знаний, 2005.

Дополнительная

  1. Уткин В.Б., Балдин К.В. Информационные системы в экономике: учебник для студ. высш. учеб. заведений. М.: Издательский центр «Академия», 2004.
  2. Филимонова Е.В. Информационные технологии в профессиональной деятельности: учебник. – Ростов н/Д: Феникс, 2004.
  3. Михеева Е.В. Практикум по информационным технологиям в профессиональной деятельности: учебное пособие. – 3-е изд. М.: Издательский центр «Академия», 2005.
  4. Безручко В.Т. Практикум по курсу «Информатика». Работа вWindows,Word,Excel: учебное пособие. – М.: Финансы и статистика, 2002.
  5. Ефимов Е.Н., Патрушина С.М., Панферова Л.Ф., Хашиева Л.И. Информационные системы в экономике. – Москва: ИКЦ «МарТ»; Ростов н/Д: Издательский центр «МарТ», 2004.
  6. Романов А.Н., Одинцов Ю.Е. Информационные системы в экономике (лекции, упражнения и задачи): учеб. пособие. – М.: Вузовский учебник, 2006.
  7. Макарова Н.В. Информатика: учебник. – 3-е перераб. изд. – М.: Финансы и статистика, 2008.
  8. Петров В.Н. Информационные системы. СПб.: Питер, 2002.
  9. Черенков А.П. Информационные системы для экономистов: учебное пособие. М.: Издательство «Экзамен», 2002.
  10. Яковлева А.В. Информационные технологии в экономике: пособие для сдачи экзамена. – М.: Юрайт – Издат, 2005.

Андреева Наталия Васильевна

Кучерова Виктория Викторовна

Попов Алексей Александрович

Основы создания документов

в табличном процессореMicrosoftExcel

Корректор – Н.А. Иванов

Подписано в печать 11.01.10

Формат 6090 / 16. Усл. печ. л. 9,5. Печать оперативная.

Бумага офсетная. ГарнитураTimes.

Заказ № 3508. Тираж 300 экз. (1-ый завод 100)

Издательский центр ОГАУ

460795, г. Оренбург, ул. Челюскинцев, 18.

Тел.: (3532) 77-61-43

Основы создания документов в табличном процессоре Microsoft EXCEL на http://mirrorref.ru


Похожие рефераты, которые будут Вам интерестны.

1. ОРГАНИЗАЦИЯ РАСЧЕТОВ В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL

2. Организация расчетов в табличном процессоре MS Excel

3. Организация представления информации в табличном процессоре

4. Изучение технологии экономических расчетов в табличном процессоре

5. Общие сведения о текстовом процессоре Microsoft Word

6. Основные функции Microsoft Excel

7. Создание деловых документов в редакторе Microsoft Word

8. Дослідження статистичних функцій Microsoft Excel

9. Решение задачи оптимизации в Microsoft Excel

10. Создание комплексных документов в текстовом редакторе Microsoft Word