ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 01.12.2023
Просмотров: 198
Скачиваний: 2
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
88
В ячейке В21 рассчитайте сумму значений данных по столбцу
«Подразделение 1» с использованием кнопки Автосуммирование
, расположенной на вкладке меню Главная. Для этого выделите ячейку В21 и два раза щелкните левой кнопкой мыши по кнопке
. Произойдет сложение данных столбца «Подразделение 1».
С помощью маркера автозаполнения скопируйте формулу из ячейки
B21 в ячейки C21 и D21.
Задайте внешние и внутренние границы таблицы и произведите форматирование названий столбцов и заголовка.
Задание
2.3.
Создать таблицу с данными, выполнить форматирование, произвести расчеты(см. рис. 2.6).
Рис. 2.6
После ввода в ячейку А5 числа 1 выполнить автозаполнение вниз, удерживая клавишу Ctrl, чтобы получить ряд 1, 2, 3, 4.
Формулы для расчета:
Всего по цеху = Заказ № 1 + Заказ № 2 + Заказ № 3 (сумма по
строке);
Итого = сумма значений по каждому заказу (столбцу).
Задание 2.4. Заполнить таблицу, выполнить форматирование, произвести расчеты(рис. 2.7).
89
Рис. 2.7
Формула для расчета:
Премиальная сумма = Сумма по сделкам х Премиальный процент.
В столбце «Премиальный процент» установите процентный формат, в столбцах «Сумма по сделкам» и «Премиальная сумма» установите денежный формат.
Задание 2.5. Заполнить таблицу, произвести форматирование и расчеты (см. рис. 2.8).
90
Рис. 2.8
Для оформления заголовков столбцов таблицы использовать команду правой кнопки мыши Формат ячеек. На вкладке Выравнивание установить флажок Переносить по словам, использовать выравнивание по горизонтали и по вертикали по центру. При необходимости объединить несколько ячеек – выделить их и установить флажок
Объединение ячеек.
Рассчитать значения незаполненных ячеек таблицы, используя следующие сведения:
премия начисляется в размере 50 % от оклада;
подоходный налог составляет 12 % от общей начисленной суммы;
пенсионный налог составляет 1 % от общей начисленной суммы;
профсоюзный налог составляет 1 % от оклада;
сумма к выдаче – это разность итоговой начисленной и итоговой
удержанной сумм.
Придать всем ячейкам, содержащим денежные значения, денежный формат с рублевым обозначением валюты и двумя знаками после запятой.
Установить тип и цвет границ таблицы.
91
2. Абсолютные ссылки
Задание 2.6. В прайс-листе приведены цены в рублях (см. рис. 2.9).
Нужно перевести их в условные единицы по заданному курсу у. е.
Рис. 2.9
Введите исходные данные, отформатируйте таблицу.
Для расчета столбца «Цена в у. е.» нужно руководствоваться следующей формулой:
Цена в у. е. = Цена в руб. / Курс у. е.
Поэтому в ячейку D7 вводим: = C7 / D2.
Однако, если затем выполнить автокопирование этого выражения вниз по столбцу, в обоих адресах ячеек будет меняться номер строки, т. е. ниже мы получим формулы:
= C8 / D3;
= C9 / D4;
= C10 / D5 и т. д.
92
Но нам нужно цену в рублях всегда делить на ячейку D2, т. е. чтобы при автокопировании в адресе ячейки D2 номер строки не менялся. Для того чтобы его зафиксировать, введите перед цифрой 2 символ $. Формула примет вид: = C7 / D$2. Теперь после ее копирования вниз по столбцу получим формулы:
= C8 / D$2;
= C9 / D$2;
= C10 / D$2 и т. д.
Итак, если требуется зафиксировать в адресе ячейкиномер строки, чтобы он не менялся при копировании формулы вдоль столбца, перед этим номером ставят символ $. Например: D$2.
Аналогично, если требуется в адресе ячейки зафиксировать имя столбца, чтобы оно не менялось при копировании формулы вдоль строки, перед его буквой ставят символ $. Например: $D2.
Можно зафиксировать в адресе ячейки и номер столбца, и номер строки, например: $D$2.
Измените курс у. е. в ячейке D2 на 80. Посмотрите, как изменятся значения в таблице.
Задание 2.7. Создать таблицу (см. рис. 2.10).
Произвести расчеты в пустых ячейках. Отформатировать в соответствии с рисунком (выравнивание, заливка, границы).
93
Рис. 2.10
Задание 2.8. Создать таблицу расчета стоимости букета, исходя из цены цветов, их количества и стоимости упаковки (см. рис. 2.11).
Рис. 2.11
94
С помощью автофигур нарисовать вазу с цветком (меню Вставка,
группа Иллюстрации, кнопка Фигуры): два треугольника, отрезок, звездочка. Один из треугольников отразить или повернуть. Сгруппировать все элементы в единый рисунок.
Заголовок «Фирма Цветы» ввести как объект WordArt (меню
Вставка, группа Текст), выбрать форму, с помощью мыши повернуть свободным вращением (за зеленый маркер).
В ячейку А17 ввести первую строчку текста, в ячейку А18 – вторую строчку.
Ввести остальные данные в соответствии с рис. 2.11.
Для того чтобы расположить текст «Красивая упаковка» вертикально, нужно предварительно выделить и объединить ячейки
D22:D29. Затем для полученной объединенной ячейки открыть окно
Формат ячеек, вкладку Выравнивание и задать вертикальное направление текста (см. рис. 2.12).
Рис. 2.12
95
Рассчитать столбец «Стоимость букета».
Формула для расчета:
Стоимость букета = Цена 1 цветка х Количество цветков +
+ Оформление букета.
Для реализации расчета в ячейку C23 нужно ввести формулу
= B23 * B$20 + D$20
и произвести автокопирование вниз по столбцу до ячейки C29.
Расположить все объекты на одной странице. Для этого в меню
Разметка
страницы открыть группу
Параметры
страницы.
В появившемся окне открыть вкладку Страница. В поле Масштаб установить переключатель в положение разместить не более чем на 1
стр. в ширину и 1 стр. в высоту.
Нажать ОК.
Задание 2.9. Создать таблицу расчета стоимости подписки на печатные издания(см. рис. 2.13) с использованием абсолютной адресации.
Рис. 2.13
96
Создать заголовок как объект WordArt ,ввести текст двумя абзацами, выбрать форму треугольника.
Создать и отформатировать таблицу (выравнивание, заливка, границы и др.) в соответствии с рис. 2.13.
В пустых ячейках таблицы нужно выполнить расчеты с помощью формул: стоимость издания за один месяц умножить на соответствующее количество месяцев. Для этого сначала в ячейку C13 нужно ввести формулу: = $B13 * C$12. Символ $ в адресе $B13обеспечит неизменность имени столбца В при копировании формулы вдоль строки. Символ $ в адресе C$12 обеспечит неизменность номера строки 12при копировании формулы вдоль столбца. Затем с помощью маркера автозаполнения скопировать формулу из ячейки C13вниз, вдоль столбца, до ячейки C21 и отпустить кнопку мыши. Столбец остался выделенным. Теперь его нужно скопировать вправо до столбца G и отпустить кнопку мыши (см. рис. 2.14).
Рис. 2.14
97
Задание 2.10. С использованием абсолютной адресации создать таблицу умножения для чисел от 0 до 9 (см. рис. 2.15).
Рис. 2.15
Для заголовка объединить ячейки A1:K1, щелкнуть правой кнопкой мыши по объединенной ячейке, из появившегося контекстного меню выбрать Формат ячеек, открыть вкладку Выравнивание, установить флажок Переносить по словам, повернуть текст на 45 градусов
(см. рис. 2.16).
Рис. 2.16
98
Ячейки B3:K12 заполнить формулами: соответствующее число из столбца А умножить на соответствующее число из строки 2. Использовать абсолютную адресацию. Формулу ввести в ячейку B3, остальные ячейки заполнить с помощью автокопирования: например, сначала вниз вдоль столбца B, а затем вправо до столбца K.
Установить границы и заливку.
Задание 2.11. С использованием абсолютной адресации создать таблицу расчета стоимости бензина, необходимого для проезда на автомобиле из Москвы в другие города.
Основными данными для расчета служат: расстояние между городами, расход автомобилем бензина на 100 км пути, цена 1 л бензина
(см. рис. 2.17).
Рис. 2.17
99
Задание 2.12. Создать таблицу расчета дохода сотрудников организации, используя абсолютную адресацию (см. рис. 2.18).
Рис. 2.18
Введите исходные данные, задайте форматы данных (денежный или процентный), отформатируйте таблицу по образцу.
Выполните расчеты по формулам:
Отчисления на благотворительность = Оклад по ставке х х % отчисления на благотворительность;
Подоходный налог = (Оклад по ставке − Необлагаемый налогом
доход − Отчисления на благотворительность) х % подоходного налога;
Всего удержано = Отчисления на благотворительность +
+ Подоходный налог;
К выдаче = Оклад по ставке − Всего удержано.
Произведите вычисления в итоговой строке.
100
1 2 3 4 5
3. Использование функций
Задание 2.13. Создать таблицу с данными о розничных ценах, произвести расчет максимальных, средних и минимальных значений
(см. рис. 2.19).
Рис. 2.19
Создайте таблицу по образцу.
Рассчитайте изменения цены в столбце E по формуле:
Изменение цены = (Цена на 01.06.2018 −
Цена на
01.04.2018)/ Цена на 01.04.2018.
Установите процентный формат для полученных в столбце E данных.
В строке 14 рассчитайте максимальные значения по столбцам
101 таблицы. Для расчета первого максимального значения выделите ячейку
В14, запустите мастер функций (кнопкой Вставка функции)и выберите функцию МАКС из категории Статистические (рис. 2.20).
Рис. 2.20
Нажмите кнопку ОК.Откроется окно Аргументы функции, в котором нужно указать данные для вычисления выбранной функции.
В качестве первого числа укажите диапазон ячеек B6:B13 и нажмите кнопку ОК (см. рис. 2.21). В ячейке В14 появится максимальное значение данных столбца B.
102
Рис. 2.21
Аналогично рассчитайте максимальные значения в других столбцах.
В строке 15, используя мастер функций, рассчитайте средние значения по столбцам. Для этого нужно применить функцию СРЗНАЧ, которая находится в категории Статистические. Будьте внимательны при указании диапазона ячеек для вычисления этой функции (B6:B13, другие ячейки попасть в диапазон расчета не должны).
В строке 16 рассчитайте минимальные значения по столбцам с помощью функции МИН.
В ячейке А2 произведите вставку функции СЕГОДНЯ,
отображающей текущую дату, установленную на компьютере (категория
Дата и Время).
Задание 2.14. С помощью функции ЕСЛИ создать таблицу расчета надбавки сотрудникам транспортной компании за экономию топлива.
Надбавка начисляется, только если план расходования топлива превышает его фактический расход.
Создайте таблицу по образцу (см. рис. 2.22).
103
Рис. 2.22
Произведите расчет премии по формуле:
Надбавка = Базовая ставка х 0,15 при условии, что
План расходования топлива > Фактически израсходовано.
Для проверки условия и выполнения расчета нужно использовать функцию ЕСЛИ.
Выделите ячейку F4, запустите мастер функций кнопкой Вставка
функции
и выберите функцию ЕСЛИ из категории Логические.
В окне Аргументы функции задайте следующие параметры расчета
(см. рис. 2.23).