|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
|
|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
Лабораторная работа №1 Редактирование рабочей книги. Построение диаграммСтр 1 из 6Следующая ⇒
Лабораторная работа №1 Редактирование рабочей книги. Построение диаграмм Цель работы: Изучение способов работы с данными в ячейке. Изучение возможностей автозаполнения. Построение диаграмм.
Задание 1. Создание и сохранение таблицы (рабочей книги). Форматирование содержимого ячеек, выбор диапазона ячеек и работа с ними, редактирование содержимого ячеек. Методика выполнения работы 1. На рабочем столе найти ярлык Microsoft Excel и открыть окно двойным щелчком левой клавиши мыши или ПускÞ ПрограммыÞ Microsoft OfficeÞ Microsoft Excel. 2. Переименуйте текущий лист Главная Þ Формат Þ Переименовать лист, введите название листа «Ведомость». 3. Добавьте еще один лист в рабочую книгу (щелкните правой кнопкой мыши на ярлыке листа и в контекстном меню выберите команду Вставить). 4. Сохраните созданный вами файл под именем book.xls в своем каталоге ( Кнопка Office Þ Сохранить ). 5. Создайте таблицу по предложенному образцу (табл. 5.1). Для этого нужно выполнить следующие действия: Таблица 5.1 Экзаменационная ведомость
6. В ячейку А1 ввести заголовок «Экзаменационная ведомость», выделить мышкой ячейки А1: E1 протащив по ним указатель, щелкните на ленте Главная на кнопке Объединить и поместить в центре в ячейку A3 ввести «№ п/п»; в ячейку ВЗ ввести «Фамилия, имя, отчество»; в ячейку СЗ ввести «№ зачетной книжки»; в ячейку D3 ввести «Оценка»; в ячейку ЕЗ ввести «Фамилия экзаменатора».
Рисунок 5.44 – Группа инструментов выравнивания
7. Отформатируйте ячейки шапки таблицы: - выделите блок ячеек (АЗ: ЕЗ); - лента Главная перейдите к группе Выравнивание; - на панели Выравнивание (рис. 5.44) щелкните на кнопках Выровнять по середине, По центру и Перенос текста , а на вкладке Шрифт измените начертание букв и размер шрифта. 8. Измените ширину столбцов, в которые не поместились введенные данные. Для этого можно перетащить границы между строками и столбцами или навести указатель мыши на границу между заголовками столбцов, дважды щелкнуть основной кнопкой мыши. Для более точной настройки надо выполнить команду Формат Þ Высота строки (Ширина столбец) из группы Ячейки. 9. Выполните обрамление таблицы: выделите таблицу, выполните команду Шрифт Þ Список Границы и с помощью соответствующих кнопок установите границы. 10. Присвойте каждому студенту свой порядковый номер, используя маркер заполнения. Для этого: - сделайте текущей первую ячейку столбца «№ п\п» и введите в нее цифру 1; - затем заполните цифрой 2 следующую ячейку этого столбца; - выделите блок, состоящий из двух заполненных ячеек; - - или выберите команду Главная Þ Редактировать Þ Заполнить. 10. Заполните столбец «Фамилия экзаменатора». Воспользуйтесь методом автозавершения, который состоит в том, что Excel «угадывает» слово, которое собирается вводить пользователь, или заполните ячейки с помощью маркера заполнения. 11. Скопируйте таблицу на другой рабочий лист при помощи буфера обмена. Для этого следует: - выделить таблицу или диапазон ячеек; - правой клавишей мыши вызвать контекстное меню; - выполнить команду Копировать ; - затем перейти на другой лист; - установить курсор в первую ячейку предполагаемой таблицы; - выполнить команду Вставить из контекстного меню. 12. Добавьте в новую таблицу одну строку и один столбец. Для этого нужно: - выделить столбец; - щелкнуть правой кнопкой мыши на выделенном диапазоне и в открывшемся контекстном меню выбрать команду Вставить; то же самое повторить для строки. 13. Внесите в таблицу ряд изменений: - озаглавьте последнюю строку – «Средний балл»; - озаглавьте последнюю колонку - «Подпись экзаменатора». 14. Вычислите под столбцом «Оценка» средний балл, набрав там формулу =.
Задание 2. На основе данных, приведенных в табл. 5.2, постройте несколько типов диаграмм, наглядно показывающих итоги сессии.
Таблица 5.2
Методика выполнения работы 1. На листе 3 создайте таблицу «Сведения о результатах сдачи сессии на факультете», внесите в нее данные. 2. Постройте диаграмму типа Гистограмма или График для всех групп и всех предметов на отдельном листе. Для этого следует: - выделить всю таблицу; - выполнить команду меню Вставка Þ Диаграмма и выбрать вид диаграммы, после этого откроется полотно для построения диаграммы и три ленты (конструктор, макет и формат) с инструментами для редактирования диаграммы (рис. 5.45).
а)
б)
в) Рисунок 5.45 – Ленты с инструментами: а – Конструктор диаграмм, б– Макет диаграммы, в – Формат диаграммы
3. На шаге 2 можно изменить диапазон ячеек, по которым строится диаграмма, задать другие имена для рядов данных, добавить или удалить ряды данных, изменить диапазон подписей по оси Х или Y (рис. 5.45). Команда Данные Þ Выбрать данные, выделите таблицу полностью(рис. 5.46 ). Рисунок 5.46 – Диалоговое окно определения рядов данных 4. На третьем шаге построения диаграммы внесите название диаграммы, обозначения осей, добавьте легенду (рис. 5.47). Команда Макет Þ Подписи.
Рисунок 5.47
5. Постройте диаграммы других типов и сравните результаты сдачи по предметам: информатика, математический анализ и экономика. Для этого выделите столбцы «Группа», «Информатика», «Математический анализ» и, удерживая клавишу Ctrl, выделите столбец «Экономика»; выберите тип диаграммы График. 6. Измените, результаты сдачи сессии и проверьте, как это отразилось на построенных диаграммах. 7. На листе 4 постройте таблицу следующего вида (рис. 5.48):
Рисунок 5.48 – Макет таблицы 8. Команда Формулы Þ Библиотека функций. В ячейку В3 введите формулу =МАКС(Лист3! B3: B9); в ячейку В4 формулу =МАКС(Лист3! C3: C9) и т.д. по всем предметам. 9. В ячейку С3 введите формулу =МИН(Лист3! B3: B9) и т.д. по всем предметам. Постройте отдельно графики по максимальным средним баллам и по минимальным. 10. Отчет о работе представьте в виде диаграмм на отдельных листах рабочей книги.
Методика выполнения работы. 1. Откройте новую рабочую книгу и сохраните ее в своей папке. 2. Создайте таблицу, внесите в нее исходные данные задачи (рис. 5.49). Рисунок 5.49 – Макет таблицы 3. Для подсчета выручки от продажи в долларах в ячейки столбца внесите соответствующие формулы. В формулах использована относительная адресация ячеек. Формула вводится лишь в одну ячейку, а остальные формулы в столбце получены при помощи автозаполнения. 4. Подсчитайте выручку от продажи в рублях. В формулах использована смешанная и абсолютная адресация ячеек. Для введения абсолютного и смешанного адреса необходимо после введения ссылки нажать клавишу F4 и выбрать из предлагаемых вариантов нужный. 5. Подсчитайте сумму выручки от продажи всех видов товаров. Выделить столбец и нажать кнопку Автосумма на ленте Формулы. .
Методика выполнения работы 1. Открыть новую рабочую книгу. Листу 1 присвоить имя «Продажа служащими ». Заполнить этот лист, как показано ниже на рисунке 5.50.
Рисунок 5.50 – Макет таблицы 2. Для того, чтобы отсортировать данные выполните команду Данные Þ Сортировка. Откроется окно «Сортировка» (рис. 5.51). В поле «Сортировать по» установите имя столбца «Имя», щелкнуть Добавить уровень, в поле «Затем по» - имя столбца «Дата». Нажать ОК.
Рисунок 5.51 3. Промежуточные итоги. Выполнить команду ДанныеÞ Структура Þ Промежуточные итоги. Откроется диалоговое окно (рис. 5.52). Установить в поле При каждом изменении в столбец «Имя», в поле Операция - сумма, в поле Добавить итоги по - столбец «Цена», нажать ОК. Повторить команду ДанныеÞ Структура Þ Промежуточные итоги. В поле Операция выбрать «среднее», отменить флажок на опции «Заменить текущие итоги» и нажать ОК.
Рисунок 5.52 – Окно параметров промежуточных итогов 4. Сделать самостоятельно сортировку и подсчитать промежуточные итоги по Категории и Магазину. 5. Щелкнув левой клавишей мыши на имени столбца, выделить столбец «Магазин», захватить его мышкой и перетащить на столбец А. 6. Добавим столбец, содержащий количество проданных фильмов: щелкнуть правой клавишей мыши на имени столбца «Категория», выбрать команду Вставить. Ввести название нового столбца – «Кол-во». Заполнить этот столбец числами в пределах 20. В столбце Н ввести название «Продажа». Этот столбец заполнить формулами, выполняющими действие Кол-во*Цену. Примерный вид новой таблицы показан на рисунке 5.53.
Рисунок 5.53 – Данные для добавления
7.
Рисунок 5.54 - Окно Форма Щелкая на кнопках «Назад» и «Далее» можно просматривать записи из нашей таблицы. Щелчок на кнопке «Добавить» откроет, пустую форму для ввода. Введите в таблицу дополнительные записи (записи приводятся далее):
Перемещать курсор между полями мышкой или клавишей Tab. После ввода закрыть окно формы и просмотреть, добавились ли ваши записи в таблицу. 8. Выборка по критериям.Выполнить команду Данные Þ Фильтр . Обратите внимание, что справа от названий столбцов вашей таблицы появилась кнопка для раскрытия списка. Откройте список в столбце «Имя» и установите выборку по «Ольге», выбрав ее из списка. Выборка должна иметь следующий вид (рис. 5.55):
Рисунок 5.55 – Таблица с автофильтром Выведите выборку на печать. Отменить выборку, можно командой ДанныеÞ Очистить. Сделайте подобно и распечатайте выборку по наименованию любого из фильмов. Выборка по условию: из списка критериев столбца «Кол-во» выберите «Числовые фильтры» и введите условие > 5. Выведите на печать. Из списка критериев столбца «Дата» выберите критерий «Фильтры по дате» и введите условие > =10.янв.06 и < =12.янв.06. Выведите на печать. Очистите выборку.
Задание 1. Выполнить несколько раз сортировку данных таблицы 5.5 в соответствии со следующими признаками - в алфавитном порядке фамилий покупателей, по убыванию суммы сделки, по возрастанию даты сделки, по совокупности признаков (фамилия, дата, сумма). Методика выполнения работы 1. Откройте новую рабочую книгу и сохраните ее под именем «Сортировка» в своей рабочей папке. 2. Создайте таблицу представленную на рисунке 5.56.
Рисунок 5.56 – Начальная таблица с данными 3. Задайте параметры форматирования для таблицы. Шрифт Times New Roman, размер шрифта 12 пт., для заголовков начертание полужирное и выравнивание по центру, перенос по словам, заливка серым цветом; для основной части. Напоминаем, что команды форматирования доступны на ленте Главная Þ Ячейки . 4. Для выполнения сортировки по полю фамилия покупателя поставьте курсор в любое место этого столбца и выполните команду Данные Þ Сортировка (рис. 5.51) . В открывшемся диалоговом окне в поле Сортировать по выберите «Фамилия покупателя». По возрастанию. 5. Повторите все шаги пункта 4 и задайте сортировку по «Сумме сделки», по убыванию. 6. Выполните повторно сортировку по полю «Дата сделки», по возрастанию. 7. Скопируйте таблицу на новый лист и на нем выполните сортировку по совокупности признаков. Для этого вызовите команду Данные Þ Сортировка. Установите Сортировать по фамилии в порядке возрастания, Затем по дате в порядке возрастания, В последнюю очередь, по сумме в порядке убывания. 8. С помощью команды Переименовать присвойте имена этим двум листам. Задание 2. Осуществите выборку информации из списка на основе команды Автофильтр. Методика выполнения работы. 1. На листе 4 создайте таблицу и заполните ее сведениями из таблицы 5.5. 2. Переименуйте Лист4, присвоив ему имя «Автофильтр №1». 3. Чтобы применить Автофильтрацию, установите курсор в область списка и выполните команду Данные Þ Фильтр. Рядом с названиями граф таблицы появятся стрелки направленные вниз, которые раскрывают список возможных значений. В графе «Пол» выберите «М» Скопируйте таблицу на лист 5 и переименуйте его в «Автофильтр №2». 4. На листе «Автофильтр №1» в графе «Пол» откройте список фильтрования и выберите «Все». Затем в графе «Дата рождения» выберите в списке фильтрования «Условие» и задайте условие (рис.5.57):
Таблица 5.5
Рисунок 5.57 – Задание условий фильтрации
5. Скопируйте отфильтрованную таблицу на лист 6 и переименуйте его в «Автофильтр №3. На листе «Автофильтр №1» отмените выборку.
Рисунок 5.58 – Пользовательский фильтр
6. В столбце «Фамилия» выберите в списке фильтрации «Условие» и задайте условие на отбор всех сотрудников, чья фамилия начинается на «Б» (рис.5.58). 7. Скопируйте отфильтрованный список на лист 7 переименуйте его в «Автофильтр №4». 8. На листе «Автофильтр №1» для графы «Фамилия» задайте «Все», а в графе «Оклад» задайте «Первые 10…» где в диалоговом окне введите «Показать 5 наибольших элементов списка». 9. Сохраните файл. Задание 3. Выполните отбор записей, из списка используя команду Расширенный фильтр.
Методика выполнения работы. 1. Перейдите на лист 8 и переименуйте его в «Расширенный фильтр». 2. Скопируйте на этот лист таблицу из предыдущего задания (табл. 5.5), вставьте ее, начиная со строки 7. Первые 6 строк отводятся для задания условий. 3. Создадим диапазон условий. Предположим, нам требуется отобрать фамилии сотрудников, которые получают больше 5000 руб. Или чей возраст превышает 50 лет. Заполните условия, как показано на рисунке 5.59.
Рисунок 5.59 – Условия для расширенного фильтра
4. Выполните команду Данные Þ Дополнительно. Заполните диалоговое окно следующим образом (рис. 5.60):
Рисунок 5.60 – Окно параметров расширенного фильтра
Просмотрите результаты отбора. При записи условий в одной строке реализуется логическое И. При записи условий на разных строках они считаются связанными логическим ИЛИ. Мы рассмотрели первый вариант, теперь рассмотрим второй. 5. Предположим нам требуется вывести только тех сотрудников, фамилии которых начинаются с букв А, Г или Н. Заполните диапазон условий (рисунок 5.61).
Рисунок 5.61 – Условия для расширенного фильтра
6. Выполните команду ДанныеÞ Дополнительно и заполните диалоговое окно (рисунок 5.62).
Рисунок 5.62 – Окно параметров расширенного фильтра
Просмотрите результаты отбора записей. 1. Выведите список всех сотрудников, заработная плата которых больше среднего уровня. Перед созданием этого фильтра введите в ячейку H2 формулу =СРЗНАЧ(F8: F20) для вычисления среднего оклада. 2. Затем в ячейку А2 вводим вычисляемое условие =F8> $H$2, ссылающееся на ячейку H2 (рисунки 5.63 и 5.64).
Рисунок 5.63 – Условия для расширенного фильтра
Рисунок 5.64 – Параметры расширенного фильтра
Задание 1 1. Для студентов планируется поездка во Францию, но поедут только студенты, у которых средний балл более или равен «4», нет неудовлетворительных оценок и по иностранному языку «5». Создать функцию автоматического определения претендентов на поездку. 2. Подсчитать в каждой группе количество «5», «4», «3» и «2». 3. Определить «Худшую группу» по максимальному количеству «2» и «Лучшую группу» по максимальному количеству «5». При выполнении вычислений применять операцию «Присвоение имени блоку ячеек». Методика выполнения работы 1. На новом листе рабочей книги создайте таблицу по образцу таблицы 5.6. Таблица 5.6 2. В столбец «Ср. балл» введите формулу, для этого воспользуйтесь кнопкой Вставить функцию в строке ввода и редактирования формул или командой Формулы Þ Библиотека функций . Выберите категорию функций Статистическая и функцию СРЗНАЧ. В открывшемся диалоговом окне введите диапазон ячеек С5: Е5. Щелкните ОК и скопируйте формулу вниз до ячейки F24. 3. С помощью функций из категории Логические создадим в ячейке G5 логическое выражение =ЕСЛИ(И(F5> =4; E5=5; C5< > 2; D5< > 2; E5< > 2); " Едет"; " Не подходит" ) Скопируйте формулу до ячейки G24. 4. Для подсчета количества человек, которые едут во Францию, а также количества различных оценок создайте на листе таблицу в соответствии с таблицей 5.7. Таблица 5.7 5. Введите формулу подсчета количества студентов подходящих для поездки в ячейку В26 =СЧЁТЕСЛИ(G5: G24; " Едет" ) Функция СЧЕТЕСЛИ находится в категории Статистические. 6. В ячейку В27 введите формулу =СЧЁТЕСЛИ(G6: G25; " Не подходит" ) 7. Количество полученных оценок определенного вида будем подсчитывать в ячейках С30: F33, используя уже знакомую нам функцию СЧЕТЕСЛИ. Введем в С30 формулу =СЧЁТЕСЛИ($C$5: $E$8; B30). Абсолютные ссылки (знак $) здесь применяются для удобного последующего копирования. Блок ячеек охватывает все оценки группы Б124, а ячейка В30 указывает на то, что подсчитываются оценки «отлично». Аналогично введите формулы подсчета «5» для других групп в ячейках D30: F30. Затем скопируйте ячейки С30: F30 вниз до 33 строки. Формула должна автоматически настроиться под другие диапазоны ячеек. 8. Присвойте имена блокам ячеек С33: F33 и С30: F30, содержащим количество двоек и пятерок по группам соответственно, как «Двойки» и «Пятерки». Для этого блок ячеек предварительно выделяется затем выполняется команда Формулы Þ Определенные имена Þ Присвоить имя . 9. В ячейку D35 введите формулу =ПРОСМОТР(МАКС(Двойки); Двойки; C29: F29) Функция МАКС находится в категории Статистические, а функция ПРОСМОТР в категории Ссылки и массивы.
С помощью справки изучите принцип работы функции ПРОСМОТР! Ответь на вопрос: для чего в функции используется блок ячеек С29: F29?
10. В ячейку D36 аналогично пункту 9 введите формулу для определения лучшей группы по количеству пятерок. Задание 1 Определить стоимость объектов недвижимости (табл. 5.10) на основе их первоначальной стоимости и таблицы скидок за продолжительность эксплуатации (табл. 5.11), т.е. начиная с 1 года – 5%-ая скидка, с 5 лет – 10%-ая скидка и т.д. Таблица 5.10
Таблица 5.11
Методика выполнения работы 1. Создать представленные таблицы на одном рабочем листе Excel. 2. Присвоить диапазонам ячеек имена: - выделить блок ячеек, содержащий значения начальной стоимости и выполнить команду Формулы Þ Присвоить имя. Задать имя Первоначальная_стоимость (обратите внимание, что в именах не должно быть пробелов); - выделите блок ячеек, содержащих значения года создания и выполните команду Формулы Þ Присвоить имя. Задать имя Год_создания; - таким же образом присвоить имя диапазону Год эксплуатации 3. Выделите блок ячеек, содержащий таблицу 5.11, и присвойте ему имя Справочная_таблица. Далее воспользуемся функцией ГПР. Функция ГПР берет значение из первого диапазона, сравнивает со значениями первой строки второго диапазона, находит равное ему или ближайшее наибольшее, а затем в качестве результата берет значение из указанной строки второго диапазона, в нашем примере - строка 2. Подобным образом работает функция ВПР, но она работает с диапазонами ячеек, расположенными вертикально. 4. В первую ячейку, находящуюся на пересечении строки с годом 2000 и столбца – год 2005 введите формулу =Первоначальная_стоимость*(1-ГПР(Год_эксплуатации-Год_создания; Справочная_таблица; 2)). скопируйте формулу с помощью автозаполнения в другие вычисляемые ячейки Задание 2 Необходимо создать две связанные таблицы для учета отдыхающих в Доме отдыха. В зависимости от категории комнаты и количества дней проживания рассчитать сумму оплаты. Выполнить поиск, и подстановку значений в таблицу используя функцию ПРОСМОТР. При этом количество дней проживания определяется как разница между днем отъезда и днем заезда. Методика выполнения работы 1. Создайте новую рабочую книгу под именем «Дом отдыха». 2. Переименуйте первый лист в «Сведения» и заполните лист (рис.5.71). 3. Присвойте имена диапазонам командой Формулы Þ Присвоить имя . a) А2: А31 - комнаты б) В2: В31 - категория в) D2: D7 – цена_категория г) Е2: Е7 – цена д) G2 – курс е) G5 – сегодня
Рисунок 5.71 – Таблица для заполнения
4. Перейдите на лист 2, переименуйте его в «Заезды». 5. Подготовить таблицу следующего вида (рис.5.72):
Рисунок 5.72 – Таблица для заполнения 6. Заполните столбец D (номер комнаты) данными по своему усмотрению, на основании данных листа «Сведения». 7. В ячейку Е2 введите формулу =ПРОСМОТР(D2; комнаты; категория) и скопировать ее вниз до 14 строки. 8. В ячейку F2 для определения этажа введите формулу =ЛЕВСИМВ(D2; 1) и скопируйте ее вниз. 9. В ячейку G2 для определения количества дней проживания вводим функцию, =ЕСЛИ(C2=0; сегодня-B2; C2-B2) 10. В ячейку H2 для определения суммы оплаты в $ введем формулу =ПРОСМОТР(E2; цен_категория; цена) 11. В ячейку I2 для определения суммы оплаты в рублях введем формулу =H2*Курс 12. Создать сводную таблицу, позволяющую определить общую сумму оплаты по месяцам заезда. Для этого установите курсор внутри таблицы «Заезды» и выполните команду Вставка Þ Таблица Þ Сводная таблица. Откроется макет для размещения полей. На ось строк перетащите поле Дата прибытия, а в область данных поля: Сумма в рублях и Сумма в $ (рис. 5.73).
Рисунок 5.73 – Макет сводной таблицы 13. Установите активную ячейку в поле даты на начальное значение и выполните команду Данные Þ Структура Þ Группировать. Заполните диалоговое окно (рис. 5.74) так, чтобы выполнялась группировка по месяцам и нажать ОК.
Рисунок 5.74 Сохраните файл.
Лабораторная работа № 7 Работа со сводными таблицами
Цель работы: освоить навыки создания, редактирования и анализа данных на основе сводных таблиц. Задание. Построить сводную таблицу для расчета месячной заработной платы рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцирована по разрядам: 2 разряд 25%, 3 разряд 32%, 4 разряд 50% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений. Методика выполнения работы 1. Открыть новую книгу. 2. Переименовать лист в Картотека. 3. Подготовить исходные данные (табл. 5.12). Таблица 5.12
4. Установить курсор в список, выполнить команду меню ВставкаÞ Сводная таблица для вызова Мастера сводных таблиц и диаграмм. 5. Указать тип источника – Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Выбрать вид создаваемого отчета – Сводная таблица. 6. Выполнить проверку диапазон выделенных ячеек списка. Диапазон включает имена столбцов и все заполненные строки таблицы. 7. Разместить поля в макете сводной таблицы: Фильтр отчета – Профессия, Названия строк – ФИО, Названия столбцов – Разряд работающего, Значения – Тариф, Операция – Сумма. Макет сводной таблицы представлен на рисунке 5.75.
Рисунок 5.75 – Макет сводной таблицы
8. На ленте Конструктор выполнить команды: Общие итогиÞ Включить по столбцам; Выбрать стиль сводной таблицы. На ленте Параметры: Сводная таблицаÞ Параметры. В открывшемся окне задать Для пустых ячеек отображать – пробел; Сохранять форматирование ячеек. Нажать кнопку ОК . В сводной таблице (рис. 5.76) представлен список всех работающих. Для каждого работающего указан только один тариф, соответствующий его разряду. Для преобразования сводной таблицы следует: 1. Установить курсор в область сводной таблицы. 2. Выполнить команду Параметры Þ Формулы Þ Вычисляемое поле для создания вычисляемого поля.
Рисунок 5.76 – Сводная таблица
3. На рис. 5.77 представлено диалоговое окно для формирования вычисляемого поля. Имя поля – Зарплата, Формула вычисления: =Тариф*168. (Коэффициент 168 зависит от количества рабочих часов в текущем учетном периоде.). Для добавления поля в формулу можно воспользоваться кнопкой Добавить.
Рисунок 5.77 – Создание вычисляемого поля 4. Установить курсор в область сводной таблицы. 5. С помощью кнопки Список полей на ленте Параметры откройте макет сводной таблицы для корректировки. 6. Удалить поле Сумма по полю Тариф (простым перетаскиванием мышкой за поле окна). 7. Установить курсор в области сводной таблицы на поле Сумма по полю Зарплата. 8. На ленте Параметры выполнить команду Активное поле Þ Параметры поля (рис. 5.78): - Изменить имя поля в сводной таблице – Месячная зарплата. Нажать кнопку Числовой формат и указать формат поля – Денежный. - Нажать кнопку ОК.
Рисунок 5.78 – Задание параметров вычисляемого поля
9. Установить курсор в область сводной таблицы на поле Разряд работающего. 10. Создать вычисляемый объект Премия. Премия выплачивается как процент к начисленной заработной плате, дифференцируется по разрядам: 2 разряд – 25%, 3 разряд – 32%, 4 разряд – 50%, 5 разряд – 55%. - На ленте Параметры выполнить команду Формулы Þ Вычисляемый объект (рис. 5.79). Указать имя объекта – Премия. - Для построения формулы в окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы - Формула: = ‘2’*0, 25+’3’*0, 32+’4’*0, 5+’5’*0, 55 - Нажать кнопку Добавить. - Закрыть окно – кнопка ОК.
Рисунок 5.79 – Создание вычисляемого объекта
11. Установить курсор в область сводной таблицы на поле Разряд работающего. Создать вычисляемый объект Вычеты, сумма вычетов это 13% от суммы заработка и премии. - Выполнить команду Формулы Þ Вычисляемый объект . Указать имя объекта – Вычеты (рис. 5.80).
Рисунок 5.80 – Создание вычисляемого объекта
- В окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы для построения формулы вида: - Нажать кнопку Добавить. - Закрыть окно – кнопка ОК. 12. Выполнить команду ПараметрыÞ ФормулыÞ Вывести формулы для просмотра выражений вычисляемых полей и объектов (рис. 5.81). Если потребуется изменить нормативы (количество отработанных часов, % премии, % вычетов), следует отредактировать вычисляемые поля и объекты – команда меню ФормулыÞ Вывести формулы , вызывать поле/объект, внести изменения
Рисунок 5.81 – Вывод формул 13. Переименовать лист, содержащий сводную таблицу, присвоив имя, Сводная таблица 1. 14. Поставить курсор внутрь сводной таблицы и на ленте Конструктор выполнить команду Макет отчета . Выбрать тип отчета. 15. Поставить курсор внутрь сводной таблицы и щелкнуть на ленте Параметры кнопку Сводная диаграмма . 16. В готовой диаграмме перетащить Разряд работающего в область Поле ряда. Выбирая вид профессии просмотреть данные по различным профессиям. 17. Сохранить рабочую книгу.
Задание 1. Рассчитать ежемесячную выплату при изменяющейся ставке и сумме кредита. 2. Применить к ячейкам с отрицательными значениями Условное форматирование. 3. С помощью подбора параметра определить, какой кредит надо взять, если известно, сколько в месяц вы сможете выплачивать в течение определенного срока. Популярное:
|
Последнее изменение этой страницы: 2016-05-03; Просмотров: 2554; Нарушение авторского права страницы
; 
установите указатель мыши на правый нижний угол выделенного блока. Указатель мыши станет черным крестиком – это маркер заполнения. Перетащите маркер заполнения при нажатой правой кнопке мыши вниз; 
Ввод данных и просмотр с помощью формы. Достать на панель быстрого доступа кнопку Форма ( Кнопка Office Þ Параметры Þ Настройка Þ Все команды). Отсортировать таблицу по категории «Магазин». Открыть с помощью соответствующей кнопки окно работы с формой (рис.5.54).
