Главная Обратная связь

Дисциплины:






Использование поименованных ячеек и диапазонов



A – Для удобства использования формул Excel позволяет присваивать ячейкам и диапазонам содержательные имена вместо адреса ячейки. Определенное имя в формуле облегчает понимание назначения формулы. Например, формулу =СУММА(Продано_в_первом_квартале) легче опознать чем =СУММ(C20:C30). Имя можно присвоить формуле или постоянному значению (константе).

1. Присвойте имена ячейкам, содержащим константы (I способ – в ручную).

• Очистите содержимое блоков ячеек D8:G21;

• Установите курсор в ячейку В3 и сделайте активным Поле имени в строке формул;

• Ведите имя Премия, нажмите Enter;

• Аналогичным образом присвойте имя Удержанияячейке В4 и имя Доплата ячейке В5;

2. Присвойте имена блокам ячеек ……… (II – автоматически)

Выделите блок ячеек С7:G21.

• Выполните последовательность команд Вставка/Имя/Создать…/Ок. (Рис. 5.) В результате блоку ячеек С8:G21 автоматически присвоены имена ячеек блока С7:G7.

Рис. 5.

3. В ячейку D8 введите формулу =Оклад*Премия следующим образом:

• Выполните последовательность команд Вставка /Имя /Вставить… /Оклад_(руб)./Ок;

• Наберите знак * и щелкните по ячейке В3, нажмите Enter.

• В результате в ячейке В3 получиться формула вида:

= Оклад_(руб).*Премия

4. Аналогичным образом выполите перерасчеты колонок Доплата (руб), Всего начислено (руб.), Удержания (руб.) с использованием поименованных диапазонов.

5. Поставьте к ячейке D7 комментарии «Премия пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Сравните результат ваших действий с данными на Рис. 6.

6. Защитите лист «Зарплата ноябрь» от изменении (Сервис/Защита/Защитить лист). Задайте пароль на лист, сделайте подтверждение пароля.

7. Убедитесь, что лист защищен и невозможно удаление данных.

8. Снимите защиту листа (Сервис/Защита/Снять защиту листа).

9. Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.

 

 

Рис. 6.

 

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

11. Представьте результат преподавателю для контроля.

12. Задания для самостоятельной работы

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

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

• до 2000 р. – желтым цветом заливки;

• от 2000 до 10 000 р. – зеленым цветом шрифта;

• свыше 10000 р. – малиновым цветом заливки, белым цветом шрифта.

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



16. На новом листе (назовите его Проценты) создайте таблицу по представленную на рис. 7. В ячейке В2 запишите универсальную формулу расчета процентов используя смешанные ссылки.

 

 

Рис. 7.

 

17. Выполните копирование формулы для блока ячеек B2:G11.

18. Скопируйте таблицу в другое место листа очистите расчетные данные.

19. Выполните расчет данных таблицы с использованием поименованных диапазонов.

20. Очистите блок ячеек B2:G11 от данных рис.7. Выполните расчет данных процентов используя в формуле поименованные диапазоны. (Диапазон B1:G1 – назовите Проценты, а блоку ячеек А2:А11 присвойте имя – Данные).

21. Пригласите преподавателя для контроля.

22. Задания для самостоятельной работы.

23.

25. Постройте диаграмму вида изображенного на рис. 7.
Постройте графики функций: С=(x/a)2–(y/b)2 c коэфф., а=4 и b=5;

Z=(x/a)2+(y/b)2 c коэфф., а=1 и b=1;

Область изменения Х и У для обеих функций от [-5; +5], c шагом 0,5;

1. Создайте новый лист (назовите его Трехмерные диаграммы). В ячейку А1 и В1 введите соответственно параметры а и b 4 и 5;

2. Используя маркер копирования заполните блок A3:A23 значениями Х изменяющимися от -5 до 5, с шагом 0,5 (или Правка /Заполнить/Прогрессия).

3. Аналогично заполните блок B2:V2 области изменения переменной У значениями изменяющимися от -5 до 5, с шагом 0,5;

4. Присвойте в Поле имени имена ячейкам А1 и В1 соответственно a и b;

5. Присвойте блокам ячеек A3:A23 и B2:V2 имя Х и имя Y соответственно.

6. Теперь введите формулы для вычисления значения функции. Для этого в ячейку В3 введите формулу (x/a)^2-(y/b)^2;

