Заглавная страница Избранные статьи Случайная статья Познавательные статьи Новые добавления Обратная связь КАТЕГОРИИ:  Археология Биология Генетика География Информатика История Логика Маркетинг Математика Менеджмент Механика Педагогика Религия Социология Технологии Физика Философия Финансы Химия ЭкологияТОП 10 на сайте Приготовление дезинфицирующих растворов различной концентрацииТехника нижней прямой подачи мяча. Франко-прусская война (причины и последствия) Организация работы процедурного кабинета Смысловое и механическое запоминание, их место и роль в усвоении знаний Коммуникативные барьеры и пути их преодоления Обработка изделий медицинского назначения многократного применения Образцы текста публицистического стиля Четыре типа изменения баланса Задачи с ответами для Всероссийской олимпиады по праву 
Мы поможем в написании ваших работ! ЗНАЕТЕ ЛИ ВЫ?
Влияние общества на человека
Приготовление дезинфицирующих растворов различной концентрации Практические работы по географии для 6 класса Организация работы процедурного кабинета Изменения в неживой природе осенью Уборка процедурного кабинета Сольфеджио. Все правила по сольфеджио Балочные системы. Определение реакций опор и моментов защемления |
Создайте новую рабочую книгу.
Для создания файла необходимо: - выбрать пиктограмму в левом верхнем углу и выбрать команду «создать»:
-в открывшемся диалоговом окне, убедиться, что выделен вид документа «Новый документ» и нажать кнопку «Создать» 2. Сохраните на своем диске под именем: Зарплата.xls. Для сохранения необходимо: -выбрать пиктограмму в левом верхнем углу и выбрать команду «Сохранить как» и соответствующий формат:
- в диалоговом окне «сохранение документа» установить соответствующие реквизиты файла: своем диске и имя «Зарплата»
3. На первом рабочем листе создайте таблицу Начисления Ø Переименуйте «лист1» на лист «Начисления, для этого необходимо дважды щелкнуть левой клавишей мыши на ярлыке листа и ввести новое имя Ø Сформируйте таблицу, выполните вычисления по формулам, переименуйте рабочий лист – Начисления.
Для этого необходимо: -установить курсор в ячейку А1 и ввести: № - установить курсор в ячейку B1 и ввести: Фамилия, имя отчество -C1: оклад -D1:налоги -F1:вычеты по НДФЛ -G1:Сумма выдачи -H1:Премия -D2:Профс -E2:НДФЛ -A3:1 -A4:2 - объединить ячейки, для этого выделить D1:E1, и выбрать пиктограмму ленты «Главная»:
Аналогично объедините ячейки А1:А2, В1:В2, C1: C2, F1: F2, G1: G2,.H1:H2. - Установка переноса по словам, для этого выделить А1:Н2, выбрать команду контекстного меню (появляется при нажатой правой клавише мыши) «Формат ячеек»:, вкладку «Выравнивание»,и установить «галочку» в поле «Переносить по словам»
- Автозаполнение, для этого выделить блок А3:А4, установить курсор мыши в правый нижний угол выделенного блока, таки образом, чтобы курсор изменился на «черный крестик»:
и протянуть курсор мыши до ячейки А9. Автозаполнение можно использовать для копирования формул. - остальные данные ввести руководствуясь приведенной в начале формой таблицы. - Оформление границ, для этого выделить блок A1:H9 и выбрать кнопку ленты Главная «границы»:
Исходными данными являются: оклад сотрудников, вычеты по НДФЛ, размер премии (в процентах), именно эти данные введены в исходную таблицу. Ø Формулы вычислений: Профс. налог = 1% от оклада (оклад * 0,01) НДФЛ = (оклад – вычеты по НДФЛ)*0,13 Сумма к выдаче = оклад – налоги Для выполнения вычислений по приведенным формулам необходимо ввести в соответствующие ячейки таблицы формулы. Рассмотрим ввод формулы на примере первой формулы.
- установить курсор в ячейку D3. - вод формулы начинается со знака «=», поэтому введите этот знак - затем необходимо ввести соответствующе условию выражение: Профс. налог = 1% от оклада, т.е Профс. налог = (оклад * 0,01), для этого вместо слова «оклад» необходимо щелкнуть мышью по соответствующей ячейке (С3), затем ввести знак арифметической операции «*» и число 0,01, В результате должно получиться:
после чего нажать клавишу «Enter» на клавиатуре, что приведет к отображению результата вычисления в ячейке. - с помощью автозаполнения, т.е. установить курсор мыши в правый нижний угол ячейки D3, таким образом, чтобы курсор изменился на «черный крестик» и протянуть курсор мыши до ячейки D9. Аналогичным образом ввести остальные формулы. В результате должна получиться следующая таблица:
4. На втором рабочем листе создайте таблицу Премия. Ø Сформируйте таблицу Премия на основе таблицы Начисления. Для выполнения этого задания создадим связи между таблицами, по которым данные будут вставляться из листа “Начисления”. - создайте заголовок таблицы на листе «Премия»
В ячейке А2 будет располагаться формула = Начисления!В3, где В3 – адрес ячейки, в которой размещена первая фамилия сотрудника на листе “Начисления”. Формулу следует ввести по следующему алгоритму: - установить курсор в ячейку А2 и ввести «=» - перейти на лист Начисления и щелкнуть мышью по ячейке В3 сразу после этого нажмите клавишу «Enter» на клавиатуре. Скопируйте формулу вниз, так чтобы скопировались фамилии всех сотрудников. В графе Сумма, начиная с ячейки В2будет располагаться формула = Начисления!G3 * Начисления!H3. Скопируйте формулу вниз, так чтобы произвести подсчет суммы по каждому сотруднику. В результате должна получиться следующая таблица:
Ø Переименуйте рабочий лист – П ремия. 5. На третьем листе создайте таблицу К выдаче Ø Переименуйте лист из “Лист 3” в “К выдаче” Ø Сформируйте заголовок таблицы.
Ø Вставьте фамилии сотрудников со ссылкой на лист “Начисления”.
Ø Вычислите Сумму к выдаче, используя ссылки на лист “Начисления” и лист “Премия”. Ø В графе Сумма, начиная с ячейки В2 будет располагаться формула = Начисления!G3 + Премия!B2. Ø Скопируйте формулу вниз, так чтобы произвести подсчет суммы к выдаче по каждому сотруднику. В результате должно получиться:
6. Создайте диаграмму. Постройте круговую диаграмму, отражающую начисления каждого сотрудника, используя таблицу Начисления. Последовательность выполнения задания: Ø Выделить два столбца (ФИО и Сумма к выдаче). Для выделения двух несмежных столбцов необходимо осуществлять их выделение при нажатой клавише Сtrl Ø На ленте «Вставка» выбрать вид диаграммы: «Круговая», тип «Объемная»
Ø Добавить подписи данных, для этого вызвать контекстное меню диаграммы и выбрать команду «Добавить подписи данных»:
Ø Изменить формат подписей данных, для этого необходимо вызвать контекстное меню подписей данных:
Ø Выбрать в диалоговом окне:
Ø В результате должна получиться следующая диаграмма:
7. Переход в режим отображения формул По умолчанию, как вы уже успели заметить, в результате вычисления формул в ячейках отображается результат. Иногда бывает необходимо просмотреть сами тексты формул, например, в соответствии с требованиями контрольно работы необходимо вставить таблицу именно в таком виде, наряду с видом вывода результатов. В Excel предусмотрена возможность просматривать данные в режиме отображения формул. Для того чтобы перейти в этот режим необходимо выполнить следующее: - выбрать пиктограмму в левом верхнем углу и выбрать команду Параметры:
- В диалоговом окне «Параметры Excel» выбрать в левой стороне окна «дополнительно» и затем установить галочку в поле «Показывать формулы, а не их значение» и нажать ОК.
В результате должен получиться следующий вид таблицы:
В конце выполнения работы необходимо вернуть исходный режим отображения результатов вычислений, для этого необходимо снять галочку в поле«Показывать формулы, а не их значение» окна параметров Excelи нажать ОК.
Задание 2 Построение графика функции Составьте таблицу значений линейной функции y=3,5x-9,5. Используйте возможности автозаполнения и копирования при создании таблицы. Постройте график этой функции.
1. Откройте новую книгу и создайте следующую таблицу:
2. Выполните необходимые вычисления в ячейках В5:L5, в соответствии с формулой линейной функции y=3,5x-9,5 3. Постройте график, для этого: - выделите диапазон А4:L5; - вставка/график/график; -отредактируйте график, для этого выполните команду конструктор/данные/выбрать данные - в диалоговом окне «Выбор источника данных»(рис) удалите в поле «Элементы легенды» удалите ряд Х
Рис. Диалоговое окно Выбор источника данных
- в диалоговом окне «Выбор источника данных» в поле «Подписи горизонтальной оси» нажмите кнопку «Изменить», в открывшемся диалоговом окне введите ячейки В4:L4; - выполните дальнейшее редактирование графика в соответствии с рисунком:
Рис. График функции Самостоятельные задания Задание 1 Самостоятельно построить график квадратичной функции y = ax2+b, где коэффициенты функции выбрать в соответствии с последнее и предпоследней цифрами студенческого билета. Координаты Х выбрать таким образом, чтобы была видна вершина параболы и симметрично ее ветви.
Задание 2 Вычислите таблицу значений функции f(x,y)=x2-y2, где х меняется от –2 до 3 с шагом 0.25, а y от 0 до 2 с шагом 0.1. Результаты отобразите с тремя знаками после точки. Постройте график поверхности. Контрольные вопросы 1. Как добавить, переименовать рабочий лист?2 2. Какие виды диаграмм вы знаете? 3. Порядок построения диаграмм. 4. Как выполнить ссылку на ячейку с другого рабочего листа?
Лабораторная работа № 5 Цель: использовать функции списка. Научиться создавать структуру таблицы, подводить итоги в смежных диапазонах. Теоретические сведения Список – это табличная база данных, которая содержится в листе рабочей таблицы. Такая база данных состоит из имен полей, которые расположены в первой строке и информационных записей, находящихся в следующих строках. В списке не должно быть пустых столбцов или пустых строк. Над списками можно выполнять следующие операции: · Фильтрация данных в списке – это выбор данных по заданному критерию (условию). Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр. При выборе команды Данные/сортировка и фильтр/Фильтр автоматически устанавливается автофильтр.. Для обращения к расширенному фильтру следует выбрать команду Данные/сортировка и фильтр/дополнительно · Сортировка списка. Сортировка – это расположение данных в определенном порядке по возрастанию или убыванию. Сортировка выполняется командой Данные/ сортировка и фильтр/ Сортировка. · Вставка формул для подведения промежуточных итогов. · Создание с помощью сводной таблицы итоговой таблицы данных списка, отфильтрованного по определенным критериям. · Создание с помощью сводной таблицы итоговой таблицы данных списка. Подведение промежуточных итогов в смежных диапазонах осуществляется через команду меню Данные/Структура/промежуточные итоги. Порядок выполнения работы 1. Ввести список «Периферия», представленный ниже.
1. Подсчитайте сумму для каждого товара по формуле: Цена*Кол-во. 2. Отсортируйте записи по Товару в порядке возрастания, по полю Тип в порядке возрастания командой Данные/Сортировка. 3. Используя автофильтр Данные/сортировка и фильтр/Фильтр, отберите записи: · По полю Тип: Лазерные принтеры. Для этого перейдите на поле Тип. Раскройте список фильтрации и выберите Лазерные. На экране остались только данные по лазерным принтерам. Отмените фильтр (Данные / сортировка и фильтр /Очистить). · По полю Товар: Сканеры. Выполнить самостоятельно! Отмените использование фильтра. ·
Отмените использование фильтра. 4.
Результат:
Самостоятельно с помощью расширенного фильтра выберите Лазерные принтеры с ценой больше 1500 руб. 5. Создайте промежуточные итоги командой Данные/структура/промежуточные итоги. 5.1. Найдем суммы, затраченные отдельно на покупку всех принтеров и всех сканеров. Для этого: 1) Отсортируйте таблицу по товару. 2) В меню Данные / структура выберите промежуточные итоги. Откроется диалоговое окно Промежуточные итоги. 3) Для того, чтобы подвести итоги по каждому товару (отдельно принтеры и отдельно сканеры), в списке При каждом изменении в выберите Товар 4) Убедитесь, что в окне Операция выбрана Сумма 5) Для того, чтобы просуммировать показатели количества товара и сумм, затраченных на покупку, установите флажки в поле Добавить итоги по напротив строк Кол-во и Сумма. 6) Проверьте, что напротив строк Заменить текущие итоги и Итоги под данными установлены флажки и нажмите ОК. 7) Таким образом, вы получите итоговые значения количества и суммы для каждого типа товара. 8) Для удаления промежуточных итогов выберите команду Данные/структура/промежуточные итоги и щелкните кнопку Убрать все. Таблица вернется в исходное состояние. 5.2. Найдите среднее значение цены отдельно для всех принтеров и всех сканеров, а также общее среднее значение цены. Для этого: 1) Отсортируйте таблицу по товару.
2) Данные è Промежуточные итоги: 3) При каждом изменении è Товар 4) Операция èСреднее 5) Добавить итоги по èЦена 6) В конце не забудьте убрать промежуточные итоги. 5.3. Вычислите число разновидностей принтеров и сканеров. Для этого 1) Отсортируйте таблицу по товару. 2) Данные è Промежуточные итоги: 3) При каждом изменении è Товар 4) Операция èКол-во значений 5) Добавить итоги по èНаименование 6) В конце не забудьте убрать промежуточные итоги. 5.4. Самостоятельно вычислите среднее значение сумм, потраченных на покупку всех принтеров и всех сканеров. Покажите результат преподавателю!!! В конце не забудьте убрать промежуточные итоги. 5.5. Вычислите суммы, потраченные на покупку каждого типа товара (каждой разновидности принтеров и сканеров). 1) Отсортируйте таблицу по типу принтеров. 2) Данные è Промежуточные итоги: 3) При каждом изменении è Тип 4) Операция èСумма 5) Добавить итоги по èСумма 6. Часто при составлении итогового отчета нужно видеть только итоги, а остальные записи скрыть. Для этого в колонках с кнопками 1 2 3 можно поменять знак - на +. Такое можно провести для каждого уровня.
Воспользуйтесь результатами предыдущего задания. 1) Скройте результаты второго уровня: Щелкните по кнопке 2. 2) Для возврата таблицы в исходный вид щелкните по кнопке того уровня, который вы хотите показать (кнопка 3) 3) Попробуйте по очереди скрывать отдельные записи второго уровня (нажимайте на кнопку -) 4) Для возврата - на кнопку +. 5) Попробуйте поработать с кнопкой 1. 6) Верните таблицу в исходное состояние. 7) Уберите промежуточные итоги. Контрольные вопросы 1. Как работать с формой? 2. Какие виды фильтров вы знаете? Как работает расширенный фильтр? 3. Для чего нужна операция Промежуточные итоги? 4. Что такое структура? Какими способами создается структура?
Лабораторная работа №6 Цель: Научиться использовать сводные таблицы и операцию консолидации
Теоретические сведения Для подведения итогов данных, расположенных в несмежных диапазонах, например, на разных рабочих листах или в разных рабочих книгах используются команды Консолидация (Данные/Работа с данными/консолидация), так же для подведения итогов, в том числе в смежных диапазонах, используется Сводная таблица( Вставка/Сводная таблица ). Сводная таблица представляет собой кросс-таблицу, имеющую следующую структуру:
Рис. Структура сводной таблицы. В ячейках «Области данных» находятся данные соответствующие значению заголовков строк и столбцов, на пересечении которых находится ячейка. Данные в области данных могут быть агрегированными данными, т.е. вычисляться на основании исходных с использованием таких функций как, суммирование, нахождение количества элементов в группе, минимальное значение, максимальное значение.
Задание№1 Создание сводной таблицы 1. Введите следующие данные для создания сводной таблицы:
Создайте сводные таблицы, отвечая на следующие вопросы: 1. Какая общая сумма вклада для каждого из отделений и по каждому из типов счетов? 2. Как распределена сумма по разным типам счетов? 3. Какое отделение имеет больше Срочных счетов? Решение: Выберите команду Вставка/Сводная таблица, в открывшемся диалоговом окне в поле «Таблица или диапазон» укажите A1:F13 (т.е. диапазон, в котором расположена исходная таблица) В диалоговом окне «Список полей сводной таблицы» необходимо, в соответствии с рисунком, перетащить с помощью мыши поля из списка, распложенного в верхней части диалогового окна, в соответствующие поля, расположенные в нижней части: «Название строк»- Отделение, «название столбцов»-Тип, «S Значения»-Вклад
Рис. Список полей сводной таблицы По умолчанию по отношению к данным, расположенным в «Области данных» применяется наиболее подходящая функция, однако, через меню раскрывающегося списка поля «S Значения» (команда Параметры полей значений) можно выбрать другую функцию(см рис, расположенный ниже).
Рис. Диалоговое окно Параметры поля значений В результате выполнения указанных операций сформировалась Сводная таблица, представленная на следующем рисунке.
Данная сводная таблица позволяет ответить на поставленные ранее вопросы. Самостоятельное задание Создайте сводную таблицу, которая позволяет ответить на следующие вопросы: 1. Какое количество клиентов каждого типа(старый, новый) обслуживается в различных отделениях банка. 2.В какой день какое количество счетов было открыто в каждом отделении? 3.Каков максимальный размер вклада в каждом отделении?
Задание№2 Подведение итогов с помощью консолидации 1. Создание исходных диапазонов данных: -При необходимости добавьте в рабочую книгу рабочие листы, для создания исходных диапазонов данных необходимо три листа. -Переименуйте эти листы, дайте им соответствующие названия Магазин1, Магазин2, Магазин3, используя команду Переименовать из контекстного меню Ярлычков. -Затем объедините три листа в группу. Для этого нажмите клавишу Ctrl, и, удерживая ее нажатой, щелчком мыши выделите эти листы. -Затем введите данные. При вводе информации в группу, эта информация будет отображаться на всех листах группы. Затем разгруппируйте эти листы, выбрав в контекстном меню Ярлычков команду Разгруппировать. Немного измените данные на втором и третьем листах. Измените Код товара и Количество за любой месяц.
2. Консолидируйте следующие данные, размещенные на трех рабочих листах с помощью команды Консолидация При создании итоговой таблицы с помощью команды Данные/Работа с данными/Консолидация перейдите на новый лист и выберите команду. В окне этой команды в списке Функция выберите нужную функцию, например, Сумм, затем поместите указатель мыши в поле Ссылка, затем щелкните мышью на ярлычке листа Магазин1 и выделите диапазон ячеек с данными для консолидации (выделяйте все данные, включая заголовки), вернитесь на лист, где создается консолидация, нажмите кнопку Добавить, затем укажите на данные листа Магазин2, нажмите кнопку Добавить, потом – на данные листа Магазин3 и кнопку Добавить. В окне команды Консолидация установите опции «Подписи верхней строки», «Значения левого столбца», «Создавать связи с исходными данными».
контрольные вопросы 1. Способы создания структуры данных. 2. Назначение сводной таблицы. 3. Определение консолидации. 4. Отличия функций "Сводная таблица" и "Консолидация".
Лабораторная работа №7 Цель: Научиться использовать функции команды Таблица подстановки.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Последнее изменение этой страницы: 2016-08-26; просмотров: 266; Нарушение авторского права страницы; Мы поможем в написании вашей работы! infopedia.su Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Обратная связь - 18.116.90.141 (0.137 с.) |
По полю Кол-во: больше 3. Раскройте список фильтрации и выберите Числовые фильтры. Выберите из списка «больше». В соседнем поле задайте значение 3. ОК.
используя Расширенный фильтр Данные /Сортировка и фильтр/дополнительно найдите сканеры, которых больше 1. Для этого под таблицей запишем условия отбора Товар – Сканер, Кол – во > 1. Выполним команду Данные /Сортировка и фильтр/дополнительно. Выполним обработку, как показано на рисунке ниже.
