Робота з таблицями баз даних в MS Excel Алгоритм створення (стр. 2 из 3)
Розподілений відрядний заробіток знайдений за формулою:
=E4*$F$2 (комірка F4).
При відсутності інших нарахувань Відрядний заробіток співпадає з величиною суми «всього нараховано».
Для визначення прибуткового податку, відрахування до пенсійного фонду, відрахування на соцстрахування, збору на випадок безробіття, які залежать від розмірів процентів (13%, 2%, 1%, 0,5% відповідно), застосовуємо функцію =ОТБР(число;число_разрядов). Так як число відповідає всьому нарахованому (в даному випадку відрядному заробітку), а число_разрядов = 0, формула виглядає так: =ОТБР(F4;0)*відсоток (0,13;0,02;0,01; 0,005) відповідно для чотирьох полів, які розглядаються).Наприклад, для комірки G4 (Прибутковий податок):
=(ОТБР(F4,0))*0.13.
Щоб вирахувати Профспілкові внески все нараховане множать на коефіцієнт 0,01 або 0, якщо людина входить або не входить до профспілки відповідно. Формула така:
=ЕСЛИ('Облік робітників'!J4="так",F4*0.01,0).
Всього утримано вираховується за формулою:
=СУММ(G4:K4) (для комірки L4).
Сума до видачі – це різниця Розподіленого відрядного заробітку і Всього утримано. Відповідно для комірки М4 формула має вигляд:
=(F4-L4).
Табл. 1.2. Створення таблиці розподілу
1.2Аналіз таблиць баз даних
1.2.1Сортування за кількістю відпрацьованих годин
Ми маємо згідно завдання за результатами розроблення відомості про робітників та таблиці розподілу створити нову таблицю (Табл. 1.3.).
Записи в полях № п.п., прізвище та ініціали, стать, вік, стаж роботи, кількість відпрацьованого часу, всього нараховано, прибутковий податок, відрахування до пенсійного фонду, відрахування на соцстрахування, збір на випадок безробіття, профспілкові внески, всього утримано, сума до видачі і їх назви ми копіюємо з відомості про робітників та таблиці розподілу і вставляємо на потрібне нам місце таким чином:
Натискаємо правою клавішею миші на місці вставки і у списку, що з’явився обираємо Специальная вставка. У вікні, що з’явилося ставимо прапорець навпроти Значения и форматы чисел і натискаємо OK (Рис. 1.7.).
Копіювання записів в інших полях не потребують зміни параметрів.
Рис. 1.7. Параметри спеціальної вставки
Тепер ми маємо виконати сортування за кількістю відпрацьованих годин.
1. Виділяємо діапазон комірок (A2:Q23), так як до нього мають ввійти назви полів.
2. Натискаємо Меню – Данные – Сортировка.
3. У графі Сортировать по вибираємо Кількість відпрацьованих годин і натискаємо OK (Рис. 1.8)
Рис. 1.8. Параметри сортування
Рис. 1.9. Параметри автофільтра
1.2.2Вибір жінок, які не є членами профспілки, віком від 25 до 40 років за допомогою автофільтра
Створюємо новий аркуш MSExcel і називаємо його Автофільтр. Розміщуємо на ньому таблицю з аркуша Сортування.
Виділяємо діапазон комірок (A2:Q23). В меню вибираємо Данные – Фильтр – Автофильтр. Натискаємо на кнопку зі стрілкою
біля запису Стать і зі списку обираємо “жін.”. Аналогічно з записом Членство у профспілці і зі списку обираємо ні. Натиснувши кнопку біля запису Вік зі списку вибираємо Условие. Для вибору людей віком від 25 до 40 років застосуємо Логічне зв’язування даних И. У графі Показать только те строки значения которых: нам треба вибрати або вписати больше 25И меньше 40. Тому заповнюємо графи, як показано на Рис. 1.9.Після натиснення OK отримуємо Табл.1.4.
1.2.3 Вибірчоловіківстарших 40 років, атакожробітників 2-горозряду, якісплачуютьпрофспілковівнескибільше 16 грн. задопомогоюрозширеногофільтра
Створюємо новий аркуш MSExcel і називаємо його Розширений фільтр. Розміщуємо на ньому таблицю з аркуша Сортування, залишивши 4 пустих полів між нею і назвою. В перше пусте поле ми копіюємо заголовки всіх записів. В друге вписуємо під заголовком Стать =“чол.”, а під заголовком Вік >40. В третє вписуємо під заголовком Розряд =“2”; під заголовком Профспілкові внески >16.
Виділяємо діапазон (A6:Q27), в меню натискаємо Данные – Фильтр – Расширенный фильтр. У вікні, що з’явилося (Рис. 1.10), вибираємо Фильтровать список на месте, навпроти графи Диапазон условий натискаємо на кнопку згортання
і виділяємо діапазон (A2:Q4). Після натиснення OK 
Рис. 1.10. Параметри розширеного
Рис. 1.11. Параметри проміжних фільтра підсумків
1.2.4Проміжні підсумки за статями
Створюємо новий аркуш MSExcel і називаємо його Підсумки. Розміщуємо на ньому таблицю з аркуша Сортування.
Алгоритм підведення проміжних підсумків за статями.
1. Виділяємо діапазон комірок (A2:Q23), так як до нього мають ввійти назви полів.
2. Виконуємо сортування за ключовим словом Стать.
3. В Меню натискаємо Данные і обираємо Итоги.
4. У вікні, що з’явилося (Рис. 1.11) у графі При каждом изменении в вибираємо зі списку Стать (підсумки мають бути після кожної зміни статі); у графі Операция – Количество; у графі Добавить итоги по ставимо галочку навпроти Стать, інші знімаємо.
5. Ставимо галочку навпроти Заменить текущие итоги і натискаємо OK.
В результаті отримуємо Табл. 1.6.
Табл. 1.6. Підведення проміжних підсумків за статями
2Графічна частина роботи
2.1Побудова кільцевої діаграми прибуткового податку
Створюємо новий аркуш з назвою Для діаграми 1 – допоміжна таблиця для кільцевої діаграми. На ній розташовуємо таблицю з аркуша Сортування, виконуємо сортування за прізвищем.
Рис. 2.1.Вибір типу діаграми
Виділяємо назву поля «Прізвище І.П.» (діапазон B2:B23) з записами, натиснувши і утримуючи Ctrl, виділяємо також поле «Прибутковий податок» (К2:К23) з записами. На панелі інструментів натискаємо на значок Мастер диаграмм, який допоможе побудувати діаграму за чотири кроки. На першому кроці вибираємо тип діаграми – Кольцевая (Розрізана) (Рис. 2.1.).Натиснувши Просмотр результата, можна переглянути діаграму.
Рис. 2.2.Настройка параметрів діаграми
На наступному кроці нічого не змінюємо, перевіряємо правильність діапазону і натискаємо Далее.
На третьому кроці на вкладинці Заголовки у графі Название диаграммы вводимо Кільцева діаграма прибуткового податку; на вкладинці Подписи данных ставимо відмітку біля підпису Значения (Рис. 2.2.)
На четвертому кроці розміщуємо діаграму на окремому аркушеві під назвою Діаграма 1. Маємо кільцеву діаграму прибуткового податку (Рис. 2.3.).
Рис. 2.3.Кільцева діаграма прибуткового податку
2.2 Побудова кругової діаграми максимального прибуткового податку за розрядами
Створюємо новий аркуш MSExcel і називаємо його Для діаграми 2. Розміщуємо на ньому таблицю з аркуша Сортування.
Таблицю називаємо Проміжні підсумки для кругової діаграми максимального прибуткового податку за розрядами. Виділяємо діапазон комірок (A2:Q23). В меню Данные вибираємо Сортировать. У вікні, що з’явилося, у списку Сортировать по обираємо Розряд і натискаємо OK (Рис. 2.4.). Не знімаючи виділення, виконуємо команду: Меню – Данные – Итоги. У вікні (Рис. 2.5) у графі При каждом изменении в обираємо Розряд, у графі Операция – Максимум, у графіДобавить итоги по – Прибутковий податок і натискаємо OK. Отримуємо Табл. 2.1.
Рис. 2.4. Сортування за розрядом
Рис. 2.5. Настройка проміжних підсумків
Табл. 2.1. Підведення проміжних підсумків для кругової діаграми максимального прибуткового податку за розрядами
Виділяємо комірку F9 і, натиснувши клавішу Ctrl, виділяємо комірки К9, F13, К13, F16, K16, F19, K19, F26, K26, F29, K29. Натискаємо на значок Мастер диаграмм, який допоможе побудувати діаграму за чотири кроки.
Рис. 2.6. Вибір діаграми
Рис. 2.7. Настройка параметрів діаграми
Шаг 1из 4. Зі списку діаграм вибираємо Круговая (об’ємний варіант) і натискаємо Далее (Рис.2.6).
Шаг 2 из 4. Нічого не змінюємо, перевіряємо правильність діапазону і натискаємо Далее.
Шаг 3 из 4. На третьому кроці на вкладинці Заголовки у графі Название диаграммы вводимо Кругова діаграма максимального прибуткового податку за розрядами; на вкладинці Подписи данных ставимо відмітку біля підписів Значения і Долі; Разделитель – (новая строка) (Рис. 2.7.).