Проекти

Центр застосувань математики, інформатики, механіки і статистики

ГоловнаПроектиExcel для фінансистів: Контроль та Аудит

Excel для фінансистів: Контроль та Аудит

Швидкий аналіз

 

Як проаналізувати великий обсяг даних і знайти серед них елементи, що відрізняються від інших? - Така задача виникає перед контролером в різних сферах діяльності. Іноді треба знайти найбільші або найменші елементи, а іноді вище середнього. Умовне форматування застосовують для виділення із загального масиву даних, що відповідають певним умовам. Його можна застосовувати як до даних, введених з клавіатури, так і до результатів обчислень. Найчастіше умовне форматування застосовують до числових даних.

В Excel є багато можливостей швидко візуалізувати дані або отримані результати за допомогою умовного форматування. Одна з таких можливостей - автоматична заливка комірок кольором або присвоєння якого-небудь значка. Крім того, потім можна виконати сортування за кольором або значком.

Приклад 1

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

Для більш ефективного контролю необхідно:

- Розбити його на три групи;

- Відобразити графічно суму в кожному платежі;

- Знайти 10 найбільших платежів;

 

Спочатку за допомогою Умовного форматування розіб'ємо діапазон даних на три кольори в залежності від суми. Виділимо діапазон з даними Головна → Умовне форматування → Правила виділення комірок → Між. У вікні встановимо значення від 1 до 10 000 і заливку, наприклад жовту (рис. 1).

 


Аналогічно зробимо ще двічі, задавши значення від 10 000 до 50 000 і зелену заливку, а також значення від 50 000 до 100 000 і червону заливку. Результат показаний на рис. 2.


 


Тепер дані можна відсортувати за кольором. Виділимо діапазон Дані → Сортування. І відсортуємо дані так, як нам зручно. Точно таке ж сортування можна виконати не тільки за заливкою комірки, але і за кольором шрифту або значкам, які присвоюються за допомогою того ж умовного форматування (рис. 3).

 

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

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

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

Набір значків використовується для класифікації даних за трьома-п'ятьма категоріями, розділеним пороговим значенням. Кожен значок відповідає діапазону значень. Наприклад, в одному з наборів значків червона стрілка вгору відповідає високим значенням; жовта середня стрілка, спрямована в бік, - середнім значенням, а зелена стрілка вниз - низьких значень.

Форматування з використанням гістограми дає зрозумілий візуальний ефект стосовно інших даних. Довжина гістограми відповідає значенню у клітинці. Чим вона довша - тим вище значення.

Для застосування необхідно виділити діапазон комірок, клацнути по кнопці Умовне форматування, вибрати в галереї команду Гістограми, а потім в підпорядкованому меню задати колірне оформлення.

Приклад умовного форматування з використанням гістограм наведено на рис. 4.

 

 

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

 На рис. 5 виділені 10 найбільших сум у платіжних дорученнях.

 

 

За допомогою умовного форматування також не викличе великих труднощів виділити накладні за певний період. Умовне форматування → Правила виділення комірок. При виборі умови Дата можна вибрати відношення виділюваних комірок до сьогоднішньої дати (вчора, сьогодні, завтра, за останні 7 днів, минулого тижня, на поточному тижні, на наступному тижні, минулого місяця, цього місяця, наступного місяця).

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

Природно, створені правила умовного форматування можна міняти і видаляти. Для зміни потрібно клацнути по кнопці Умовне форматування і вибрати команду Управління правилами. У вікні Диспетчер правил умовного форматування в полі списку Показати правила форматування для вибрати лист, для якого проводиться зміна правил. Виділіть правило, яке треба змінити, і натисніть кнопку Змінити правило. У вікні Зміна правила форматування можна вибрати інший тип правила, змінити умову і параметри форматування.

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

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

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

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

 

Пошук повторів

 

Приклад 2

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

 

 

