Файл: Лабораторная работа 1 Ввод и обработка данных, использование функций, построение и редактирование диаграмм.docx

ЛАБОРАТОРНАЯ РАБОТА 1

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

1. 
   Составьте таблицу следующего вида (рис.1). Введите в таблицу заголовок.
   
2. 
   Заполните ячейку В4. Затем протащите мышь вправо до ячейки F4 включительно (указатель мыши необходимо навести на ячейку В4 таким образом, чтобы он принял форму черного крестика). Отпустите левую кнопку мыши. Клетки автоматически заполнятся месяцами.
   
3. 
   Заполните остальные ячейки.
   

Рисунок 1 – Пример таблицы
ЗАДАЧА 1. Рассчитать выручку от проката машин на 4 часа, 1 день, одну неделю.

4. 
   Выделите ячейки B5:G5. Выполните щелчок на кнопке Сумма на панели инструментов Стандартная.
   
5. 
   Скопируйте формулу из ячейки G5 в ячейки G6:G7.
   
6. 
   Выделите ячейки B5:B8. Выполните щелчок на кнопке Сумма .
   
7. 
   Скопируйте формулу из ячейки B8 ячейки С8:G8.
   

Расчет процента

8. 
   Установите курсор на ячейку H5. Щелкните на кнопке процент на панели Форматирование для задания формата ячейки.
   
9. 
   Наберите формулу =G5/G8 и, не нажимая клавишу Enter, нажмите клавишу F4. Формула примет вид = G5/$G$8, то есть отностиельный адрес G8 заменится на абсолютный $G$8 (не меняющийся при копировании формулы в другие ячейки). Нажмите клавишу Enter. Появится величина в процентах.
   
10. 
    Скопируйте формулу из ячейки H5 в ячейки H6:H8.
    

Имена ячеек

11. 
    Присвойте ячейке G8 имя Всего: установите курсор на ячейку G8, выполните команду Вставка – Имя – Присвоить, введите имя ячейки Всего, щелкните на кнопке ОК.
    
12. 
    Очистите ячейки H5:H8 для расчета процентов по другой формуле.
    
13. 
    Введите имя ячейки в формулу расчета процента: установите курсор на ячейку H5 и введите формулу = G5/Всего, скопируйте формулу в H6:H8.
    
14. 
    Быстрый переход к ячейке по ее имени: Откройте список в поле Имя в строке Формул, щелкните на имя ячейки. Для перехода к ячейке по ее имени можно также выполнить команду Правка – Перейти (или нажать клавишу F5) и выбрать имя ячейки.
    

Контроль взаимосвязи ячеек при расчетах

15. 
    Выведите панель инструментов Зависимости. Для этого выполните команду Сервис – Зависимости – Панель зависимостей.
    
16. 
    Установите курсор на ячейку G8. Щелкните на кнопке Влияющие ячейки. Щелкните на кнопке Зависимые ячейки.
    

---

Влияющие ячейки – это ячейки, на которые ссылается формула в текущей ячейке. Зависимые ячейки – это ячейки, содержащие формулы, которые ссылаются на текущую ячейку.

17. 
    Удалите все стрелки, щелкнув на кнопке Убрать все стрелки на панели Зависимости.
    
18. 
    Сохранить таблицу под именем Таблица3.xls. Рабочему листу, на котором находится таблица, присвойте имя Задача 1.
    
19. 
    Следующему листу присвойте имя Задача 2 (если в рабочей книге только один лист, щелкните правой кнопкой мыши по ярлычку этого листа и выполните команду Добавить – Лист).
    

ЗАДАЧА 2. Определить выручку от продажи товаров на лотках и тенденцию роста доходов. Составить таблицу следующей формы (рис.2)


Рисунок 2 – Пример таблицы

1. 
   Установите курсор на ячейку Е6. Введите формулу: = СУММ(D4:D6)
   
2. 
   Установите курсор на ячейку Е9. Введите формулу, используя мастер функций: щелкните на кнопке Вставка функции на панели инструментов Стандартная, в поле Функция щелкните на имени функции СУММ, нажмите на кнопку ОК, в появившемся диалоговом окне в поле Число1 введите диапазон суммируемых чисел D7:D9, щелкните на кнопке ОК.
   
3. 
   Мастер функций можно вызвать также при одновременном нажатии клавиши SHIFT и F3. Для получения пояснения по функции щелкните на кнопке Справка (кнопка со знаком вопроса в левом нижнем углу диалогового окна).
   
4. 
   Самостоятельно подсчитайте сумму в ячейке Е12 любым способом.
   

Функции СРЗНАЧ, МАКС, МИН

5. 
   В ячейку Е14 введите формулу = СРЗНАЧ(Е6;Е9;Е12).
   
6. 
   В ячейку D15 введите формулу = МАКС(D4:D12)/
   
7. 
   В ячейку D16 введите формулу = МИН(D4:D12).
   
8. 
   Очистите ячейки Е14, D15, D16 и введите формулы функций через Мастер функций.
   

