Приклад виконання завдання.

Тема: "Табличний процесор Excel"

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

Постановка задачі.

1. Створити таблицю з розрахунками по рецептурі морозива «Морозиво «Фруктове»», яка має таку структуру:

• порядковий номер;

• номери сировини;

• найменування сировини;

• витрати сировини на 1 т готової продукції ;

та назвати електронний листок, на якому розташована таблиця, «Форма». Дані занести з таблиць 2 та 3.

• Додати у таблицю колонку «Розрахунок сировини на 1,5 т готової продукції, кг» та виконати необхідні обчислення.

• Розрахувати «сумарні витрати» по 3 і 4 колонкам таблиці.

• Додати у таблицю колонку «питома вага» та виконати обчислення з точністю до сотих.

• Вставити заголовок «Морозиво «ФРУКТОВЕ»».

• Відформатувати створену таблицю.

2. Створити листок «Сировина» з таблицею виду.

Порядковий номер

Сировина

Дані взяти з Таблиці 3.

3. На третьому листку «Таблиця» створити нову таблицю

та виконати над її даними наступні дії:

• у першу колонку занести номера сировини відповідної рецептури;

• у другій колонці за допомогою функції «ВПР» отримати найменування сировини по її номеру (в першій колонці);

• у третю та четверту колонки скопіювати дані з колонок 5 та 6 першої таблиці крім підсумкового рядка (копіювати тільки значення без формул);

• додати колонку "Група", куди занести "1", якщо питома вага не більше 10, або "2" в іншому випадку;

4. Побудувати діаграму (колонки 2 і 3 лист «Таблиця») на окремому листку.

5. Здійснити фільтрацію даних по групам сировини. Результат роботи зберегти на листку «Фільтр».

6. Упорядкувати дані по групам. Результат роботи зберегти на листку «Упорядкування».

7. Підвести підсумки по групам сировини. Результат роботи зберегти на листку «Підсумки».

8. Роздрукувати та зберегти електронну таблицю на магнітному диску.

9. Роздрукувати таблиці в режимі відображення формул.

Виконання завдання

1. Після завантаження програми Microsoft Excel на листку з ім’ям «Лист1» створюємо таблицю, заданої структури

   1. Створення таблиці:

      1. у клітинку А1 вводимо текст «№»;

      2. у клітинку В1 вводимо текст «Номери сировини»;

      3. у клітинку С1 вводимо текст «Найменування сировини»;

      4. збільшуємо ширину колонки С, для цього покажчик миші встановлюємо на правій межі імені стовпця С, при цьому активізується новий покажчик — двохстороння стрілка, «перетягуючи» який плавно змінюємо ширину стовпця;

      5. у клітинку D1 вводимо текст «Витрати сировини на 1 т готової продукції, кг»;

      6. задамо режим переносу по словам в клітинці D1, для цього виділимо і використаємо пункт меню Формат ─ Ячейки… ─ вкладка «Выравнивание» ─ ;

      7. клітинки А2:А7 заповнюємо номерами, для цього використаємо інструмент Автозаповнення: у клітинки А2, А3 вводимо перше та друге значення (відповідно числа1 та 2). Виділяємо клітинки А2, А3 і покажчик миші підводимо до хрестика, розташованого у правому нижньому кутку клітинки (при цьому форма покажчика миші має змінитися на «+»), натискуємо на ліву клавішу миші й «розтягуємо» блок у напрямку клітинок А4:А7 (заповнюючи їх).

      8. у клітинки В2:В7 вводимо «Номери сировини» (дані в таблиці 2)

      9. у клітинки С2:С7 вводимо «Найменування сировини» (дані в таблиці 3);

      10. у клітинки D2:D7 вводимо «Витрати сировини на 1 т готової продукції» (Таблиця 2);

      11. клацнемо правою клавішею миші по ярлику листка «Лист1» і виберемо команду Переименовать із контекстного меню. Текстовий курсор переміститься в поле ярлика, де введемо нове ім'я листка «Форма».

   2. У клітинку E1 вводимо текст «Розрахунок сировини на 1,5 т готової продукції, кг», а у клітинку E2 розрахункову формулу «=D2*1,5» та натискаємо клавішу Enter. Копіюємо цю формулу в клітинки E3:E7, для цього підводимо курсор до хрестика, розташованого у правому нижньому кутку клітинки E2 (при цьому форма покажчика миші має змінитися на «+»), натискуємо на ліву клавішу миші й «розтягуємо» блок у напрямку клітинок E3:E7;

   3. Виділимо діапазон клітинок E2:E7, натиснемо клавішу (Автосумма) і в клітинці E8 отримаємо суму значень діапазону клітинок E2:E7 (=СУММ(E2:E7)). Аналогічно обчислимо суму по 5 колонці;

   4. У клітинку F1 вводимо текст «Питома вага», а у клітинку F2 розрахункову формулу «=E2*100/$E$8». Адреса $E$8 є абсолютною, тобто вона не змінюється при копіюванні. Знак $ — ознака абсолютної адреси (створюється введенням символів з клавіатури або виділенням адреси і натисненням на клавішу F4). Копіюємо формулу в клітинки F3:F7 так само, як в п.3. Виділяємо клітинки F2:F7 і задаємо точність подання чисел із фіксованою комою кнопками-піктограмами – ,. Перша збільшуєкількість знаків після десяткової коми, а друга зменшує її.

   5. Для створення заголовка «Морозиво «Фруктове»» спочатку вставимо рядок над таблицею. Виділяємо рядок таблиці, перед яким потрібно розмістити новий (1-й рядок) і використовуємо команду Вставка—Строки. Виділяємо клітинки А1:F1, а потім об'єднуємо і центруємо за допомогою кнопки («Объединить и поместить в центре»). У об'єднану клітину вводимо текст «Морозиво «Фруктове»»

   6. Для форматування даних та оформлення таблиці потрібно виділяти діапазони клітин (з однаковим форматом) і використовувати команду меню Формат ─ Ячейки… або панель інструментів Форматирование. В результаті на листку «Форма» ми отримали таблицю (див. рис. 14)