В Excel є кілька способів це зробити.

  1. Виділяємо комірки, в яких треба знайти дублікати.
  2. Вибираємо Умовне форматування → Правила виділення комірок → Інші правила.
  3. Вибираємо тип правила - Форматувати лише унікальні або повторювані значення ..
  4. Зі списку внизу вибираємо Повторювані і задаємо формат (колір) комірки.

 

 Інший спосіб ще простіший.

  1. Виділяємо комірки, в яких потрібно знайти дублікати.
  1. Вибираємо Умовне форматування → Правила виділення комірок → Інші правила.

Уже при виборі команди повторювані значення автоматично відобразяться в колірній заливці.

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

 

 

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

Також важливо не переборщити з використанням умовного форматування, щоб від кольору і піктограм не рябіло в очах. Головне правило - має бути достатньо одного погляду, щоб зрозуміти візуально наведені результати.

 

Пошук пропусків

 

Є документи, які повинні мати послідовну нумерацію, яка не допускає пропусків. Однак на практиці такі пропуски можуть бути присутніми. Наприклад, дані знищені або, навпаки, зарезервовані для запису заднім числом.

Приклад 3

 У сформованому реєстрі платіжних доручень контролеру необхідно перевірити, чи немає пропущених номерів в платіжних дорученнях (рис. 8).

 

 

 

 Якби можна було виконувати арифметичні дії над комірками, що містять номера платіжних доручень, то це значно спростило б завдання. Але проблема в тому, що багато бухгалтерських програм при формуванні шифру документа використовують букви і розділові знаки (крапки, тире, дріб і т. д.). Природно, Excel сприймає такі дані як текст і категорично відмовляється виконувати які-небудь дії над цими комірками. Навіть зміна формату комірки за допомогою команд Формат → Комірки → Числовий не дає жодних результатів. При спробі обчислень Excel видає результат: #ЗНАЧ!.

Є кілька варіантів виходу з такої ситуації.

В нашому випадку відсортуємо по зростанню стовпець «Номер». Вставимо праворуч два стовпці. У першому з них запишемо формулу D2 = ПРАВСИМВ ('рис.8 та 9'!$C2;3) і протягнемо її вниз. В результаті в цьому стовпці отримаємо три останні знаки з сусіднього стовпця, які вже придатні до виконання арифметичних дій. Якщо операцію виконати все одно не виходить, можна застосувати функцію ЗНАЧЕН. Формула сусіднього стовпця E3 = D3-D2. У тих комірках, в яких ми побачимо значення «2», і знаходяться пропущені номери. Є ще інші значення, відмінні від одиниці, вони з’явились через різні типи документів в одному звіті в нашому прикладі. У будь-якому випадку значення, відмінні від одиниці, підлягають уважному вивченню (рис.9).

 

 

 

Застосування зведених таблиць

 

 Про те, як побудувати зведену таблицю, ви уже розглядали.

 На підставі тих самих вихідних даних ми побудували таку таблицю, помістивши «Вид операції» в назви стовпців, «Контрагент» - в поля рядків, а в полі значення - «Суму» (рис. 9).

Побудова такої таблиці займає кілька секунд, зате вона наочно показує нетипові і одиночні операції. До того ж у деяких випадках можна простежити відповідність контрагента і призначення платежу. Також ми бачимо підсумкову суму платежів по кожному контрагенту. При необхідності можна додати у фільтр звіту поле «Дата» і контролювати ті ж показники по днях, місяцях або іншим періодам. Це один із прикладів застосування зведених таблиць при внутрішньому контролі.

 

 

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

Read 5132 times Last modified on Четвер, 09 жовтня 2014 22:18

Leave a comment

Важливо

     Найвище призначення математики полягає в тому, щоб знаходити прихований порядок в хаосі, що оточує нас.

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

Н. Вінер

Що ми вміємо?

01Розглядати часткове явище в ролі прояву загального порядку.

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

03Послідовно і логічно вибудовувати складні концепції або операції.

На сайті 343 гостей та користувачі відсутні