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

Microsoft Excel 2010. Вычисление по формулам
В ходе выполнения данного практического задания студент должен научиться:

1. 
   Создавать и использовать простые формулы в Excel.
   

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

Относительные ссылки – это ссылки, которые при копировании формулы изменяются автоматически в соответствии с относительным расположением исходной ячейки и создаваемой копии (например: H4).

Абсолютные ссылки – это ссылки, которые при копировании не изменяются (например: $H$4).

Смешанные ссылки – это ссылки, которые сочетают в себе и относительную и абсолютную адресацию (например: $H4,H$4).

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

1. 
   На рабочем листе в отдельных ячейках подготавливаются значения основных аргументов функции.
   
2. 
   Осуществляется вызов Мастера функций щелчком на кнопке f_x возле строки для написания и редактирования формул.
   

3. 
   Выполняется выбор категории функции. В списке Функция содержится полный перечень доступных функций выбранной категории. В нижней части окна появляются краткий синтаксис и справка о назначении выбранной функции. Гиперссылка Справка по этой функции вызывает экран справки для встроенной функций, на которой установлен курсор. Кнопка Отмена прекращает работу Мастера функций. При щелчке на кнопке ОК осуществляется переход к работе с диалоговым окном выбранной функции. Кнопка ОК переносит в строку формулы синтаксическую конструкцию выбранной встроенной функции.
   
4. 
   В списке требуемой функции выполняется выбор, в результате которого появляется диалоговое окно для ввода аргументов. Для каждой функции существует регламентированный по составу и формату значений перечень аргументов.
   
5. 
   В поле ввода диалогового окна можно вводить как ссылки на адреса ячеек, содержащих собственно значения аргументов, так и сами значения аргументов.
   
6. 
   Отказ от работы со встроенной функцией осуществляется щелчком на кнопке Отмена.
   
7. 
   Завершение ввода аргументов и запуск расчета значения троенной функции выполняется щелчком на кнопке ОК.
   
8. 
   Формула начинается со знака «=» (знака равенства). Далее следует имя функции, а в круглых скобках указываются аргументы в последовательности, соответствующей синтаксису функции. В качестве разделителей аргументов используется выбранный при настройке Windows разделитель; обычно это точка с запятой («;») или запятая («,»). Например, в ячейку С13 введена формула: =ДОХОД(В16;В17;0.08;47.727;100;2;0).
   

Отдельные аргументы функции могут быть как константами, так и ссылками на адреса ячеек.
Задание:

Торговая фирма имеет в своем ассортименте следующие товары: телевизоры стоимостью $ 300, видеомагнитофоны стоимостью $ 320, музыкальные центры стоимостью $ 550, видеокамеры стоимостью $ 700, видеоплееры стоимостью $ 198 и аудиоплееры стоимостью $ 40. В январе было продано: телевизоров – 10, видеомагнитофонов – 5, музыкальных центров – 6, видеокамер – 2, видеоплееров – 7, аудиоплееров – 4. В феврале продано: телевизоров – 7, видеомагнитофонов – 8, музыкальных центров – 3, видеокамер – 2, видеоплееров – 4, аудиоплееров – 6. В марте продано: телевизоров – 6, видеомагнитофонов – 6, музыкальных центров – 5, видеокамер – 4, видеоплееров – 9, аудиоплееров – 7. Используя возможности Excel, требуется найти сумму выручки от продажи в рублях и долларах. Так же вычислить Уровень продаж и Среднюю, максимальную и минимальную выручки за три месяца.

Результат задания и оформление представлены на рис. «Выручка за три месяца».




рис. «Выручка за три месяца»


Методика выполнения задания:

1. 
   Создайте таблицу, внесите в нее исходные данные задачи.
   

Примечание: При оформлении таблицы не забудьте нужные ячейки объединить. Текст шапки таблицы выровнять по центру (по вертикали и горизонтали), перенести по словам.

2. 
   Для подсчета выручки от продаж в долларах в ячейку D3 внесите формулу: =C3*B3. Чтобы не вводить данную формулу заново в следующую ячейку необходимо:
   
   1. 
      Выделить ячейку D3.
      
   2. 
      Навести курсор на правый нижний угол ячейки до появления черного крестика.
      
   3. 
      Зажать правую кнопку мыши и растянуть ячейку до ячейки D8.
      
3. 
   Для подсчета выручки от продаж в рублях в ячейку E3 внесите формулу: =D3*$B$12.
   
4. 
   Подсчитайте Итоговые суммы по каждому столбцу (выделить столбец и щелкнуть на кнопке Автосумма на панели инструментов - ∑, или воспользуйтесь функцией СУММ – в окне Мастера функций). Например: =СУММ(C3:C8).
   
5. 
   Уровень продаж по следующей формуле: =ЕСЛИ(D9>=10000; "Высокая продажа"; "Низкая продажа").
   
6. 
   Для расчета Средней выручки за три месяца необходимо выполнить следующие действия:
   
   1. 
      Выделить ячейку B14.
      
   2. 
      Поставить знак «=».
      
   3. 
      Выбрать в Мастере функций функцию СРЗНАЧ.
      

4. 
   В появившемся диалогом окне функции для Число 1 введите следующие данные:
   

1. 
   ОК.
   
1. 
   Аналогично подсчитайте Максимальное и минимальное значения в долларах, а так же в рублях.
   
2. 
   Подготовьте ваш документ к печати.
   
3. 
   Работу сохраните в своей папке под именем «Практическая работа 2 - Excel».
   

Самостоятельная работа:

1. 
   Откройте документ «Практическая работа 2 - Excel». Переименуйте Лист 1 – «Выручка за три месяца», Лист 2 – «Сводная ведомость».
   
2. 
   На листе «Сводная ведомость» составьте таблицу, представленную на рисунке.
   
3. 
   Заполните таблицу и произведите соответствующие расчеты, используя полученные знания при выполнении Практической работы «Выручка за три месяца».
   
4. 
   Подготовьте ваш документ к печати.
   
5. 
   Работу сохраните в своей папке (Сохраните нажатием на дискетку в левом верхнем углу редактора).
   

Примечание:

- Для подсчета Уровня знаний необходимо использовать следующую формулу:

=ЕСЛИ(Q7>=5;"Отлично"; ЕСЛИ(Q7<=3,7;"Удовлетворительно"; "Хорошо")).

- Для подсчета Качественной успеваемости:

=(E21+E22)/14

- Для подсчета количества 5, 4, 3, и 2:

=СЧЁТЕСЛИ(E7:E20;5); =СЧЁТЕСЛИ(E7:E20;4); =СЧЁТЕСЛИ(E7:E20;3); =СЧЁТЕСЛИ(E7:E20;


Рис. «Сводная ведомость»