Рис. 14.

2. Створимо листок «Сировина» з таблицею, як в п. 1. Дані беремо з Таблиці 3.

3. Перейменуємо третій листок за допомогою команди Переименовать із контекстного меню (назвемо «Таблиця»). Створимо нову таблицю та виконаємо над її даними наступні дії:

   1. у клітинку А1 вводимо текст «Номери сировини» та форматуємо (пункт меню Формат ─ Ячейки… ─ вкладка «Выравнивание»), а в клітинки А2:А7 заносимо номера сировини відповідної рецептури (дані в Таблиці 2);

   2. у клітинку В1 введемо текст «Найменування сировини». В клітинку В2 введемо назву сировини з таблиці листка «Сировина», використовуючи номер сировини, що знаходиться в клітинці А2 листка «Таблиця» . Для цього зробимо активною клітинку В2 і введемо функцію =ВПР(ТАБЛИЦЯ!A2;СИРОВИНА!$A$2:$B$54;2), (для введення функції ВПР використаємо «Мастер функций» (рис. 15), який активізують кнопкою «Функции» або за допомогою команди меню Вставка — Функция…).

Рис. 15

Копіюємо формулу в клітинки В3:В7 так само, як в п.2.

3. у клітинки С1 і D1 введемо відповідно назви колонок «Вага сировини» та «Питома вага». Виділимо дані в колонках 5 та 6 (крім підсумкового рядка і заголовків) таблиці, що розташована на листку «Форма» і скопіюємо в буфер обміну командою Правка — Копировать головного меню. Для вставки даних у третю та четверту колонки (тільки значення без формул) використаємо команду Специальная вставка… з меню Правка і у вікні, що відкриється відмітимо крапкою параметр «значения»;

4. у клітинку Е1 введемо текст «Група». В колонку "Група", необхідно занести "1", якщо питома вага не більше 10, або "2" в іншому випадку.

Розглянемо функцію ЕСЛИ у повному форматі:

ЕСЛИ(логічний вираз; значення_якщо _істина; значення_якщо _хибність).