7. Скопируйте эту формулу на все значения Х;

8. Не снимая выделение, используя маркер копирования распространите выделенные в ячейках формулы на все значения переменной Y;

9. Постройте диаграмму типа поверхность по области B3:V23;

10. Скопируйте лист Трехмерные диаграммы;

11. Измените значения переменных a и b, на коэффициенты 1 и 1

12. Выделите область B3:V23. Используя команду Правка/Заменить замените в этой области знак «» на знак «+» ;

13. Постройте диаграмму типа поверхность по области B3:V23;

14. Расположите обе диаграммы рядом, для чего скопируйте вторую диаграмму на лист рядом с первой.

 

15. Откройте файл Пр_р_№1. Сделайте активным лист2 с вычислениями математических выражений. Исправьте формулы с вычислениями функций F(x), G(x) с использованием абсолютных ссылок или поименованных диапазонов по своему усмотрению.

16. Пригласите преподавателя для контроля.

Практическая работа №7,8.

Связанные таблицы. Расчет промежуточных итогов в таблицах

Ms Excel

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

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

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

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

3. Присвойте скопированному листу название «Зарплата декабрь». Исправьте название месяца в ведомости на декабрь. Измените значение Премии на 46 %, Доплаты – на 8 %. Убедитесь, что программа произвела пересчет формул (рис. 1).

 


Ведомость зарплаты за декабрь

 

Рис. 8

 

4. Перед расчетом итоговых данных за квартал проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь–декабрь:

a. Выделите блок ячеек A7:H21 и выполните команду Данные/Сортировка, Сортировать по… в окне выпадающего списка выберите поле Фамилия И.О.

b. аналогичным образом отсортируйте все ведомости.

5. Скопируйте содержимое листа «Зарплата декабрь» на новый лист.

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

7. Отредактируйте лист «Итоги за квартал» согласно образцу на рис. 2. Для этого удалите в основной таблице (см. рис. 1) колонки Оклада и Премии, и строку 22 «Всего». Удалите также строки с расчетом максимального, минимального и среднего доходов под основной таблицей и строки над таблицей со значениями констант. Отформатируйте заголовок таблицы.

8. Вставьте новый столбец «Подразделение» между столбцами «Фамилия» и «Всего начислено». Заполните столбец «Подразделение» данными по образцу (см. рис. 2).

 

Рис. 9

 

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

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

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

='Зарплата октябрь'!E7+'Зарплата ноябрь'!F8+'Зарплата декабрь'!F8

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

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

11. В силу однородности расчетных таблиц зарплаты по месяцам для расчета квартальных значений столбцов «Удержание» и «К выдаче» достаточно скопировать формулу в последующие ячейки Рис.3.

Расчет квартального начисления заработной платы, связывание

листов электронной книги.

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

12. Проверьте правильность заполнения таблицы. Откройте для просмотра еще одно окно. Выполните команды меню Окно\ Новое (в заголовке окон одно и тоже имя файла, и № окон 1и 2).

13. В новом окне выберите рабочий лист «Зарплата октябрь».

14. Выполните команды меню Окно\ Расположить \Рядом (активным всегда является только одно из окон. Для активизации другого окна нужно щелкнуть по нему мышью).

15. Проверьте, как работает связь таблиц. На листе «Зарплата октябрь» исправьте % премии на 30%. Обратите внимание! Изменились ли расчеты колонки Всего начислено (руб.) на листе «Итоги за квартал». Т.о., связь между различными листами одной рабочей книги действует.

16. Введите прежнее значение размера премии (27%). Данные снова изменились.

Рис. 4.

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

a. установите курсор в поле списка и введите команду Данные/Сортировка.Область списка выделиться. Если этого не произошло, то предварительно выделите весь список, а затем введите указанную команду;

b. в диалоговом окне «Сортировка диапазона» установите параметры показанные на рис. 4.

Таблица примет вид, как на рис. 5.

 

 

Рис. 5

 

 

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

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

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

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

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

Рис. 6.

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

 

 

 

Рис. 10


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

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

21. Постройте диаграмму вида представленного на рис. 8, используя данные ведомости с промежуточными итогами.

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

 

Рис. 11





sdamzavas.net - 2019 год. Все права принадлежат их авторам! В случае нарушение авторского права, обращайтесь по форме обратной связи...