Главная Партнеры Контакты  
Юридическая компания — «Основной закон», консультации и помощь в возвращении депозитов, защита по кредиту

ЮК
"ОСНОВНОЙ ЗАКОН"  

г. Киев, бул. Пушкина, 2а                
тел.: (044) 334-99-77                               
         (095) 407-407-3
         (096) 703-11-82

график работы: пн.- пт. с 9:00 до 18:00
          
                           

 












Рассматривается вопрос о предоставление нотариусам права выдачи извлечения из Реестра прав на недвижимое имущество.
Министерством юстиции был разработан проект Закона «О внесении изменений в некоторые Законы Украины относительно предоставления информации о государст...


Держреєстрація речових прав на нерухоме майно та їх обтяжень у 2014 році буде здійснюватись за новою - удосконаленою та спрощеною - процедурою.
Постанова Кабінету Міністрів "Про затвердження порядку державної реєстрації прав на нерухоме майно та їх обтяжень і Порядку надання інформації з Держа...




Система Orphus


«Фішки» Excel для бізнесу

Вчені вже давно довели: фізіологічно мозок людини використовується повністю. Це, звичайно, не означає, що всі можливості ми користуємося максимально продуктивно, а тому вчитися, в тому числі використовувати ресурси мозку, ніколи не буває зайвим. Але ось сподіватися на існування якоїсь методики, яка дозволить, особливо не напружуючись, підключити інші 95% і стати в 20 разів розумніший, можна вже перестати.

Те ж стосується і Excel. Так, якщо вивчити Visual Basic, написати купу складних макросів, то його можливості стають по-справжньому безмежні. Але це можна сказати і про будь-який інший програмі: якщо навчитися програмувати і дописати її, то легко можна збільшити можливості і в 10, і в 20, і навіть в 100 разів. Тим часом всі основні можливості Excel пов'язані з його базовими функціями, знайомими кожному, навіть «непросунутого» користувачеві.

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

У цьому номері журналу я розповім вам про деякі можливості Microsoft Excel, які дуже корисні при його використанні для управління бізнесом.

Іменовані комірки і функція ВПР

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

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

Однак подібний підхід теж пов'язаний з деякими проблемами, які необхідно вирішити. Уявімо, що керівник відділу продажів компанії, що займається продажем автомобілів, вносить дані про продажі в файл (див. Табл. 1).

З табл. 1 дані переносяться в звіт про прибутки і збитки компанії, фрагмент форми якого наведено в табл. 2.

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

  • осередок B2 табл. 2 (підсумкова виручка від продажу автомобілів) дорівнює осередку B10 табл. 1;
  • осередок B3 табл. 2 (виручка за моделлю 1) дорівнює осередку B4 табл. 1 і т.д.

Але не все так просто. Уявімо, що компанія продала ще один автомобіль, і табл. 1 прийняла інший вид (див. Табл. 1.1).

Зверніть увагу на те, що додавання рядка призвело до зміни індексів осередків. Якщо раніше осередок з підсумкової виручкою була під індексом B10, то тепер він дорівнює B11. Підсумкові продажу за моделлю 2 раніше перебували в осередку B7, а тепер перемістилися в B8 і т.д.

А тепер я відкрию маленький секрет (і, можливо, заощаджу купу часу користувачам Excel, які іноді не можуть зрозуміти, з якої ж причини їх дані «попливли»). При зміні індексу осередків в табл. 1 формули в табл. 2, що має посилання на неї, зміняться лише в тому випадку, якщо в момент зміни обидва файли будуть відкриті. Тобто якщо в момент, коли ми в табл. 1 додали рядок «Автомобіль 6», файл з табл. 2 у цього ж користувача був відкритий, то формула в комірці B2 поміняє посилання з осередку B10 табл. 1 на B11 ​​і т.д.

Якщо ж файл з табл. 2 не було відкрито (а, швидше за все, так і буде, адже керівник відділу продажів, редагує табл. 1, навіть не має доступу до зведеного звіту про прибутки і збитки), то табл. 2 продовжить посилатися на клітинку B10 табл. 1. І умовний фінансовий директор, відкривши звіт, в рядку виручки побачить не загальний дохід від продажу автомобілів, а всього лише виручку від продажів моделі 3.

Як же позбутися цього недоліку?

Одним з варіантів є привласнення осередкам текстових імен замість звичних нам буквено-числових індексів. Так, осередку B10 табл. 1 ми можемо присвоїти ім'я, наприклад, «Ітого_виручка» (закладка «Формули» на панелі інструментів в Excel, «Присвоїти ім'я»), а осередку B9 - ім'я «Ітого_виручка_модель3».

Що це нам дасть?

