Вычисления в таблицах с помощью формул и встроенных функций

Практическая работа
Вычисления в таблицах с помощью формул и встроенных функций
Цель работы: овладеть основными навыками вычислений с помощью формул и функций
Дополнительные сведения:
Ввод формулы всегда начинается со знака =. После знака = в активную ячейку вводятся адреса ячеек с исходными данными и знаки математических действий. Формула может содержать несколько операций, в этом случае должны использоваться скобки. Сколько открывающихся скобок столько же должно быть и закрывающихся.
Ввод формулы в ячейку всегда должен заканчиваться нажатием на Enter или кнопку Вход в строке формул.
Копировать, переносить или заполнять диапазон ячеек можно только внесенной в ячейку формулой.
Для копирования формулы в диапазон ячеек нужно подвести курсор мыши к маркеру заполнения, нажать левую кнопку мыши и протянуть курсор до конца диапазона.
Для вставки функции в активную ячейку нужно нажать кнопку Вставка функции на панели инструментов или выполнить команду Вставка – Функция … или нажать комбинацию клавиш Shift + F3. В диалоговом окне Мастер функций нужно выбрать категорию функции из выпадающего списка Категория и ниже в окне саму функцию и нажать ОК. в следующем диалоговом окне в соответствующих полях нужно указать адреса ячеек.
Адреса ячеек можно вводить с помощью клавиатуры или выделением ячеек на рабочем листе щелчком левой кнопки мыши и при необходимости удерживая клавишу Ctrl. После ввода всех необходимых данных нужно нажать кнопку ОК.
Ход работы:
Постановка задачи «Показатели производства»
Предприятие имеет заказ на выпуск некоторого вида продукции, план выпуска которой определен для каждого месяца календарного года.
Необходимо выполнить расчет и анализ показателей производства по месяцам, а также провести итоговые статистические расчеты за год. Для каждого месяца следует отразить в таблице:
план выпуска;
фактически выпущенное количество продукции;
процент выполнения плана;
отношение выпущенной продукции за месяц к продукции выпущенной за год (доля месяца в годовом выпуске)
по приведенным в таблице данным необходимо выполнить анализ итогов работы предприятия за год и других статистических показателей.
Заполнение таблицы исходными данными:
Выделите диапазон ячеек и объедините их.
Введите в ячейку А1 заголовок таблицы Показатели производства.
Выделите диапазон ячеек А4:С4 и объедините их.
Выделите диапазон ячеек А4:G4 и установите выравнивание по центру по горизонтали и по вертикали, режим переноса по словам.
Введите в ячейки A4, D4, E4, F4, G4 названия столбцов таблицы соответственно:
Месяцы, План выпуска, Фактически выпущено, Процент выполнения плана, Выполнено в % к фактически выпущенному за год
Измените ширину столбцов так, чтобы они приняли такой же вид как на рисунке. Для этого подведите курсор мыши к вертикальному разделителю между заголовками столбцов рабочего листа (курсор примет вид крестика с горизонтальными стрелочками), нажмите левую кнопку мыши и удерживая ее передвиньте разделитель в право и лево до нужной ширины.
Заполните столбцы под общим названием Месяцы:
В диапазоне А1:А16 постройте числовой ряд от 1 до 12: в ячейку А5 введите 1 и при нажатой клавиши Ctrl и левой кнопки мыши протащите маркер заполнения до ячейки А16.
В диапазоне ячеек В5:В16 построить ряд из дат – последних чисел каждого месяца: наберите в ячейках В5 и В6 даты 31.01.1999 и 28.02.1999 соответственно. Выделите диапазон ячеек В5:В6, установите курсор на маркер заполнения и протащите его до конца диапазона.
Для диапазона ячеек С5:С16 введите названия месяцев: наберите в ячейке С5 текст Январь , выделите ячейку С5, установите курсор на маркер заполнения и протащите его до конца диапазона.
Заполните диапазон ячеек D5:D16 и Е5:Е16 числами как в таблице.
50165111125
Расчет и анализ работы предприятия
В ячейке В18 наберите текст Итого за год
В ячейке D18 ввести сумму значений в диапазоне D5:D16. Для этого выполните следующее:
Выделите ячейку D18 и щелкните на кнопку Автосумма (∑) на панели Стандартная. В ячейке появится формула =СУММ(D5:D17). Измените диапазон на D5:D16
Аналогично в ячейке Е18 вычислите значение суммы фактически выпущенной продукции за год
Введите в ячейку F5 формулу =E5/D5 (адреса ячеек не набирайте вручную, а выделяйте их левой кнопкой мыши). Заполните диапазон ячеек F5:F16, используя маркер заполнения
Выделите диапазон ячеек F5:F16 и отформатируйте его представив данные в процентном формате. Для этого нужно нажать на Процентный формат на панели инструментов Форматирование, а затем дважды кнопку Увеличить разрядность на той же панели.
В ячейку F18 ввести формулу для вычисления процента выполнения плана за год =E18/D18. Отформатируйте ее представив данные в процентном формате с двумя цифрами в дробной части.
В диапазоне ячеек G5:G16 нужно вычислить для каждого месяца его долю в процентах к выпущенному за год. Формула в ячейке G5 будет иметь вид =Е5/$E$18. Автоматически заполните формулами этот диапазон.
В ячейку В20 введите текст Максимально за месяц
В ячейку В21 введите текст Минимально за месяц
В ячейку В22 введите текст В среднем за месяц
В ячейке Е20 постройте формулу =МАКС(Е5:Е16) с помощью Статистической функции МАКСВ ячейке Е21 постройте формулу =МИН(Е5:Е16) В ячейке Е22 постройте формулу =СРЗНАЧ(Е5:Е16) Выделите диапазон ячеек Е20:Е22 и скопируйте его на диапазон ячеек F20:F22, используя маркер заполнения
Для ячейки Е22 установите числовой формат вывода целых чисел (без дробной части).
Для диапазона F20:F22 установите процентный формат с двумя цифрами после запятой
Оформите таблицу по своему решению
Задание для самостоятельного выполнения
Задание «Ведомость»
-34988551435Создайте ЭТ для внесения данных о зарплате служащих фирмы; внесите в нее приведенные данные. Заголовок таблицы и названия столбцов отформатируйте по образцу.
Заполните ячейки E3:J8, используя необходимые формулы
Заполните строку ИТОГО
Подсчитайте средний оклад служащих фирмы
Сохраните таблицу под именем Ведомость.
Контрольные вопросы:
Как ввести в ячейку формулу?
Перечислите основные типы функций в Excel
Что такое относительная ссылка?
Что такое абсолютная ссылка?
Как можно отредактировать формулу?
Как скопировать формулу в другие ячейки?
Что будет, если при вводе формулы в ячейку опустить знак «равно»?