Користувальницькі довідники в MS Excel, MS Office, Програмні керівництва, статті

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


У цій статті я хотів би поділитися з читачами своїм рішенням даної проблеми. Стаття також порушує питання про роботу з масивами інформації: укрупнення, класифікація, вибірка та ін


Невелике введення


На початку обмовлюся про термін з назви статті – «користувальницькому довіднику». Користувальницьким довідником я називаю якийсь масив даних, розташований на окремому аркуші або в окремій книзі (для зручності). Цей масив даних містить інформацію про ступінь угруповання даних, про параметрах відображення інформації та ін. Користувальницький довідник має схожість з довідниками 1С (як мені здається), хоча і віддалене.


Отже, тепер про все по порядку.


1. Довідник з метою уніфікації інформації


Проблема: Є дані з різних баз, від різних користувачів. Номенклатура, по суті, одна, а найменування написані по різному. Мета даного довідника – уніфікувати номенклатуру, щоб інформація стала однорідної і можна було застосовувати знайомі всім функції (СУММЕСЛІ, СЧЕТЕСЛИ, ВВР, ГПР та ін.)


Наприклад, є дані з реєстру приймально-здавальних актів (Таблиця № 1)

Таблиця № 1

Реєстр пса


З таблиці № 1 відразу видно проблема – одна і та ж номенклатура записана по-різному. А значить, функції СУММЕСЛІ, СЧЕТЕСЛИ коректно застосувати не удасться.


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


Короткий порядок дій:


– Копіюємо лист з вихідними даними в окрему книгу;


– Створюємо у цій же книзі лист “довідник”, де, наприклад, в стовпці A будемо перераховувати наявну номенклатуру, а в стовпці B – “правильна назва”, яке ми хотіли б бачити. Довідник поки не заповнюємо.


– На аркуші з вихідними даними правіше всіх вихідних даних створюємо стовпець, шапка якого називається “Номенклатура загальна” (де буде відображатися “правильна назва”), а під шапкою пишемо і розтягуємо вниз до кінця таблиці з вихідними даними формулу = ВПР (B4; довідник! A: B; 2; 0) (B4 – в даному прикладі осередок, що містить вихідну номенклатуру). Після розтягування формул до кінця отримаємо, що всі значення стовпця “Номенклатура загальна” містять помилку Н / Д (Адже ми ще не заповнювали довідник!);


– Ставимо автофільтр на стовпець “Номенклатура загальна” з умовою Н / Д.


– Починаємо заповнювати лист довідник, копіюючи з листа з вихідними даними значення стовпця “Номенклатура”, а навпаки в ручну проставляючи “правильні” до тих пір, поки всі помилки Н / Д не заберуться. Якщо на лист довідник скопіювати лише значення стовпця “Номенклатура” (У стовпець A), не проставлено “Правильних значень” (У стовпець B), то значення функції ВПР в даному випадку старіє дорівнює 0. Тут є невеличка хитрість – кожен раз при додаванні в довідник “Номенклатури” (особливо при створенні першого довідника), зручно кожен раз, заходячи на лист з вихідними даними оновлювати автофільтр на умова Н / Д, хоча й не обов’язково, т. к. значення Н / Д в міру заповнення довідника будуть змінюватися на “правильні номенклатури”, а Excel автоматично автофільтр не оновлює.


Для нашого прикладу, довідник може виглядати наступним чином (Таблиця № 2).

Таблиця № 2

Довідник сировини


З таблиці № 2 бачимо, що в 1 стовпці стоять найменування сировини (по суті, одного виду), отримані з різних джерел. У стовпці 2 об’єднуємо ці види сировини в один.


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


В результаті редагованих лист “Вихідні дані” буде виглядати так, як представлено в таблиці № 3.

Таблиця № 3

Реєстр пса з додаванням стовпця “Номенклатура загальна”


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


2. Довідник з метою угруповання


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


Проблема: якщо є сировина кількох видів (В нашому прикладі це А і Б) і кожен з цих видів має клас (цифри після букв). Для когось аналізу нас не цікавить клас, а цікавить тільки вид сировини. Отримана в попередньому розділі таблиця цього зробити не дозволяє.


Рішення: цю задачу також вирішуємо за допомогою “Пользовательского довідника”


