Excel 2010: що можуть «розумні» таблиці
Вітаю всіх співробітників «Б & К»! У програмі Excel 2010 довільну ділянку робочого листа можна перетворити на таблицю. Для цієї операції навіть передбачено спеціальний розділ на стрічці головного меню. Підкажіть, що дає таке перетворення на практиці і для чого воно потрібне? Чим (якщо не враховувати форматування) така таблиця відрізняється від робочого листа Excel? Спасибі.
Олександр Тимофєєв, головний бухгалтер, м. Харків
Відповідає Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
Інструмент роботи з таблицями з’явився в Excel досить давно (у попередніх версіях програми він називався списками). Однак саме в Excel 2007, 2010 засоби роботи з таблицями набули рис по-справжньому закінченого інструменту. До того ж дуже корисного для роботи з бухгалтерськими базами даних. Особливості роботи з таблицями в Excel 2010 розглянемо на прикладі бази, наведеної на рис. 1. Це фрагмент реєстру податкових накладних. У ньому я залишив усього 10 записів. А сама база складається з таких полів:
— «№Док» — номер документа (накладної). Зовні номер виглядає як число, однак він зображений у текстовому форматі;
— «Дата» — дата реєстрації документа. Відформатовано її у вигляді дати;
— «НаимПредпр» — найменування підприємства (контрагента). Це текстова колонка;
— «ИНН» — індивідуальний податковий номер. Колонку також відформатовано як текст;
— «ОбщСум» — загальна сума за накладною, уключаючи ПДВ. Ці значення визначаються за формулою. Наприклад, для комірки «E2» формула буде такою «=F2+G2» (сума за товаром плюс ПДВ);
— «СумБезНДС» — сума без ПДВ. Це значення зображене як число;
— «НДС» — сума ПДВ. Для комірки «G2» формула має такий вигляд: «=F2/5».
Думаю, з базою все зрозуміло. Тепер наше завдання — перетворити її на таблицю Excel та подивитись, які переваги дає таке перетворення при обробці даних.
Створення таблиць в Excel 2010
Щоб створити таблицю з довільної бази даних в Excel 2010, робимо так:
1. Викликаємо Excel 2010, у ньому відкриваємо документ, як наведено на рис. 1.
2. Ставимо покажчик активної комірки усередині області з базою даних. Для нашого прикладу це може бути будь-яка комірка в межах блока «A2:G10». За бажання діапазон комірок можна вказати й уручну, виділивши його на робочому листі. Це може бути блок будь-якого розміру, але краще вказувати лише адреси колонок. Річ у тім, що з часом кількість записів у базі даних може зростати. Тому не варто обмежувати її розмір конкретним номером рядка на робочому листі. Виходячи з цього, у нашому прикладі діапазон комірок для перетворення на таблицю міг би виглядати так — «A:G».
3. Переходимо до меню «Главная».
4. На стрічці цього меню в розділі «Стили» клацаємо по іконці «Форматировать как таблицу» (рис. 2). Відкриється вікно з варіантами форматування, зображене на рис. 3.
5. Подвійним клацанням миші вибираємо будь-який варіант. Я вибрав «Стиль таблицы: светлый 1». З’явиться вікно, наведене на рис. 4. У ньому Excel 2010 покаже діапазон комірок, який він передбачає перетворити на таблицю.
6. Уключаємо прапорець «Таблица с заголовками». База даних, наведена на рис. 1, містить заголовки, і ми повинні повідомити про це програмі Excel.
7. Перевіряємо діапазон комірок, які Excel пропонує включити до таблиці. Якщо діапазон Excel визначив правильно, натискуємо «ОК». Якщо діапазон вибрано неправильно, коригуємо його (це можна зробити прямо на робочому листі). І лише після цього натискуємо «ОК». Таблиця набере вигляду, як наведено на рис. 5.
Щоб відмінити форматування блока комірок у вигляді таблиці, робимо так.
1. Ставимо покажчик активної комірки всередину таблиці.
2. Клацаємо по кнопці «Конструктор» головного меню програми.
3. У розділі «Сервис» клацаємо по кнопці «Преобразовать в диапазон».
Однак повернемося до таблиці на рис. 5 та подивимось, що ми отримали насправді? У результаті наших дій Excel 2010 проаналізував вміст робочого листа довкола активної комірки. У блоці «A1:G11» він виявив базу даних, виділив її та застосував до комірок параметри форматування зі стилю «Стиль таблицы: светлый 1». У результаті заголовки таблиці набули напівжирного зображення, а рядки з парними номерами відформатовано на сірому фоні (таке оформлення робить таблицю більш читабельною).
Порада Щоб повною мірою скористатися перевагами таблиць, зберігайте файли у форматі Excel 2007, 2010 (з розширенням «*.xlsx»).
Але це не все. Крім форматування, Excel 2010 надав таблиці низку унікальних властивостей, які роблять роботу з нею набагато зручніше, ніж зі звичайними комірками робочого листа. Розглянемо ці особливості докладніше.
Фільтрація даних у таблицях Excel
Подивіться на перший рядок блока комірок, перетвореного на таблицю. У ньому з’явилися значки вибору, характерні для інструменту «Автофильтр». Excel уключив його на етапі перетворення блока комірок на таблицю. Клацання на будь-якому з цих значків розкриває спеціальне меню, через яке можна зробити вибірку записів із бази даних за заданою умовою. Для прикладу виберемо з бази даних відомості щодо контрагента «ООО "xPeria"». Робимо так.
1. Клацаємо по значку випадного списку в колонці «НаимПредпр». Відкриється вікно, як наведено на рис. 6.
2. У ньому залишаємо галочку лише біля елемента «ООО "xPeria"». Решту галочок прибираємо.
3. Натискуємо «ОК». На екрані залишаться лише записи за контрагентом «ООО "xPeria"».
Зрозуміло, що при роботі з автофільтром можна використовувати й інші умови — «содержит», «не содержит», «больше», «меньше» тощо.
Автоматичне застосування фільтру — важлива, але не єдина перевага таблиць Excel 2010. Є й інші можливості, які допоможуть бухгалтеру працювати з великими обсягами даних.
Імена таблиць як посилання
Відразу після створення таблиці 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» (перша вільна комірка в колонці «Дата»).
3. Вводимо значення, наприклад «17/01/2012».
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
При роботі з великими таблицями прийнято фіксувати рядок заголовків так, щоб він завжди залишався на екрані. В Excel 2010 для цього призначено іконку «Закрепить области», яка розташована на стрічці меню «Вид» у групі «Окно». Після перетворення блока значень на таблицю Excel необхідність у цій іконці зникає. При перегортанні таблиці вниз Excel замість літерних позначень колонок («A», «B», «C» тощо) підставить назви полів із рядка заголовків. Щоб подивитись на роботу цього інструменту, робимо так.
1. Відкриваємо документ з таблицею, як наведено на рис. 5.
2. Додаємо до бази даних декілька записів так, щоб вона не поміщалася на одному екрані у висоту.
3. Перегортаємо базу «вниз», щоб заголовки вийшли за межі верхньої частини робочого вікна. Рядок з позначеннями колонок Excel 2010 набере вигляду, як зображено на рис. 8. Отже, при роботі з таблицями необхідність у примусовому закріпленні рядків та колонок зникає.
Підсумки в таблицях Excel
Підсумкові значення за даними таблиці Excel можна отримувати буквально одним рухом миші. Наприклад, для бази на рис. 5 це відбуватиметься так.
1. Відкриваємо документ, ставимо покажчик активної комірки всередину таблиці.
2. Переходимо в меню «Конструктор».
3. У групі «Параметры стилей таблиц» уключаємо прапорець «Показать итоговую строку». У кінці таблиці з’явиться рядок із загальним підсумком (рис. 9).
4. Клацаємо лівою кнопкою по значенню підсумку або по слову «Итог». Поряд з коміркою з’явиться значок випадного списку.
5. Клацанням миші розкриваємо цей список. З’явиться меню з 10 пунктів. У них перелічені можливі функції для визначення підсумку («Среднее», «Количество», «Максимум», «Минимум» тощо). Варіант «Другие функции…» відкриває доступ до всіх вбудованих функцій із бібліотеки Excel 2010.
6. У списку вибираємо відповідну функцію та клацаємо по ній лівою кнопкою миші. Excel 2010 тут же перерахує підсумок.
Прийоми адресації в таблицях Excel
При зверненні до даних усередині таблиці замість діапазону значень зручно використовувати імена полів. Наприклад, щоб підсумувати всі числа у стовпці «НДС» у таблиці на рис. 5, можна скористатися формулою «=СУММ(Таблица1[НДС])». Ця формула робить те ж саме, що і «=СУММ(F2:F10)». Але робота з іменами має істотні переваги. Зокрема, у формулах з іменами не доведеться вказувати фізичні адреси комірок. Дописуючи дані в кінці таблиці, діапазон підсумовування змінюватиметься автоматично.
Крім того, при роботі з таблицями можна застосовувати спеціальні способи адресації. В Excel 2010 передбачено п’ять таких способів, а саме:
— «=Таблица1[#Все]» — посилання на всю таблицю, уключаючи заголовки стовпців, дані та рядок підсумків;
— «=Таблица1[#Данные]» — посилання на дані без урахування рядка заголовків;
— «=Таблица1[#Заголовки]» — посилання на перший рядок таблиці разом із заголовками стовпців;
— «=Таблица1[#Итоги]» — посилання на рядок підсумків (якщо його включено до активної таблиці);
— «=Таблица1[#Эта строка]» — посилання на поточний рядок. Наприклад, формула «=Таблица1[[#Эта строка];[НДС]]» посилатиметься на значення ПДВ із поточного рядка таблиці.
Ось декілька прикладів формул з використанням спеціальної адресації стосовно таблиці на рис. 5. Формула «=СУММ(Таблица1[#Данные])» підрахує суму всіх чисел у базі даних. У нашому випадку вона дорівнює «42641,85». Такий самий результат можна отримати за формулою «=СУММ(Таблица1[#Все])». Щоб визначити суму ПДВ для накладної з номером «001» (другий рядок таблиці), можна скористатися формулою «=Таблица1[@НДС]». Її потрібно ввести в будь-яку вільну комірку другого рядка робочого листа (наприклад, до «I2»).
Мабуть, це все, що потрібно знати про роботу з таблицями в Excel 2010. На мій погляд, інструмент вийшов зручний, простий у застосуванні. І, що найголовніше, — він чудово пристосований для роботи з таблицями великого розміру. А це означає, що в роботі бухгалтера «розумні» таблиці напевно знайдуть своє застосування.
Успішної роботи! Чекаю ваших листів, пропозицій та зауважень на bk@id.factor.ua , nictomkar@rambler.ru.