По-перше, при додаванні рядків або стовпців в таблицю індекс осередку буде змінюватися (як в нашому прикладі, коли осередок B10 табл. 1 перетворилася в табл. 1.1 в B11). Ім'я ж власне осередки завжди залишатиметься незмінним, скільки б рядків або стовпців ми б перед нею не додавали. Тобто осередок, що містить загальну суму виручки від продажу автомобілів, завжди буде носити ім'я «Ітого_виручка», що б ми з таблицею не робили.

По-друге, імена осередків ми теж можемо використовувати в формулах. Тобто замість умовної формули = B10 ми з таким же успіхом можемо використовувати формулу = Виручка_ітого.

І якщо осередок B2 нашої таблиці буде посилатися на клітинку B10 табл. 1, а на осередок з ім'ям «Виручка_ітого» даної таблиці, то ми завжди можемо бути впевнені, що, відкривши звіт про прибутки і збитки, побачимо там загальну суму виручки, а не якусь загадкову величину.

Але даний спосіб хороший тільки в тому випадку, якщо кількість осередків, яким необхідно присвоїти ім'я, невелика. У нашому випадку в табл. 1 ім'я потрібно буде дати тільки чотирьом осередкам: суми виручки по кожній моделі і загального обсягу продажів. А якщо таких осередків сотні? Або тисячі? На практиці мені доводилося зустрічати і таке інше. Тоді просте присвоєння імен може перетворитися в дуже нудну і трудомістку роботу.

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

Наприклад, якщо в осередку B2 табл. 2 ми вкажемо формулу виду:

= ВПР ( "РАЗОМ продажу; A1: B10; 2),

де A1: B10 - масив заповнених даних в табл. 1

то дана формула знайде в табл. 1 рядок, де в стовпці A (перший стовпець зазначеного нами діапазону) написано «РАЗОМ продажу» (вказаний нами параметр поіска3) і виведе значення, наведене в стовпці 2 (вказаний нами останнім порядковий номер стовпчика). Іншими словами, необхідну нам виручку. І знову-таки, що б ми не робили з нашою табл. 1, формула ВВР буде шукати не рядок під якимось певним номером, а рядок, де міститься текст «РАЗОМ Дохід", що нам і потрібно.

Підсумовування за умовами

Тепер припустимо, що у нас є таблиця (див. Табл. 3), куди певний співробітник записує рух грошових коштів (за рахунком або касі): від кого і за що отримано, навіщо і скільки витрачено.

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

Як це зробити на практиці? Найпростіше це здійснити, вдавшись до так званого умовного підсумовування, тобто коли в певну комірку переносяться дані, що відповідають заданим умовам.

Подивимося, наприклад, яка цифра повинна бути перенесена з табл. 3 в табл. 4 в клітинку, наприклад, B5 ( «Господарські витрати за січень»). Очевидно, що це сума всіх витрат з табл. 3, за якими в стовпці B зазначено, що вони є господарськими витратами, а зазначені в стовпці А дати відносяться до січня.

Зробити це можна за допомогою функції СУММЕСЛІМН, що дозволяє підсумувати дані по декільком заданим критеріям. У нашому прикладі в осередок B6 табл. 4 необхідно записати формулу:

= СУММЕСЛІМН ($ D $ 2: $ D $ 7; $ B $ 2: $ B $ 7; "Господарські витрати"; $ A $ 2: $ A $ 7; "<01.02.2015"; $ A $ 2: $ A $ 7; "> 31.12 .2014 "),

де $ D $ 2: $ D $ 7 - посилання на діапазон комірок табл. 3, що містить підсумовувані суми (стовпець «Витрата»);

$ B $ 2: $ B $ 7, $ A $ 2: $ A $ 7 - посилання на діапазони осередків табл. 3, що містять значення, за якими буде проводитися фільтрація (в нашому випадку - стовпці «Стаття руху грошових коштів» та «Дата»);

"Господарські витрати", "<01.02.2015", "> 31.12.2014" - посилання на значення фільтра, при яких дані будуть прийматися в розрахунок.

У нашому прикладі вийде, що витрати коштів потраплять в осередок B6 звіту про рух грошових коштів при дотриманні наступних умов:

  • якщо в стовпці B табл. 3 вказана стаття «Господарські витрати»;
  • якщо в стовпці А табл. 3 дата больше 31 грудня 2014 р і ця ж дата менше 1 лютого 2015 р

Відповідно, якщо ми хочемо отримати виплати за статтею «Господарські витрати» за лютий 2015 р, то у формулі необхідно буде обмежити підсумовувані значення по стовпцю А1 березня 2015 р зверху і 31 січня 2015 р - знизу.

ЯКЩО, НАПРИКЛАД, В фільтри ЗАЗНАЧЕНО «ГОСПОДАРСЬКІ ВИТРАТИ», ТО І В ТАБЛИЦІ ОБОВ'ЯЗКОВО ПОВИННІ БУТИ НЕ «ВИТРАТИ ГОСПОДАРСЬКІ», НЕ «госпвитрати» І Т. Д., А САМЕ «ГОСПОДАРСЬКІ ВИТРАТИ».

Якщо ми хочемо підсумувати витрати грошей за статтею «Маркетингові витрати», то відповідним чином ми міняємо застосовуваний фільтр до колонку B. Якщо ж нам необхідно підсумувати не розходяться, а прихід грошових коштів, то в якості діапазону вказують не стовпець D, а стовпець C.

В результаті, прописавши відповідні формули в звіті про рух грошових коштів, ми отримаємо його заповнений варіант (див. Табл. 5)

5)

