Excel 2018 що можуть «розумні» таблиці, сайт для бухгалтерів бюджетних установ

- «№Док» - номер документа (накладної). Зовні номер виглядає як число, однак він представлений в текстовому форматі;

- «Дата» - дата реєстрації документа. Відформатована вона у вигляді дати;

- «НаімПредпр» - найменування підприємства (контрагента). Це текстова колонка;

- «ІПН» - індивідуальний податковий номер. Колонка теж відформатована як текст;

- «ОбщСум» - загальна сума за накладною, включаючи ПДВ. Ці значення визначаються за формулою. Наприклад, для осередку «E2» формула буде такою «= F2 + G2» (сума по товару плюс ПДВ);

- «СумБезНДС» - сума без ПДВ. Це значення представлено як число;

- «ПДВ» - сума ПДВ. Для осередку «G2» формула виглядає так: «= F2 / 5».

Думаю, з базою все зрозуміло. Тепер наше завдання - перетворити її в таблицю Excel і подивитися, які переваги дає таке перетворення при обробці даних.

3. Переходимо до меню «Головна».

4. На стрічці цього меню в розділі «Стилі» клацаємо по іконці «Форматувати як таблицю» (рис. 2). Відкриється вікно з варіантами форматування, зображене на рис. 3.

6. Включаємо прапорець «Таблиця із заголовками». База даних на рис. 1 містить заголовки, і ми повинні повідомити про це програмі Excel.

7. Перевіряємо діапазон комірок, які Excel пропонує включити в таблицю. Якщо діапазон Excel визначив правильно, натискаємо «ОК». Якщо діапазон вибраний невірно, коригуємо його (це можна зробити прямо на робочому аркуші). І тільки після цього натискаємо «ОК». Таблиця набуде вигляду, як на рис. 5.

Щоб скасувати форматування блоку осередків у вигляді таблиці робимо так.

1. Ставимо покажчик активної клітинки всередину таблиці.

2. Клацаємо на кнопці «Конструктор» головного меню програми.

3. У розділі «Сервіс» клацаємо на кнопці «Перетворити в діапазон».

Фільтрація даних в таблицях Excel

Подивіться на перший рядок блоку осередків, перетвореного в таблицю. У ній з'явилися значки вибору, характерні для інструменту «Автофільтр». Excel включив його на етапі перетворення блоку осередків в таблицю. Клацання на будь-якому з цих значків розкриває спеціальне меню, через яке можна зробити вибірку записів з бази даних по заданій умові. Для прикладу виберемо з бази даних відомості по контрагенту «ТОВ" xPeria "». Робимо так.

1. Клацаємо на значку списку в колонці «НаімПредпр». Відкриється вікно, як на рис. 6.

2. У ньому залишаємо галочку тільки біля елемента «ТОВ" xPeria "». Решта галочки прибираємо.

3. Натискаємо «ОК». На екрані залишаться тільки записи по контрагенту «ТОВ" xPeria "».

Зрозуміло, при роботі з Автофільтри можна використовувати і інші умови: «містить», «не містить», «більше», «менше» і т. Д.

Імена таблиць як посилання

Відразу після створення таблиці Excel привласнює їй унікальне ім'я. За замовчуванням імена будуть такими: «Таблиця 1», «Таблица2» і т. Д. Щоб дізнатися ім'я поточної таблиці, робимо так.

1. Ставимо покажчик активної клітинки всередину області з даними. Чи стане доступно меню «Конструктор» (рис. 7).

2. Клацаємо на цьому меню лівою кнопкою мишки. Відкриється стрічка для роботи з таблицями.

3. У розділі «Властивості» знаходимо параметр «Ім'я таблиці:». У нашому конкретному випадку базі даних податкових накладних Excel присвоїв назву «Таблица1» (рис. 7).

Ім'я таблиці можна відкоригувати. Для цього потрібно увійти в режим Конструктора і в поле «Ім'я таблиці:» (група «Властивості») ввести нову назву і натиснути «Enter».

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

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

Дуже зручно використовувати ім'я таблиці в поєднанні з функціями пошуку «ВПР ()», «ГПР ()». На мій погляд, саме цей випадок представляє для бухгалтера найбільший інтерес. Виходячи з цього, наведу невеликий приклад.

Ми хочемо за номером накладної вибрати з бази даних назву підприємства і записати його в осередок «J1». Робимо так.

1. У осередок «I1» вводимо номер накладної. Наприклад, значення «003».

2. У осередок «J1» вводимо формулу «= ВПР (I1; Таблица1; 3)». У цьому виразі «I1» - осередок з шуканим значенням. Параметр «Таблица1» означає посилання на базу даних. Число «3»-номер колонки в базі, з якої «ВПР ()» поверне результат. Для накладної з номером «003» таким результатом буде «ТОВ" xPeria "».

Створення формул в таблицях Excel

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

1. Відкриваємо документ, який ви бачите на рис. 5.

2. Ставимо покажчик активної клітинки на «B12» (перша вільна комірка в колонці «Дата»).

4. Натискаємо «Enter». В осередках «E10» і «G10» з'являться формули, як ніби вони були скопійовані з вищестоящої рядки.

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

Важливо! При поповненні таблиці новими записами не залишайте порожніх проміжків між базою даних і заповнюється рядком.

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

1. Відкриваємо базу даних, як на рис. 5. Переходимо в кінець таблиці.

2. Ставимо курсор мишки на маркер в правому нижньому кутку останньої клітинки бази даних (рис. 5).

3. Коли курсор прийме форму двонаправленої стрілки, тримаємо ліву кнопку мишки і перетягуємо маркер вниз на кілька рядків.

4. Відпускаємо кнопку мишки. Excel додасть до бази нові записи. Причому в ці записи він відразу скопіює розрахункові формули. У нашому прикладі такі формули з'являться в колонках «E» і «G».

При додаванні нових колонок Excel автоматично приєднає їх до існуючої таблиці. Якщо нова колонка буде містити формули, копіювати їх зовсім не обов'язково. Досить ввести формулу один раз, а Excel сам скопіює її на всю висоту таблиці. На практиці це виглядає так.

1. Відкриваємо документ з таблицею, як на рис. 5.

2. У осередок «H1» вводимо заголовок нової колонки. У нашому прикладі це буде «Контроль».

3. Переходимо на осередок «H2» і вводимо формулу «= G2-ОКРУГЛ (F2 / 5; 2)». Ця формула перевіряє правильність округлення значень з колонки «G».

4. Натискаємо «Enter». Всі елементи таблиці по колонці «H» будуть заповнені формулами.

Заголовки в таблицях Excel

1. Відкриваємо документ з таблицею, як на рис. 5.

2. Додаємо до бази даних кілька записів так, щоб вона не поміщалася на одному екрані в висоту.

Підсумки в таблицях Excel

Підсумкові значення за даними таблиці Excel можна одержувати буквально одним рухом мишки. Наприклад, для бази на рис. 5 це виглядає так.

1. Відкриваємо документ, ставимо покажчик активної клітинки всередину таблиці.

2. Переходимо в меню «Конструктор».

3. У групі «Параметри стилів таблиць» включаємо прапорець «Показати підсумковий рядок». В кінці таблиці з'явиться рядок із загальним підсумком (рис. 9).

4. Клацаємо лівою кнопкою на значенні підсумку або на слові «Підсумок». Поруч з осередком з'явиться значок списку.

Поділитися з колегами