Ця логічна функція визначає напрямок обчислень і відіграє роль оператора умовного переходу. Вона використовується для перевірки умови стосовно значень та формул і повертає одне розраховане значення, якщо задана умова після розрахунку дає значення ІСТИНА, й інше розраховане значення, якщо значення умови після розрахунку буде ХИБНІСТЬ. Аргументами функції можуть бути до семи вбудованих функцій в разі складних перевірок.

Активізуємо кнопку«Функции» , знаходимо функціюЕСЛИ й активізуємо кнопку ОК. В аргументі Лог_выражение (рис. 16) створюємо умову, яка буде мати вигляд D2<=0. Аргумент Значение_если_истина містить 1 (умова належності до першої групи), якщо умова аргументу Лог_выражение має значення ІСТИНА, аргумент Значение_если_ложь містить 2 (умова належності до другої групи), й активізують кнопку ОК.

Рис. 16

Після цього Мастер функций закінчує свою роботу.

В клітинці Е2 отримали № групи за допомогою функції

=ЕСЛИ(D2<=10;1;2)

Рис. 17

4. Для побудови діаграми слід виділити колонки 2 та 3 (лист «Таблиця») таблиці, дані яких увійдуть у діаграму, після чого активізувати кнопку Мастер диаграмм або команди меню Вставка ─ Диаграмма… і вибрати або ввести дані у вікна майстра діаграм (див. опис в теоретичній частині). В результаті отримаємо діаграму (див.рис._18) на аркуші «Диаграмма1»;

Рис. 18

5. Створимо листок «Фільтр» з таблицею, як в п. 1. Дані беремо з листа «Таблиця». Виділимо дані в колонках 1 та 5 таблиці, що розташована на листку «Таблиця» і скопіюємо в буфер обміну командою Правка — Копировать головного меню. Перейдемо на лист «Фільтр». Для вставки даних у нову таблицю (тільки значення без формул) використаємо команду Специальная вставка… з меню Правка і у вікні, що відкриється відмітимо крапкою параметр «значения». Встановлюємо курсор у таблицю та активізуємо команди Данные ─ Фильтр ─ Автофильтр. В результаті у верхній частині кожного стовпця таблиці з'являються кнопки списків . Далі у списку поля «Група» вибираємо потрібну нам умову фільтрування. Наприклад, значення «1» або «2». (Результат роботи командифільтрації даних по групам сировини - див. рис. 6);

6. Для виконання цього пункту завдання створимо лист «Упорядкування» (див. п. 5). Для виконання сортування даних в таблиці, курсор встановлюємо у будь-яку комірку таблиці (лист «Упорядкування») й активізуємо команди Данные ─ Сортировка…. У результаті на екрані дисплея з'являється вікно Сортировка диапазона і в списку поля Сортировать по вибираємо ключ сортування (поле «Група») та зазначаємо вид сортування (по возрастанию). Після цього активізуємо кнопку ОК. (Результат обробки команди Упорядкування даних по групам представлений на рис. 10).

7. Створюємо лист «Підсумки» (див. п. 5). Треба знайти сумарне значення поля «Вага сировини» для кожної групи сировини. З цією метою спочатку слід упорядкувати дані в полі «Група». Далі, встановивши курсор у таблицю (лист «Підсумки»), активізуємо команди Данные ─ Итоги…. У вікні «Промежуточные итоги», задаємо такі параметри:

- у полі «При каждом изменении в:» у списку вибираємо поле «Група»;

- у полі «Операція:» в списку вибирають відповідну функцію для розрахунку. Це функція Сумма;

- у полі «Добавить итоги по:» активізують перемикачі ліворуч від тих полів, значення яких необхідно розрахувати. Це поле «Вага сировини».

Після цього активізуємо кнопку ОК. (Результат виконання представлений на рис. 12).

8. Перед друкуванням будь-якого документа, щоб ознайомитися з тим, який вигляд він матиме на папері, і при потребі внести в документ необхідні корективи, його можна переглянути за допомогою команди Файл — Предварительный просмотр. Для друкування таблиці можна активізувати кнопку Печать панелі інструментівСтандартна або активізувати команди Файл — Печать… головного меню і задати потрібні параметри. Збереження таблиці на магнітному диску здійснюється командами Сохранить как або Сохранить пункту меню Файл. Перша команда зберігає файл із новим ім'ям, друга — з його початковим ім'ям.

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