49160 (Створення електронної таблиці "Відомість нарахування премії за 1 квартал 2010 року"), страница 4

Скопіювати аркуш “Розр_Горобець” та перейменувати на “Фільтр-Р_Горобець".

Вибрати як заголовок таблиці рядок нумерації (11). Рядок (10) слугуватиме для наочності.

Скопіювати заголовок таблиці (10 - 11-й рядки) в рядки 33, 34, щоб сформувати діапазон умов.

Сформуємо умови відбору.

Примітка.

Логічне “і” в Excel реалізується в одному рядку, “або” - в різних.

Виділити діапазон А11: L26.

Виконати команду ДаніФільтрРозширений фільтр.

В діалоговому вікні розширеного фільтра задати:

В полі “Обробка” активізувати пункт

“Скопіювати результати до іншого розташування”;

Вихідний діапазон$A$11: $L$26;

Діапазон умов$A$33: $L$35;

Діапазон для результату$A$38: $L$40.

Натиснути ОК.

Зберегти таблицю (додаток Д) у книзі під тим самим іменем.

Фільтрація за критерієм, що обчислюється

Завдання:

На аркуші “Фільтр-Форм_Прізвище" вивести запис з найбільшою Нарахованою премією

Етапи виконання:

Скопіювати аркуш “Розр_ Горобець” та перейменувати на “Фільтр-Форм_Горобець".

Вибрати як заголовок таблиці рядок нумерації (11). Рядок (10) слугуватиме для наочності.

Скопіювати заголовок таблиці (10 - 11-й рядки) в рядки 3, 34, щоб сформувати діапазон умов.

Сформувати таблицю критеріїв (умови фільтрації).

Примітка.

В діапазоні умов заголовок стовпця (11), за яким проводиться відбір, не повинен збігатися із заголовком початкового списку.

У рядку 34 в клітині L34 замість числа 12 введемо довільний текст, наприклад, “ Найбільша нарахована премія". (Можна взагалі залишити клітину пустою).

Сформувати умови відбору. В клітину L35 ввести =L12=МАКС ($L$12: $L$26).

Примітка.

Результатом виконання даної формули є TRUE (істина) або FALSE (хибність) залежно від значення клітини L12.

Виділити діапазон А11: L26.

Виконати команду Дані Фільтр Розширений фільтр.

У діалоговому вікні розширеного фільтра задати:

Натиснути ОК.

Зберегти таблицю (додаток Е) у файлі під тим самим іменем.

3.6 Підведення проміжних підсумків

Завдання:

На аркуші “Пр_Прізвище” підвести проміжні підсумки: за посадою знайти середню Умовну зарплату.

Етапи виконання:

Скопіювати аркуш “Розр_Горобець” та перейменувати на “Фільтр-Пр_Горобець".

Рядок нумерації (11) вилучити, заголовок таблиці - рядок (10).

Відсортувати таблицю за полем Посада, при зміні якого необхідно підвести проміжні підсумки (стовпчик Порядковий номер не підлягає сортуванню).

Виділити діапазон А: J21.

Виконати команду ДаніПідсумки.

В діалоговому вікні проміжних підсумків задати:

Натиснути ОК.

Примітка.

Результатом є структурована таблиця з підсумками по кожному відділу та у цілому.

Зберегти таблицю (додаток Ж) у файлі під тим самим іменем.

3.7 Процес консолідації робочих листів

Завдання:

Виконати два види консолідації за розташуванням: динамічну (аркуш “КД_Прізвище”) та статичну (аркуш “КС_Прізвище”) на прикладі знаходження середнього за стовпцем 12 за три місяці.

Етапи виконання:

Скопіювати аркуш “Розр_Garmash" п’ять разів та перейменувати на “Січень_Garmash", “Лютий_Garmash, “Березень_Garmash", “КС_Garmash”,

“КД_Garmash".

Консолідація даних “За розташуванням" (Статистична).

На аркуші “КС_Garmash" очистити діапазон клітин L12: L26.

Виділити діапазон L12: L26.

Командою ДаніКонсолідація активізувати однойменне діалогове вікно.

У списку “Функція” вибрати функцію, що використовується при об’єднанні даних, - середнє.

У полі “Посилання” за допомогою кнопки перейти на лист “Січень_Горобець”, виділити той самий діапазон L12: L26 та натиснути Додати.

Аналогічно передати у список діапазонів дані за Лютий та Березень.

Примітка.

Результатом виконання статичної консолідації на відміну від динамічної є неструктурована таблиця з підсумками за три місяці, в якій не можна їх переглянути детально. На аркуші із статичною консолідацією не відбувається змін у результаті проведення якихось змін в даних на аркушах, які беруть участь в консолідаціїї.

Аркуш зі статичною консолідацію наведено у додатку И.

Консолідація даних “За розташуванням" (Динамічна).

На аркуші “КД_Горобець” виконати описану послідовність дій. Щоб консолідація була динамічною, потрібно у вікні консолідації активізувати поле “Створювати зв’язки з вихідними даними".

Примітка.

Результатом виконання динамічної консолідації на відміну від статичної є структурована таблиця з підсумками за три місяці, в якій можна детально їх переглянути шляхом активізації кнопки або згорнути (приховати) кнопкою .

Зберегти таблицю (додаток И) у файлі під тим самим іменем.

