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

Практическая работа

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

Цель работы: овладеть основными навыками вычислений с помощью формул и функций

Дополнительные сведения:

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

2. Ввод формулы в ячейку всегда должен заканчиваться нажатием на Enter или кнопку Вход в строке формул.

3. Копировать, переносить или заполнять диапазон ячеек можно только внесенной в ячейку формулой.

4. Для копирования формулы в диапазон ячеек нужно подвести курсор мыши к маркеру заполнения, нажать левую кнопку мыши и протянуть курсор до конца диапазона.

5. Для вставки функции в активную ячейку нужно нажать кнопку Вставка функции на панели инструментов или выполнить команду Вставка - Функция … или нажать комбинацию клавиш Shift + F3. В диалоговом окне Мастер функций нужно выбрать категорию функции из выпадающего списка Категория и ниже в окне саму функцию и нажать ОК. в следующем диалоговом окне в соответствующих полях нужно указать адреса ячеек.

6. Адреса ячеек можно вводить с помощью клавиатуры или выделением ячеек на рабочем листе щелчком левой кнопки мыши и при необходимости удерживая клавишу Ctrl. После ввода всех необходимых данных нужно нажать кнопку ОК.

Ход работы:

1. Постановка задачи «Показатели производства»

Предприятие имеет заказ на выпуск некоторого вида продукции, план выпуска которой определен для каждого месяца календарного года.

Необходимо выполнить расчет и анализ показателей производства по месяцам, а также провести итоговые статистические расчеты за год. Для каждого месяца следует отразить в таблице:

• план выпуска;

• фактически выпущенное количество продукции;

• процент выполнения плана;

• отношение выпущенной продукции за месяц к продукции выпущенной за год (доля месяца в годовом выпуске)

по приведенным в таблице данным необходимо выполнить анализ итогов работы предприятия за год и других статистических показателей.

2. Заполнение таблицы исходными данными:

• Выделите диапазон ячеек и объедините их.

• Введите в ячейку А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 числами как в таблице.

3. Расчет и анализ работы предприятия

• В ячейке В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 установите процентный формат с двумя цифрами после запятой

4. Оформите таблицу по своему решению

Задание для самостоятельного выполнения

Задание «Ведомость»

1. Создайте ЭТ для внесения данных о зарплате служащих фирмы; внесите в нее приведенные данные. Заголовок таблицы и названия столбцов отформатируйте по образцу.

2. Заполните ячейки E3:J8, используя необходимые формулы

3. Заполните строку ИТОГО

4. Подсчитайте средний оклад служащих фирмы

5. Сохраните таблицу под именем Ведомость.

Контрольные вопросы:

1. Как ввести в ячейку формулу?

2. Перечислите основные типы функций в Excel

3. Что такое относительная ссылка?

4. Что такое абсолютная ссылка?

5. Как можно отредактировать формулу?

6. Как скопировать формулу в другие ячейки?

7. Что будет, если при вводе формулы в ячейку опустить знак «равно»?