Таким чином, використання функції СУММЕСЛІМН дозволяє включати в розрахунок тільки ті значення таблиць, які відповідають одній або кільком заданим умовам.

умовні списки

Описаний раніше інструмент умовного підсумовування можна ефективно використовувати на практиці тільки в тому випадку, якщо стаття руху грошових коштів, наприклад, буде вказана в таблиці саме таким чином, як встановлено фільтр на її підсумовування. Якщо, наприклад, в фільтрі вказано «Господарські витрати», то і в таблиці обов'язково повинні бути не «Витрати господарські», не «госпвитрати» і т.д., а саме «Господарські витрати».

При цьому при заповненні таблиць ніяк не можна забувати про людський фактор. Співробітник може припуститися помилки ненавмисно. Ось особисто ви бачите різницю між словосполученнями «Господарські витрати» і «Господарські витрати»? А тим часом з точки зору Excel це дві різні статті, оскільки в першому випадку між словами стоїть один пробіл, а в другому - два.

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

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

Трохи ускладнити наш приклад (табл. 3), припустивши, що користувач вказує не тільки статтю руху грошових коштів, але і центр фінансової звітності (далі - ЦФО).

3), припустивши, що користувач вказує не тільки статтю руху грошових коштів, але і центр фінансової звітності (далі - ЦФО)

При цьому, наприклад:

  • всі господарські витрати відносяться спочатку на «технічне» ЦФО «Загальні витрати» (ОР), а далі вже розподіляються між «реальними» ЦФО по вибраній базі розподілу;

ВИКОРИСТАННЯ ФУНКЦІЇ СУММЕСЛІМН ДОЗВОЛЯЄ ВМИКАТИ В РОЗРАХУНОК ТІЛЬКИ ТІ ЗНАЧЕННЯ таблиць, ЯКІ ВІДПОВІДАЮТЬ одній або кільком ЗАДАНИМ УМОВ.

  • одержувана ж від покупців виручка, а також оплата придбаних рекламних матеріалів відноситься або на ЦФО «Відділ про даж автомобілів» (ОПА), або на ЦФО «Відділ продажів запасних частин» (ОПЗЧ) в залежності від того, за що саме отримані гроші і що саме ми рекламуємо.

І перед нами стоїть завдання, щоб:

  • в стовпці B користувач не набирав статтю руху грошових коштів вручну, а вибирав із запропонованого списку;
  • те ж саме і в стовпці C, але при цьому пропоновані значення повинні залежати від змісту стовпця B: якщо обрана стаття «Господарські витрати», то пропонується тільки ЦФО «ОР», якщо ж обрані статті «Надходження від покупців» або ж «Витрати на маркетинг », то до вибору повинні пропонуватися значення ЦФО ОПА або ОПЗЧ.

З першою частиною нашого завдання все просто. На окремому аркуші потрібно перерахувати всі статті руху грошових коштів, виділити стовпець B табл. 6 і через інструмент «Дані / Перевірка даних» в «Типе даних» вибрати список, вказавши в якості джерела діапазон наших перерахованих статей.

Але що робити з ЦФО? Найпростіший варіант - теж перерахувати всі наявні ЦФО і в стовпці C табл. 6 вказати їх в якості можливих значень.

Однак в цьому випадку є ймовірність, що співробітник може вказати невірний ЦФО. Наприклад, при надходженні коштів від покупців вкаже «Загальні витрати». Або ж віднесе на «Відділ продажів автомобілів» платежі за господарські товари.

До чого це може призвести? Як ми знаємо, дані з нашого файлу далі йдуть до звіту про рух грошових коштів (при цьому, раз ми використовуємо ЦФО, то і звіт, напевно, у нас сегментований по ЦФО). І якщо в звіті не виявиться потрібного рядка (наприклад, в звіті по загальних витрат може не бути статті «Надходження від покупця», оскільки подібне надходження не планується), то відповідна виплата може просто загубитися.