3.8 Технологія побудови графіків

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

Завдання:

Побудувати:

лінійний графік співвідношення даних стовпчиків 10 та 12 (аркуш "Д_Прізвище"), кругову діаграму% співвідношення "Всього нараховано з/п за квартал" за категорією (аркуш "Круг_Прізвище").

Етапи виконання:

Скопіювати аркуш “Розр_Горобець” та перейменувати на “_Garmash".

Приховати рядок 11.

На робочому аркуші виділимо діапазони комірок (J10: J26, L10: L26) з даними Умовна з/плата та Нараховано премії (несуміжні діапазони виділяються за допомогою Ctrl).

Активізувати Майстер діаграм за допомогою однойменної кнопки на стандартній панелі інструментів.

У діалоговому вікні Майстра виконати чотири кроки.

Крок 1 Вибрати тип та вигляд діаграми.

Крок 2 Змінити або вибрати діапазон даних, на основі яких буде побудовано діаграму.

Крок 3 Ввести заголовки діаграми та її осей. Встановити параметри діаграми, пов’язані з її зовнішнім оформленням.

Крок 4 Вибрати варіант розміщення діаграми (на поточному або окремому аркуші)

Натиснемо кнопку Готово.

Аналогічним чином будуємо кругову діаграму відповідно до поставленого завдання.

Зберегти таблицю з графіком (додаток К) у файлі під тим самим іменем.

4. Інструкція з експлуатації таблиці

Завантажити Excel.

Відкрити файл “№варіанта_Горобець. xls”

Даний файл має такі аркуші:

Розр_Горобець" - розрахунковий вигляд таблиці;

“Форм_Горобець” - формульний вигляд таблиці;

“Сорт_Горобець ”- відсортована таблиця;

“Фільтр-А_Горобець” - таблиця з проведеним автофільтром;

“Фільтр-Р_Garmash" - таблиця з проведеним розширеним фільтром;

“Фільтр-Форм_Горобець" - таблиця з проведеним розширеним фільтром за критерієм, що обчислюється;

“Пр_Горобець” - таблиця з проміжними підсумками;

“Січень_Горобець", “Лютий_Горобець”,

“Березень_Горобець” - вихідні таблиці для проведення консолідації;

“KС_Горобець”, “КД_Горобець” - таблиці з результатами динамічної та статичної консолідації відповідно;

“Д_Горобець", “Круг_Горобець” - таблиці з лінійним графіком та діаграмою.

В побудованій таблиці, виходячи з фактичних даних, заповнюються за власним розсудом тільки клітини стовпців: A, B, D, E, F, G, I.

Для того щоб ввести числові дані, необхідно активізувати потрібну клітину, ввести дані, натиснути Enter. Пересуватися по клітинах таблиці можна за допомогою клавіш управління курсором та за допомогою миші.

Для того щоб перейти на інший аркуш, потрібно покажчик миші навести на ярлик потрібного аркуша, розміщеного в нижній частині документа і натиснути один раз лівою кнопкою миші.

Редагувати дані можна в рядку формул або в самій клітині, натиснувши двічі на ній лівою кнопкою миші, або натиснути F2, попередньо виділивши клітину. Для видалення даних з клітини потрібно її виділити і натиснути Delete. Для виділення діапазону клітин слід протягти покажчик миші від одного кута діапазону до протилежного.

Іноді виникає ситуація, коли потрібно додати чи видалити рядок, наприклад, при зміні кількості найменувань продукції у списку. Щоб вставити рядок, потрібно виділити клітину, над якою вставляємо рядок Щоб вставити декілька рядків, потрібно виділити таку саму кількість рядків знизу від місця вставки. Щоб одночасно виділити декілька рядків, натиснути ліву кнопку миші і протягнути її по заголовках. Тоді відкрити меню Вставка→Рядки. Вставлені рядки будуть такого самого формату, що й виділені. Щоб видалити рядки, необхідно їх виділити, відкрити меню Правка→Видалити. Всі посилання у формулах і іменах відповідно поновлюються.

Щоб змінити ширину стовпця чи рядка, потрібно помістити покажчик миші на межу, яка розділяє заголовки двох стовпців чи рядків, натиснути кнопку миші і перетягнути в потрібному напрямку границю або виконати команду меню Формат→Стовпець (Рядок) →Автодобір ширини (висоти).

Зручний і швидкий спосіб переглянути деякі дані надають діаграми. Наприклад, на графіку “XY_Garmash" можна визначити собівартість продукції та ціну реалізації за тонну. Для цього слід навести курсор на ряд даних, і автоматично висвітиться значення.

Захист формул.

Виділити діапазон клітин, в яких потрібно приховати формули. За необхідністю можна виділити діапазон клітин чи весь аркуш.

Команда головного меню Формат→Клітинки→Захист. Установити прапорець “Сховати формули". Натиснути ОК.

Команда головного меню Сервіс→Захист→Захистити аркуш.

Якщо виникає необхідність у зміні вмісту комірок, слід зняти захист комірок, виконавши зворотні дії „Сервіс/Захист аркуша”. Для більш надійного захисту даних у таблицях можна встановлювати пароль.

Вихід з Excel. У меню Файл вибрати команду Вихід або натиснути мишею кнопку мінімізації .