Короткий порядок дій:


– На початковому аркуші правіше стовпця “Номенклатура загальна” створюємо стовпець з шапкою “Вид загальний”, під шапкою пишемо формулу = ВПР (B4; довідник! A: C; 3; 0) і розтягуємо вниз до кінця таблиці. В даному випадку значення функцій ВПР будуть рівні нулю (адже стовпець C – не заповнений);


– На вже створеному листі “довідник” в стовпці C додаємо “Вид сировини”, яку заповнюємо вручну (також зручно користуватися автофільтрів з умовою нуль, як і в минулому розділі з умовою Н / Д.


В результаті перерахованих дій отримаємо наступний результат (таблиця № 4).

Таблиця № 4.

Реєстр пса з додаванням стовпця “Вид загальний”

Тепер, використовуючи стовпець “Номенклатура загальна”, можна коректно застосовувати функції, подібні функціям СУММЕСЛІ і СЧЕТЕСЛИ, але вже групуючи сировину за видами.


Невелике зауваження: в даному випадку, при створенні стовпця “Вид загальний” можна у функції ВПР спиратися не на стовпець B, а на стовпець “Номенклатура загальна”, тоді в довіднику потрібно правіше створити зв’язку Номенклатура загальна – Вид загальний. Це трохи заощадить час, т. к. різних значень в стовпці “Номенклатура загальна” менше (А найчастіше на порядок!), ніж в стовпці “Номенклатура”. Цю зв’язку потрібно розташовувати на аркуші довідник право (а не в жодному випадку не внизу) зв’язки Номенклатура – Номенклатура загальна (наприклад, в стовпці D (для зручності відображення залишаючи стовпець C порожнім).


3. Довідник з метою відображення та обліку інформації


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


Рішення: створюємо довідник, який буде враховувати співвідношення дата – декада з ознакою враховувати – не враховувати.


Короткий порядок дій:


– На початковому аркуші правіше стовпця “Вид загальний” створюємо стовпець з шапкою “Декада”, під шапкою пишемо формулу = ВПР (A4; довідник! E: F; 2; 0), де A4 – вихідна дата з реєстру пса і розтягуємо вниз до кінця таблиці. В даному випадку значення функцій ВПР будуть рівні помилку Н / Д;


– На аркуші “Довідник” створюємо в стовпцях E і F зв’язку Дата – Декада. Такий довідник створюється досить швидко, т. к. кожна дата елементарно прив’язується до однієї з 3-х декад. Можна, природно, цю процедуру ще більше спростити, використовуючи функцію ЯКЩО і ДЕНЬ, хоча це і не обов’язково.


– На початковому аркуші правіше стовпця “Декада” створюємо стовпець з шапкою “Відображати декаду”, під шапкою пишемо форулу = ВПР (значення декади; довідник! H: I; 2; 0), де значення декади – значення клітинки напроти в стовпці “Декади”;


– На аркуші “Довідник” створюємо в стовпцях H і I зв’язку Декада – Відображати декаду. Такий довідник створюється елементарно, т. к. має тільки 3 рядки і 2 шпальти. За замовчуванням, ставимо в стовпці “Відображати декаду” всюди 1.


– Тепер на вихідному аркуші в графі “Відображати декаду” всюди стоять значення “1”.


– Правіше стовпця “відображати декаду” робимо графи “Відображати кількість” (перемножування відповідних осередків стовпця “кількість” і стовпця “Відображати декаду”) і “Відображати Всього з ПДВ, руб. “(перемножування відповідних осередків стовпця” Всього з ПДВ, руб. ” і стовпця “Відображати декаду”).


– У випадку, якщо необхідні дані за певну декаду, ставимо на аркуші “Довідник” навпроти всіх не потрібних декад “0”, а навпаки потрібної декади залишаємо “1”.


В результаті виконання вищевказаних дій отримуємо такі результати (таблиця № 5)

Таблиця № 5


Реєстр пса з подекадної розбивкою і параметрами відображення


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


Підведення підсумків


Підсумковий довідник на підставі 3-х розділів буде виглядати наступним чином (таблиця № 6).

Таблиця № 6


Підсумковий довідник


З зазначеним в розділах 1-3 прикладом можна ознайомитися в форматі Excel, перейшовши за посиланням.

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*