Функция РАНГ

Функция ранг определяет ранг (номер) элемента в общей совокупности.

9. 
   В ячейку С4 введите формулу =РАНГ($D4;$D$4:$D$12), где D4 – содержит число, для которого определяется ранг, а D4:D12 – массив чисел, среди которого определяется ранг.
   
10. 
    Скопировать формулу в ячейки С5:С12.
    

Функция ТЕНДЕНЦИЯ

11. 
    Выполните подготовительные операции: в ячейки В20:В22 введите соответственно значения из ячеек Е12, Е9, Е6, в ячейки С20:С25 введите годы: 2005 – 2010.
    
12. 
    В ячейку В23 введите формулу =ТЕНДЕНЦИЯ(В20:В22;С20:С22;С23). Скопируйте формулу из ячейки В23 в ячейку В24.
    
13. 
    Задайте в ячейках В23:В24 формат целых чисел. Для этого следует: выделить нужные ячейки, выполнить команду Формат – Ячейки, выбрать вкладку число, выбрать категорию Числовой.
    
14. 
    Сохранить таблицу. Третьему листу рабочей книги присвойте имя Задача3.
    

---

ЗАДАЧА 3. Муниципалитет города с кодом 10 ввел налог на лиц старше 18 лет в размере 10%. Определить величину налога. Составить таблицу следующей формы (рис.3):


Рисунок 3 – Пример таблицы
Функция ЕСЛИ

1. 
   Введите в таблицу заглавие, шапку, цифровые и текстовые данные.
   
2. 
   В ячейку Е3 введите формулу =ЕСЛИ(И(В3=10;С3>18);D3*0.1;0). Формула означает, что если код города равен 10 и возраст старше 18 лет, то сумма налога определяется умножением дохода на величину налога. В противном случае сумма налога равна 0. Скопируйте формулу из ячейки Е3 в Е4:Е5.
   

ЗАДАЧА 4. Произвести анализ объема продаж и определить, насколько объем продаж, совершенных каждым агентом в отдельности, отличается от среднего объема по целой группе агентов. Составить таблицу следующей формы (рис.4):


Рисунок 4 – Пример таблицы
Функции ОКРУГЛ, ABS, СТЕПЕНЬ, КОРЕНЬ

1. 
   В ячейку В9 введите формулу = ОКРУГЛ(СРЗНАЧ(В5:В7);0).
   
2. 
   В ячейку С5 введите формулу =ABS(B5-$B$9). Скопируйте формулу из ячейки С5 в ячейки С6:С7.
   
3. 
   В ячейку D5 введите формулу =СТЕПЕНЬ(С5;2). Скопируйте формулу из ячейки D5 в ячейки D6:D7.
   
4. 
   В ячейку D10 введите формулу =ОКРУГЛ(СРЗНАЧ(D5:D7);0).
   
5. 
   В ячейку D11 введите формулу =ОКРУГЛ(КОРЕНЬ(D10);0).
   

Функции ДИСПР и СТАНДОТКЛОНП (по генеральной совокупности).

6. 
   Очистите ячейки D10 и D11, чтобы произвести расчет дисперсии и стандартного отклонения, используя соответствующие функции.
   
7. 
   В ячейку D10 введите формулу =ДИСПР(В5:В7).
   
8. 
   В ячейку D11 введите формулу =СТАНДОТКЛОНП(В5:В7).
   

Ввод и обработка данных в формате ДАТА-ВРЕМЯ

1. 
   Загрузите программу MS Excel.
   
2. 
   Новый лист рабочей книги назовите Функции ДАТА-ВРЕМЯ.
   
3. 
   В ячейки с А4 по F4 введите следующие данные (рис.5)
   

Рисунок 5 – Пример таблицы

4. 
   Столбец Порядковый номер заполните числами с 1 по 14, используя автозаполнение.
   
5. 
   Столбец Фамилия заполните фамилиями из пользовательского списка.
   
6. 
   Столбец Дата Рождения заполните следующими числами.
   
7. 
   В ячейках D5:D18 создайте формулу для вычисления возраста студентов, используя функцию СЕГОДНЯ и математическую функцию ЦЕЛОЕ. Чтобы вычислить возраст, достаточно вычесть из текущей даты дату рождения и затем полученный результат разделить на число 365 (число дней в году). Деление на 365 необходимо, чтобы перевести результат в годы. Для того, чтобы округлить возраст до целых чисел, рекомендуется использовать в качестве внешней функции функцию ЦЕЛОЕ из категории Математические. Другими словами, в ячейке D5 нужно создать следующую формулу:
   

---

=ЦЕЛОЕ((СЕГОДНЯ()-С5)/365)

8. 
   Скопируйте формулу из ячейки D5 в ячейки D6:D18.
   
9. 
   Если в ячейках D5:D18 содержится число кратное 5, будем считать возраст юбилейным.
   

