Створення електронної таблиці "Відомість нарахування премії за 1 квартал 2010 року" (стр. 4 из 5)
3.2 Використання в таблиці стандартних функцій
У роботі були використані такі функції: календарні функції ГОД, СЕГОДНЯ, логічна ЕСЛИ та підсумкові СУММ, МАКС, СРЗНАЧ, ПРОИЗВЕД.
Наприклад, щоб знайти суму діапазону H12: H26, активізуємо клітину H27, натискаємо на стандартній панелі інструментів значок автосуми
, в клітині з’являється =SUM (H12: H26). Оскільки запропонований діапазон нас не задовольняє, виправляємо протягуванням миші на (H12: H26) і натискаємо Enter.Введення в клітину D24 функції для обчислення середнього значення діапазону.
Активізувати E29.
Натиснути на кнопці Вставка функції 
на стандартній панелі інструментів.
У списку Категорія вибрати пункт Статистичні.
У списку функцій вибрати функцію СРЗНАЧ і натиснути на кнопці ОК.
Виділити потрібний діапазон (Е13: Е26) методом протягування і натиснути клавішу ENTER.
Аналогічно в клітину E28 вводимо функцію = MAX (E12: E26) для обчислення максимального значення діапазону (E12: E26).
3.3 Форматування таблиці
1) Зміна ширини стовпця:
Переміщуємо праву границю поточного (вибраного) заголовка стовпця до того часу, поки його ширина не досягне необхідного розміру.
2) Форматування таблиці:
Введений текст вирівнюється автоматично в клітині по лівому краю, числа - по правому краю. За допомогою форматування встановлюються необхідні параметри. Формат числа, призначений для клітині, визначає спосіб перегляду числа на аркуші.
Наприклад, для вирівнювання голівки таблиці по центру необхідно її виділити (A10: L10) і скористатися на панелі інструментів Формат / Клітинки... / вибрати вкладку Вирівнювання і у вікні команди встановити: положення “По горизонталі - по центру", “По вертикалі - по центру” і поставити прапорець на “Відображення - переносити по словах”.
3) Збереження електронної таблиці.
Аркуш з побудованою розрахунковою таблицею перейменувати “Розр_Горобець” (додаток А). Зберегти файл
а) під новим ім’ям:
Файл → Зберегти як... Диск А: / №3_Горобець. xls
б) під тим самим іменем:
Файл ®Зберегти,
CTRL/S
на стандартній панелі інструментів
.4) Перетворення таблиці у формульний вигляд.
Для організації наступних аркушів робочої книги треба скопіювати аркуш “Розр_Garmash" необхідну кількість разів.
Для перетворення таблиці у формульний вигляд необхідно вибрати пункт меню Сервіс ® Параметри... ® вибрати вкладку Вигляд і встановити прапорець у команді Параметри вікна напроти Формули.
Таблицю, представлену у формульному вигляді, зберегти на аркуші “Форм_Горобець” (додаток Б) у файлі під тим самим іменем.
3.4 Сортування даних
Завдання:
У аркуші “Сорт_Горобець” виконати сортування таблиці за 3 ключами:
"Категорія" у порядку зростання.
"П.І.П." за абеткою.
"Нараховано премії" у порядку зростання.
Етапи сортування:
Скопіювати аркуш “Розр_Горобець” методом протягування ярлика аркуша та перейменувати новий аркуш “Розр_Горобець (2) ” на “Сорт_Горобець”.
Визначити, що є заголовком таблиці. У даному випадку - це рядок (10).
Приховати рядок нумерації (11): виділити заголовок рядка ® контекстне меню®Приховати.
Виділити діапазон В10: L26.
Виконати дії: Дані®Сортування.
В діалоговому вікні “Сортування" встановити відповідні ключі та параметри:
Натиснути ОК.
Відсортовану таблицю зберегти на аркуші “Сорт_Горобець” (додаток В) у файлі під тим самим іменем.
3.5 Вибірка даних за заданими умовами
Авто фільтр.
Завдання
На аркуші “Фільтр-А_Прізвище” відібрати записи, для яких Посада починається з літери "К" та Нараховано з/п за квартал білише за 7200 грн.
Етапи виконання:
Скопіювати аркуш “Розр_Горобець” та перейменувати на “Фільтр-А_Горобець".
Вибрати як заголовок таблиці рядок нумерації (11).
Виділити діапазон А11: L26.
Виконати дії: Дані ®Фільтр® Автофільтр.
У стовпці Посада назначити умову:
Натиснути ОК.
У стовпці Всього нараховано з/ за квартал назначити умову:
Натиснути ОК.
Відфільтровану таблицю зберегти на аркуші “Фільтр-А_Горобець” (додаток Г) у файлі під тим самим іменем.
Розширений фільтр.
Команда табличного процесора розширений фільтр на відміну від команди автофільтр потребує визначення умови (критерію) відбору даних в окремому діапазоні даного листка поза списком. Для цього необхідно визначити, в якому місці даного листка будуть розміщені умова критеріїв відбору та результати.
Завдання:
На аркуші “Фільтр-Р_Прізвище” відібрати записи, для яких П.І.П. починається з літери “Л” і Категорія менша за 3.
Етапи виконання:
Скопіювати аркуш “Розр_Горобець” та перейменувати на “Фільтр-Р_Горобець".
Вибрати як заголовок таблиці рядок нумерації (11). Рядок (10) слугуватиме для наочності.
Скопіювати заголовок таблиці (10 - 11-й рядки) в рядки 33, 34, щоб сформувати діапазон умов.
Сформуємо умови відбору.
| П.І.П. | Посада | Категорія |
| 2 | 3 | 4 |
| +К* | >3 |
Примітка.
Логічне “і” в 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 та натиснути Додати.