Тому ми зробимо наступне:

  • поруч із кожною зазначеною статтею руху грошових коштів вкажемо ЦФО, за якими вона може відображатися;
  • кожному з вийшов «наборів» ЦФО дамо ім'я, рівне імені статті руху грошових коштів (робиться це також через «Присвоїти ім'я», але тільки ми вибираємо не одну клітинку, а їх діапазон).

Те, що у нас вийде, прийме приблизний вигляд, представлений в таблиці 6.

І тепер до колонку З табл. 6 ми застосовуємо інструмент «Перевірка даних», в якому також вибираємо список, але в якості джерела даних обираємо не список ЦФО, а вказуємо посилання на сусідню клітинку (в стовпці B) через формулу ДВССИЛ, яка дозволить нам отримати не власне значення осередки в стовпці B, а відразу посилання на перелік значень в діапазоні з ім'ям, яке в даному осередку міститься.

Пам'ятайте, що в імені осередків і діапазонів можна використовувати прогалини. І щоб у нас все вийшло красиво і правильно (тобто не було статей виду «Хозяйственние_расходи» і т.п.), нам ще потрібно в статті руху грошових коштів прогалини замінити на знак підкреслення. В результаті в поле «Джерело» в перевірці даних ми вкажемо формулу:

= ДВССИЛ (ОДСТАВІТЬ); ""; "_")).

Тепер при заповненні колонки З табл. 6 співробітнику буде виводитися для вибору невесь перелік наявних в компанії ЦФО, а тільки ті з них, за якими можлива виплата за вказаною ним статті руху грошових коштів, що суттєво знизить ймовірність помилок і можливість втрати даних.

Умовне форматування

У використовуваному нами прикладі здійснювані виплати грошових коштів (враховуються в табл. 3 або 6, але ми повернемося саме до табл. 3, щоб не перевантажувати приклад) в подальшому формують звіт про рух грошових коштів Компанії.

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

В результаті табл. 5 прийме вигляд, представлений в табл. 7

7

Зазначу, що в Excel дуже зручно контролювати виконання бюджету з використанням такого інструменту, як умовне форматування.

Як ви розумієте, дані в нашому звіті відображаються в режимі online: як тільки співробітник ввів прихід або витрата в табл. 3, вони автоматично з'являються в нашому бюджеті.

В EXCEL ДУЖЕ ЗРУЧНО КОНТРОЛЮВАТИ ВИКОНАННЯ БЮДЖЕТУ ЗА ДОПОМОГОЮ ТАКОГО ІНСТРУМЕНТУ, ЯК УМОВНЕ ФОРМАТИРОВАНИЕ.

Щоб бюджетом було зручно користуватися, зробимо так, щоб при наближенні до планових значень витрати за відповідними статтями виділялися особливим чином. І ми поставимо наступні правила:

  • в разі якщо фактичні витрати за статтею бюджету становить понад 90% від його запланованої величини, осередок буде виділятися жовтим;
  • в разі якщо фактичні витрати перевищив плановий, осередок буде виділятися червоним.

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

Отже, ми виділяємо цікавить нас осередок, наприклад C6 ( «Фактичні платежі за господарськими витратами»), і вибираємо інструмент «Умовне форматування / Правила виділення осередків / Більше» і вказуємо:

Форматувати клітинки, які БІЛЬШЕ: B6 х 0,9 з Жовта заливка і темно-жовтий текст.4

Після цього в разі, якщо фактичні витрати складе більше 90% від планового, ваша осередок забарвиться в жовтий колір. Потім ще раз повторюємо цю операцію (Умовне форматування / Правила виділення осередків / Більше) і вказуємо:

Форматувати клітинки, які БІЛЬШЕ: B6 з Світло-червона заливка і темно-червоний текст.

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

8

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

Точно таким же чином (тільки, природно, навпаки: менше 90% - червоне, від 90% до 100% - жовте) ми можемо виділити і прибуткові статті бюджету. Більш того, виділення витратних статей ми можемо прив'язати до виконання плану прибуткових: якщо, наприклад, ступінь виконання плану по видатках перевищує ступінь виконання плану по приходу, то осередок загоряється червоним. Що, погодьтеся, просто неймовірно зручно. І, головне, ще більш корисно.

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

Нагадую, що, якщо у вас залишилися питання, я завжди з радістю відповім на них - адресу моєї електронної пошти [email protected]

Що це нам дасть?
А якщо таких осередків сотні?
Або тисячі?
Як це зробити на практиці?
Ось особисто ви бачите різницю між словосполученнями «Господарські витрати» і «Господарські витрати»?
Але що робити з ЦФО?
До чого це може призвести?
Главная Партнеры Контакты    
Cистема управления сайта от студии «АртДизайн»