Чтобы создать формулу, определяющую юбилейный возраст, нужно воспользоваться логической функцией ЕСЛИ. Правильно созданная формула должна иметь следующий вид:

=ЕСЛИ(ОСТАТ(D5;5)=0;”юбилей”;”-”)

В логическом выражении приведенной формулы проверяется условие кратности 5 числа в ячейке D5. Для этого используется математическая функция ОСТАТ.

10. 
    Скопируйте формулу из ячейки Е5 в ячейки Е6:Е18.
    
11. 
    С помощью условного форматирования ячейки с текстом юбилей оформите шрифтом курсив полужирный, синего цвета.
    
12. 
    Заполните ячейки F5:F18 в соответствии со следующим правилом: премия начисляется только тем студентам, у которых юбилей. Сумма премии равна 50$. Для решения этой задачи используйте функцию ЕСЛИ. Правильно созданная формула в ячейке F5 должна иметь следующий вид:
    

=ЕСЛИ(Е5=”юбилей”;50;”-”)

13. 
    Примените любой из финансовых долларовых форматов для оформления ячеек F5:F18.
    
14. 
    Вычислите, сколько студентов – юбиляров в вашей таблице. Используйте функцию СЧЕТЕСЛИ.
    
15. 
    Вычислите, сколько студентов в вашей таблице моложе 24 лет. Используйте функцию СЧЕТЕСЛИ.
    
16. 
    Вычислите, сколько студентов в вашей таблице старше 25 лет. Используйте функцию СЧЕТЕСЛИ.
    

Построение и редактирование диаграмм

ЗАДАЧА 1. Приведенная ниже таблица (рис.6) использует упрощенную модель определения основных финансовых показателей (себестоимость и прибыль) работы. Таблица заполняется следующим образом. В строку Реализовано значения вводятся в соответствии с таблицей. Строки Доход, Себестоимость и Прибыль заполняются в соответствии с нижеприведенными формулами.

Формулы для определения месячных показателей:

Доход = Цена товара * Реализовано

Себестоимость = Постоянные издержки +Переменные издержки * Реализовано

Прибыль = Доход – Себестоимость

Рабочему листу с таблицей дайте имя Пример. Сохраните созданную таблицу под именем Dohod.xls.
ПОСТРОЕНИЕ ДИАГРАММ

1. Внедренная диаграмма. Построить внедренную диаграмму, отражающую объем реализации по месяцам, тип диаграммы – круговая объемная. Для удобства построения установить масштаб просмотра 75%.

---

Рисунок 6 – Пример таблицы
Решение задачи:

• 
  Выделить диапазон ячеек А7:D8 ( по данным, содержащимся в этих ячейках, должна быть построена диаграмма);
  
• 
  Щелкнуть на кнопке Мастер диаграмм панели инструментов Стандартная;
  
• 
  Переместить курсор мыши в поле таблицы;
  
• 
  Указать область таблицы для размещения диаграммы в диапазоне ячеек А13:Е23. Для этого нужно поместить курсор в ячейку А13, нажать левую клавишу мыши и, не отпуская ее, переместить курсор мыши в ячейку Е23, после чего отпустить левую клавишу мыши;
  
• 
  Далее следовать указаниям Мастера диаграмм.
  

В этом примере перед вызовом Мастера диаграмм выделялся диапазон данных с информацией, на основе которой строилась сама диаграмма. Возможна и обратная последовательность действий.
ЗАДАЧА 2. Построить внедренную диаграмму, отражающую объем реализации по месяцам, тип диаграммы – график. Для удобства построения установить масштаб просмотра – 75%.

Решение задачи:

• 
  Щелкнуть на кнопке Мастер диаграмм панели инструментов Стандартная;
  
• 
  Указать область таблицы для размещения диаграммы в диапазоне F2:J18;
  
• 
  В появившемся окне Мастера диаграмм необходимо задать диапазон ячеек $А$7:$D$8;
  
• 
  Далее следовать указаниям Мастера диаграмм.
  

ЗАДАЧА 3. Построить диаграмму, отражающую объем реализации по месяцам на отдельном листе, тип диаграммы – гистограмма.

Решение задачи:

• 
  Выделить диапазон ячеек А7:D8;
  
• 
  Нажать клавишу F11. В результате диаграмма будет помещена на отдельном листе – листе диаграмм. Имя листа по умолчанию Диаграмма1, каждый последующий лист диаграмм будет содержать слово Диаграмма, но номер будет увеличиваться с шагом 1. Переименуйте этот лист, дав ему имя Гистограмма.
  

ЗАДАЧА 4. Построить диаграмму, отражающую объем реализации по месяцам, тип диаграммы – круговая объемная.

Решение задачи:

• 
  Выделить диапазон ячеек А7:D8;
  
• 
  Выбрать пункт меню Вставка, команда Диаграмма, а в открывшемся подменю выполнить команду На новом листе;
  
• 
  После появления окна Мастер диаграмм выполнить необходимые действия.
  
• 
  Переименуйте этот лист, дав ему имя Круговая.
  

---