Упражнение 2. Абсолютные и относительные ссылки
Откройте с сетевого диска файл Упражнение 2 (меню Файл / Открыть или кнопка ).
Объедините ячейки А1–F1 (см. предыдущее упражнение) и отформатируйте текст Заголовка, установив:
размер шрифта – 12;
начертание – полужирный курсив.
Установите в ячейках А2– F2:
шрифт – полужирный; размер – 11 пт.;
выравнивание – по центру.
Отформатируйте таблицу по предложенному образцу:
| A
| B
| C
| D
| E
| F
| 1
| Таблица раскрываемости преступлений
| 2
| №
| Наименование
района
| Зарегистрировано преступлений
(кол-во)
| Из них
раскрыто
(кол-во)
| Процент
%
| Удельный вес раскрытых
%
| 3
| 1
| Автозаводский
| 245
| 110
|
|
| 4
| 2
| Канавинский
| 316
| 199
|
|
| 5
| 3
| Советский
| 205
| 130
|
|
| 6
| 4
| Приокский
| 113
| 87
|
|
| 7
| 5
| Нижегородский
| 217
| 146
|
|
| 8
|
| ИТОГО:
|
|
|
|
| Введите в таблицу необходимые формулы:
в ячейку Е3 введите формулу = D3/C3 (для вычисления процента раскрываемости);
скопируйте эту формулу до ячейки Е7;
в ячейках С8 и D8 вычислите итого (используйте Автосуммирование);
в ячейках F3:F7 подсчитайте удельный вес раскрытых преступлений (формула расчета: количество раскрытых преступлений по району разделить на общее количество раскрытых преступлений);
в ячейку F3 введите формулу =D3/D8.
Скопируем эту формулу на весь столбец F и… получим сообщение об ошибке! Посмотрите формулы в ячейках F4 : F7 – в чем ошибка?
| Внимание! Автоматическая настройка формул нужна не всегда. Иногда, как в данном случае, она вызывает ошибки! Для того чтобы при копировании введенной в ячейку F3 формулы не возникало ошибок необходимо, чтобы адрес ячейки D8 не изменялся при копировании.
Исправим ошибку! Вернитесь к формуле, введенной в ячейку F3, подведите курсор в этой формуле к D8 и нажмите клавишу F4 на клавиатуре. Ваша формула примет следующий вид: =D3/$D$8, это означает, что адрес ячейки D8 не относительный, а абсолютный, то есть при копировании ячейки он так и останется ссылкой на ячейку D8. Нажмите клавишу Enter.
Теперь, выделив ячейку F3, скопируйте ее на весь столбец до ячейки F7 (включительно), т. е. протащите маркером заполнения выделенную ячейку по всему столбцу. Ошибки исчезли!
|
Для того чтобы данные в ячейках E3:F7 были представлены в процентном формате, нужно выделить эти ячейки, затем либо выбрать кнопку на панели инструментов, либо выполнить команду Формат / Ячейки / Число и выбрать нужный формат там. Выполните обрамление таблицы по предложенному образцу: для этого выделите нужные ячейки и примените к ним команды меню Формат / Ячейки – Граница (или же сделайте это с помощью кнопки – Границы). В результате ваша таблица должна будет выглядеть так: Таблица раскрываемости преступлений
| №
| Наименование
района
| Зарегистрировано
преступлений,
(кол-во)
| Из них
раскрыто,
(кол-во)
| Процент,
%
| Удельный вес
раскрытых,
%
| 1
| Автозаводский
| 245
| 110
| 45%
| 16%
| 2
| Канавинский
| 316
| 199
| 63%
| 30%
| 3
| Советский
| 205
| 130
| 63%
| 19%
| 4
| Приокский
| 113
| 87
| 77%
| 13%
| 5
| Нижегородский
| 217
| 146
| 67%
| 22%
| ИТОГО:
| 1096
| 672
|
|
| Сохраните результат в своей папке с именем Упражнение 2.
Упражнение 3.
Ссылки, выравнивание в ячейке
Откройте с сетевого диска файл Vedomost.
Подготовьте ведомость для выдачи зарплаты в соответствии с образцом. Для этого: удалите cтолбец C –«Звания» и введите в ячейки формулы для расчета. Внимание! Не забудьте, в столбцах № 6-9 все данные должны рассчитываться по формулам.
Формулы для расчета:
Сумма доплаты за сложность и напряженность = Оклад * (на % доплаты);
Итого = Оклад + (Доплата за звание) + (Сумма доплаты за сложность и напряженность);
Подоходный налог = Итого * (на % Подоходного налога – ячейка F2). Подсказка! Учтите, процент подоходного налога – величина постоянная для всех работников, нужна абсолютная ссылка!;
| Для адресации ячеек в формулах Excel используются:
относительные ссылки – автоматически изменяются при копировании, например: G5;
абсолютные ссылки – при копировании не изменяются, обозначаются знаком доллар ($), например: $G$5.
Клавиша F4 – преобразование ссылок.
|
Сумма к выдаче = Итого – (Подоходный налог);
Всего = Автосуммирование столбца (Сумма к выдаче). Подсказка! Воспользуйтесь кнопкой .
После ввода формул скопируйте их с помощью мыши (см. Упражнение 1).
Отформатируйте таблицу по образцу с помощью команды Формат / Ячейки вкладки Граница и Вид.
Разверните текст в ячейках D3, Е3, F3 – меню Формат / Ячейки вкладка Выравнивание и установите ориентацию 90 градусов.
В результате вы получите следующую таблицу:
Ведомость расчета заработной платы работников ОВД
|
|
| Подоходный налог
| 13%
|
|
|
| N
| Должность
| Оклад
| Доплата за звание
| %
доплаты за сложн. и напряж.
| Сумма доплаты за сложность и напряж.
| Итого
| Подоход. налог
| Сумма к выдаче
| 1
| 2
| 3
| 4
| 5
| 6
| 7
| 8
| 9
| 1
| Начальник
отдела
| 2 990р.
| 1 199р.
| 60%
| 1 794,00р.
| 5 983,00р.
| 777,79р.
| 5 205,21р.
| 2
| Старший
инспектор
| 2 453р.
| 1 098р.
| 40%
| 981,20р.
| 4 532,20р.
| 589,19р.
| 3 943,01р.
| 3
| Инспектор
| 2 209р.
| 999р.
| 40%
| 883,60р.
| 4 091,60р.
| 531,91р.
| 3 559,69р.
| 4
| Секретарь
| 2 000р.
| 899р.
| 35%
| 700,00р.
| 3599,00р.
| 467,87р.
| 3 131,13р.
| 5
| Уборщица
| 550р.
| 0р.
| 20%
| 110,00р.
| 660,00р.
| 85,80р.
| 574,20р.
|
|
|
|
|
|
|
| всего:
| 16 413,25р.
| Сохраните результат в своей папке с именем Упражнение 3.
Упражнение 4.
Мастер функций
Откройте созданный вами ранее файл Упражнение 1.
Преобразуйте таблицу в ежедневный отчет о продажах. Для этого:
Вставьте в начало таблицы 2 строки и добавьте необходимые данные (дату вводить не надо). Внимание! Текст и числа вводятся в разные ячейки.
Для ежедневного автоматического изменения текущей даты – выделите ячейку F1 и воспользуйтесь Мастером функций – меню Вставка / Функция или кнопка fx.
В появившемся меню выберите категорию – Дата и время, тип функции – Сегодня, в следующем окне вводятся аргументы функции, но так как функция Сегодня их не имеет – вам остается нажать на кнопку OK.
| Функции – заранее определенные формулы, выполняющие вычисления в указанном порядке по заданным величинам, называемыми аргументами.
Вставка функции – выделить ячейку для ввода функции, вызвать Мастер функций ( fx ), выбрать категорию, нужную функцию и уточнить в аргументе функции диапазон выбранных ячеек.
Мастер функций позволяет использовать набор функций Excel, которые разбиты по Категориям (Финансовые, Статистические и др.) с кратким описанием каждой функции. Если вы не знаете, к какой категории принадлежит нужная функция, выберите категорию Все.
Можно вводить аргументы функции вручную, а можно выделить нужные диапазоны.
| Добавьте недостающие данные;
Введите формулы расчета:
Сумма (в руб.) = Сумма($) * Курс доллара;
НДС (в руб.) = Сумма (в руб.) * НДС (в %);
Итого сумма с НДС (в руб.) = Сумма (в руб.) + НДС (в руб.);
Скопируйте введенные формулы на нужный диапазон ячеек. (Подсказка! Не забудьте про абсолютную ссылку на значение курса доллара);
Подсчитайте строку ИТОГО с помощью функции Автосуммирования.
Отформатируйте таблицу в соответствии с образцом.
Подсказка! При форматировании заголовка таблицы воспользуйтесь клавишами Принудительного переноса Принудительный перенос в ячейке – Alt+Enter.
Вставьте рисунок (меню Вставка / Рисунок / Картинки – из коллекции Microsoft Office – Технология – Вычислительная техника). К таблице добавьте данные «об общей сумме продаж» – данные должны вставляться автоматически из ячеек таблицы (Подсказка! Введите в ячейку знак = и укажите адрес ячейки, например: =Е10).
Поменяйте курс доллара на 29,2 р., НДС на 15% и посмотрите – изменился ли ваш отчет.
Сохраните таблицу в своей папке с именем Упражнение 4.
Упражнение 5.
Сортировка, статистические функции
Откройте с сетевого диска файл Dinamika, отформатируйте его в соответствии с указаниями:
Установите для шапки таблицы:
размер шрифта – 10 пт.;
начертание полужирное;
выравнивание в ячейках – по центру;
установите перенос по словам;
разворот текста на 90 в тех ячейках, где это нужно;
выполните нужное обрамление таблицы.
| Для расположения данных по алфавиту или по возрастанию (убыванию) выделяется вся таблица и выполняется сортировка – меню Данные \ Сортировка.
Сортировать можно одновременно данные в трех столбцах: сначала сортируются данные в одном столбце, при наличии одинаковых значений производится сортировка по другому столбцу и т. д.
| Отсортируйте «Наименования районных ОВД» в первом столбце по алфавиту. Для этого выделите таблицу (Внимание! выделяются все данные – ячейки А4:Е12, а не только первый столбец) и воспользуйтесь командами меню Данные / Сортировка. Установите – сортировать 1 столбец по возрастанию.
Проверьте результат (см. образец).
Наименование районных ОВД
| Общее число зарегистрированных преступлений
| Прирост + -,
%
| Раскрыто преступлений (кол-во)
| Удельный
вес, %
| 2003 г.
| 2004 г.
| 1
| 2
| 3
| 4
| 5
| 6
| Арзамасское
| 1 811
| 1 999
|
| 1 144
|
| Богородское
| 1 223
| 1 013
|
| 950
|
| Борское
| 1 625
| 1 997
|
| 813
|
| Вадское
| 1 183
| 1 338
|
| 902
|
| Гагинское
| 1 452
| 1 223
|
| 995
|
| Городецкое
| 1 617
| 1 819
|
| 1 022
|
| Лукояновское
| 1 500
| 1 954
|
| 855
|
| Навашинское
| 1 858
| 1 701
|
| 997
|
| Максимальный показатель по области
|
|
|
|
|
| Минимальный показатель по области
|
|
|
|
|
| Итого по Нижегородской области
|
|
|
|
|
| Добавьте к таблице три строки и заполните их данными, воспользовавшись Мастером функций.
Для этого:
выделите ячейку, в которую хотите ввести функцию, и нажмите кнопку f x .
выберите из категории Статистические функцию МАКС.
Подсказка! Нужные вам функции «МАКС», «МИН» относятся к категории Статистические.
Для расчета данных введите в ячейки следующие формулы и скопируйте их:
в ячейках столбца D: Прирост % = (кол-во зарегистрир. прест. 2004 г. вычесть кол-во зарегистрир. прест. 2003 г.) разделить на число зарегистрир. прест. 2003 г. (Внимание! Скобки необходимо ввести обязательно!);
в ячейках столбца F: Удельный вес раскрытых преступлений % = кол-во раскрытых. прест. по району разделить на общее кол-во раскрытых прест. по области.
Подсказка! При составлении формулы учесть – общее кол-во раскрытых прест. по области – величина неизменная для всех ячеек! Используйте в формуле для копирования знак $.
Вставьте строку заголовка таблицы: для этого переместите курсор в первую ячейку и воспользуйтесь командой меню Вставка / Строки. Затем напишите заголовок: «Динамика преступности по Нижегородской области». Размер шрифта для заголовка – 12 пт.
Отформатируйте информацию в ячейках:
в ячейках с числами используйте: Формат / Ячейки… вкладка Число:
формат – числовой;
число десятичных знаков – 0;
установите флажок – разделитель групп разрядов;
в ячейках с процентами установите процентный формат – %;
В результате вы получите следующую таблицу: Динамика показателей раскрываемости по Нижегородской области
| Наименование районных ОВД
| Общее число зарегистрированных преступлений
| Прирост +, %
| Раскрыто преступлений (кол-во)
| Удельный вес %
| 2003 г.
| 2004 г
| 1
| 2
| 3
| 4
| 5
| 6
| Арзамасское
| 1 811
| 1 999
| 10%
| 1 144
| 15%
| Богородское
| 1 223
| 1 013
| -17%
| 950
| 12%
| Борское
| 1 625
| 1 997
| 23%
| 813
| 11%
| Вадское
| 1 183
| 1 338
| 13%
| 902
| 12%
| Гагинское
| 1 452
| 1 223
| -16%
| 995
| 13%
| Городецкое
| 1 617
| 1 819
| 12%
| 1 022
| 13%
| Лукояновское
| 1 500
| 1 954
| 30%
| 855
| 11%
| Навашинское
| 1 858
| 1 701
| -8%
| 997
| 13%
| Максимальный показатель по области
| 1 858
| 1 999
|
| 1 144
|
| Минимальный показатель по области
| 1 183
| 1 013
|
| 813
|
| Итого по Нижегородской области
| 12 269
| 13 044
|
| 7 678
|
|
Сохраните таблицу в своей папке с именем Упражнение 5. Упражнение 6.
Построение диаграмм. Мастер диаграмм
Административный
округ
| Численность
рабочих (тыс. чел.)
| Центральный
| 1194,4
| Северный
| 512,8
| Северо-Западный
| 196,6
| Северо-Восточный
| 353,2
| Южный
| 438,9
| Юго-Западный
| 272,1
| Юго-Восточный
| 373,8
| Западный
| 366,4
| Восточный
| 427,8
| г. Зеленоград
| 77,5
| Откройте с сетевого диска файл Упражнение 6.
Выделите всю таблицу и вызовите Мастера диаграмм – Вставка / Диаграмма или кнопка на Панели инструментов. Далее по шагам настройте необходимый вид диаграммы.
1 шаг: выберите тип – Круговая и нужный вид диаграммы, затем нажмите кнопку Далее.
2 шаг: переключатель рядов данных должен стоять на метке «в столбцах» и Далее.
3 шаг: выберите размещение легенды: внизу, а на вкладке Подписи данных поэкспериментируйте с переключателями, наблюдая изменение вида диаграммы в демонстрационном окне, но остановитесь на переключателе Доли (см. рисунок). В следующем окне убедитесь, что название диаграммы и легенда сформированы верно.
Возможно, для улучшения вида диаграммы в документе вам придется изменить пропорции диаграммы с помощью двойных стрелок.
Что делать, если вам не нравится что-либо в вашей диаграмме: цвет, вид, шрифт и т. д.?
Нужно войти в режим редактирования диаграммы двойными щелчками по интересующей области.
Если это легенда или заголовок, то, управляя вкладками, можно изменить шрифт, его высоту, размещение, начертание, а также произвести заливку.
Если это сама диаграмма, то можно убрать черные линии границ, изменить цвет любого из помеченных секторов, при этом и цвет его метки в легенде изменится автоматически. Наиболее интересующий вас сектор можно сделать «вынутым» простым перетаскиванием мышью (см. рисунок).
Проделайте эту процедуру с секторами двух округов: Северного и Южного, измените цвет секторов на ярко-зеленый и красный.
Попробуйте самостоятельно создать объемную гистограмму. Полученные диаграммы должны выглядеть примерно так:
Сделайте гистограмму по выборочным областям, для этого:
выделите ячейки с данными А3, А7, А11 (для формирования легенды) и В3, В7, В11. (Внимание! Поскольку выделение информации не сплошное, а выборочное – делать это нужно, удерживая нажатой клавишу Ctrl). Постройте обычную гистограмму для выделенных данных, т.е. сравните Центральный, Южный и Восточный округа.
| Создание диаграммы – выделяются данные, которые должны быть отображены в диаграмме, затем запускается Мастер диаграмм – Вставка / Диаграмма или кнопка .
При корректировке данных все изменения сразу же отображаются на диаграмме
После того как диаграмма создана, можно изменить тип диаграммы, диапазон данных или добавить новые точки в ряды данных, на основе которых строилась диаграмма – меню Диаграмма / Добавить данные или Диаграмма / Исходные данные.
Для перемещения диаграммы ее нужно предварительно выделить и перетащить с помощью мыши в новое место. Для изменения размеров ее необходимо выделить и перетащить маркеры изменения размера.
Подписи данных – это значения для точек данных и подписи категорий данных. Подписи можно делать для одной точки, для нескольких точек или для всех точек.
Легенда – это список условных обозначений, используемых в диаграмме. При создании диаграммы с помощью Мастера диаграмм легенда создается по умолчанию.
| Сохраните файл с именем Упражнение 6.
Упражнение 7.
Смешанные ссылки
Задание: Подготовьте шпаргалку для продавца мороженого.
Введите первоначальные данные.
| A
| B
| C
| D
| E
| F
| G
| 1
|
| 1
| 2
| 3
| 4
| 5
| 6
| 2
| Рожок
| 3,2
|
|
|
|
|
| 3
| Эскимо
| 2,9
|
|
|
|
|
| 4
| Семейное
| 10,5
|
|
|
|
|
| 5
| Батончик
| 4,7
|
|
|
|
|
| 6
| В ваф. стак.
| 2,6
|
|
|
|
|
| 7
| В брикете
| 3,5
|
|
|
|
|
| Введите в ячейку С2 формулу для расчета: =В2*С1
Скопируйте эту формулу в ячейки С3:С7. Результат получился неверным. Проанализируем почему. Для этого посмотрите формулу в ячейке С3 (двойной щелчок мыши): =В3*С2, а должно быть =В3*С1. Обратите внимание, что при копировании адрес ячейки С1 изменился на С2, что и привело к ошибке, а если быть совсем точным – цифра 1 изменилась на 2. Сделаем так, чтобы она не менялась – выделим снова формулу в ячейке С2, поставим курсор на ячейку С1 (см. рисунок) и, нажимая несколько раз клавишу F4, изменим формулу на =В2*С$1 и нажмем Enter.
Скопируйте заново эту формулу в ячейки С3:С7. Теперь результат верен.
Далее скопируем эту формулу в ячейки D2:G2. Результат опять получился неверным. Проанализируйте почему (см. выше). Измените формулу. В результате вы должны получить формулу: =$В2*С$1
Эту формулу можно копировать и вниз, и вправо.
Отформатируйте таблицу (результат смотрите ниже):
-
| 1
| 2
| 3
| 4
| 5
| 6
| Рожок
| 3,20р.
| 6,40р.
| 9,60р.
| 12,80р.
| 16,00р.
| 19,20р.
| Эскимо
| 2,90р.
| 5,80р.
| 8,70р.
| 11,60р.
| 14,50р.
| 17,40р.
| Семейное
| 10,50р.
| 21,00р.
| 31,50р.
| 42,00р.
| 52,50р.
| 63,00р.
| Батончик
| 4,70р.
| 9,40р.
| 14,10р.
| 18,80р.
| 23,50р.
| 28,20р.
| В ваф. стак.
| 2,60р.
| 5,20р.
| 7,80р.
| 10,40р.
| 13,00р.
| 15,60р.
| В брикете
| 3,50р.
| 7,00р.
| 10,50р.
| 14,00р.
| 17,50р.
| 21,00р.
| Запишите созданный файл в свою папку с именем Упражнение 9.
| Если при копировании формулы нужно сохранять фиксированным только один параметр, то есть номер строки или номер столбца, используются смешанные ссылки (например, $A4 – при копировании не будет изменятся столбец А; В$3 – не будет изменятся 3).
Клавиша F4 служит для переключения между абсолютными, относительными и смешанными ссылками.
|
|