Таблиці у форматі EXCEL 2007

Для спрощення управління логічно пов'язаними даними EXCEL 2007 введено новий формат таблиць. Використання таблиць у форматі EXCEL 2007 знижує можливість введення некоректних даних, спрощує вставку та видалення рядків і стовпців, спрощує форматування таблиць.

Вихідна таблиця

Нехай є стандартна таблиця (діапазон осередків), що складається з 6 стовпців.

У стовпці № (номер позиції), починаючи з другого рядка таблиці, є формула =A2+1 , що дозволяє робити автоматичну нумерацію рядків. Для прискорення введення значень у стовпці Од. (одиниця виміру) за допомогою Перевірки даних створено Випадаючий (розкривається) список.

У стовпці Вартість введена формула для підрахунку вартості товару (ціна * кількість) = E3 * D3 . Числові значення у стовпці відформатовано з відображенням роздільників розрядів.

Дії зі звичайною таблицею

Щоб показати переваги таблиць у форматі EXCEL 2007, спочатку зробимо основні дії зі звичайною таблицею.

Для початку додамо новий рядок таблицю, тобто. заповнимо даними рядок 4 листа:

заповнимо значеннями стовпці без формул (Найменування, Ціна, Кількість);
у стовпцях Вартість та № за допомогою Маркера заповнення скопіюємо формули в комірки нижче;
щоб у новому рядку запрацював Випадаючий список, в стовпці Од.изм. скопіюємо формат в комірку нижче. Для цього виділимо коміркуС3, скопіюємо її в Буфер обміну і, виділивши комірку нижче, через меню Головна/ Буфер обміну/ Вставити/ Спеціальна вставка/ Умови на значення вставимо Випадаючий список (або як на попередньому кроці скопіюємо Маркером заповнення значення з коміркиС3 вС4, тим самим скопіювавши правило Перевірки даних, потім необхідно ввести значення Одиницівимірювання у новому рядку).

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

Тепер розглянемо самі дії, але у таблиці у форматі EXCEL 2007.

Створення таблиць у форматі EXCEL 2007

Виділимо будь-яку комірку розглянутої вище таблиці та виберемо пункт меню Вставка/Таблиці/Таблиця.

EXCEL автоматично визначить, що в таблиці є заголовки стовпців. Якщо прибрати галочку Таблиця із заголовками, то для кожного стовпця будуть створені заголовки Стовпець1, Стовпець2, …

ПОРАДА: Уникайте заголовків у числових форматах (наприклад, «2009») та посилань на них. Під час створення таблиці вони будуть перетворені на текстовий формат. Формули, що використовують як аргументи числові заголовки, можуть перестати працювати.

Після натискання кнопки ОК:

до таблиці буде автоматично застосований стиль із надрядковим виділенням;
у заголовку буде включений фільтр (щоб його відключити виділіть будь-яку комірку таблиці та натиснітьCTRL+SHIFT+L, повторне натискання увімкне фільтр);
стане доступним спеціальна вкладка в меню для роботи з таблицями (Робота з таблицями/ Конструктор), вкладка активна тільки коли виділено будь-яку комірку таблиці;
таблиці буде присвоєно Ім'я, яке можна переглянути через конструктор таблиць або через Менеджер імен ( Формули/ Певні імена/ Менеджер імен ).

ПОРАДА: Перед перетворенням таблиці у формат EXCEL 2007 переконайтеся, що вихідна таблиця правильно структурована. У статті Поради щодо побудови таблиць викладено основні вимоги до «правильної» структури таблиці.

Видалення таблиць у форматі EXCEL 2007

Щобвидалити таблицю разом з даними, потрібно виділити будь-який заголовок у таблиці, натиснутиCTRL+A, потім клавішуDELETE (любо виділіть будь-яку комірку з даними, двічі натиснітьCTRL+A, потім клавішуDELETE ). Інший спосіб видалити таблицю - видалити з аркуша всі рядки або стовпці, що містять осередки таблиці (видалити рядки можна, наприклад, виділивши потрібні рядки за заголовки, викликавши правою клавішею миші контекстне меню і вибравши пункт Видалити).

Щоб зберегти дані таблиці, можна перетворити її на звичайний діапазон. Для цього виділіть будь-яку комірку таблиці (Буде відображено вкладку Робота з таблицями, що містить вкладку Конструктор) та через меню Робота з таблицями/ Конструктор/ Сервіс/ Перетворити на діапазон перетворіть її на звичайний діапазон. Форматування таблиці залишиться. Якщо форматування також потрібно видалити, перед перетворенням у діапазон очистіть стиль таблиці ( Робота з таблицями/ Конструктор/ Стилі таблиць/ Очистити ).

Додавання нових рядків

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

Почнемо із заповнення зі стовпця Найменування (перший стовпець без формул). Після введення значення у таблиці автоматично додасться новий рядок.

Як видно з малюнку зверху, форматування таблиці автоматично пошириться на новий рядок. Також у рядок скопіюються формули у стовпцях Вартість та №. У стовпці Од.ізм. стане доступним Випадаючий список із переліком одиниць вимірів.

Для додавання нових рядків у середині таблиці виділіть будь-яку комірку в таблиці, над якою потрібно вставити новий рядок. Правою клавішею миші викличте контекстне меню, виберіть пункт Вставити (зі стрілочкою), потімпункт Рядки таблиці вище.

Видалення рядків

Виділіть одну або кілька осередків у рядках таблиці, які потрібно видалити. Клацніть правою кнопкою миші, виберіть у контекстному меню команду Видалити, а потім команду Рядки таблиці. Буде видалено лише рядок таблиці, а не весь рядок аркуша. Аналогічно можна видалити стовпці.

Дані підсумків у таблиці

Клацніть у будь-якому місці таблиці. На вкладці Конструктор у групі Параметри стилів таблиць встановіть прапорець Рядок результатів.

В останньому рядку таблиці з'явиться рядок підсумків, а в самій лівій комірці відображатиметься словоПідсумок.

У рядку підсумків клацніть комірку в стовпці, для якого потрібно розрахувати значення підсумку, а потім клацніть стрілку розкривного списку. У списку, що розкривається, виберіть функцію, яка буде використовуватися для розрахунку підсумкового значення. Формули, які можна використовувати у рядку підсумкових даних, не обмежуються формулами зі списку. Можна ввести будь-яку потрібну формулу в будь-якому осередку рядка підсумків. Після створення рядка підсумків додавання нових рядків до таблиці не може, т.к. рядки перестають додаватися автоматично при додаванні нових значень (див. розділ Додавання рядків). Але в цьому немає нічого страшного: результати можна вимкнути/включити через меню.

Надання імен таблицям

При створенні таблиць у форматі EXCEL 2007, EXCEL надає імена таблиць автоматично: Таблиця1, Таблиця2 і т.д., але ці імена можна змінити (через конструктор таблиць: Робота з таблицями/ Конструктор/ Властивості/ Ім'я таблиці ), щоб зробити їх більш виразними .

Ім'я таблиці неможливо видалити (наприклад, через Менеджер імен). Поки існує таблиця – буде визначено її ім'я.

Структуровані посилання (посилання на поля та значення таблиці у формулах)

Тепер створимо формулу, в якій як аргументи вказано один із стовпців таблиці у форматі EXCEL 2007 (формулу створимо поза рядком підсумки).

Введемо в коміркуH1 частина формули: = СУМ(
Мишкою виділимо діапазонF2:F4 (весь стовпець Вартість без заголовка)

Але, замість формули = СУМ (F2: F4 ми побачимо = СУМ (Таблиця1 [Вартість])

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

Розглянемо інший приклад підсумовування шпальти таблиці через її Ім'я. У осередкуH2 введемо =СУМ(Т (літера Т – перша буква імені таблиці). EXCEL запропонує вибрати, що починається на «Т», функцію або ім'я, визначене в цій книзі (у тому числі і імена таблиць) .

Двічі клацнувши на імені таблиці, формула набуде вигляду = СУМ (Таблиця 1 . Тепер введемо символ [ (відкриваючу квадратну дужку). EXCEL після введення = СУМ ( Таблиця 1) запропонує вибрати конкретне поле таблиці. Виберемо поле Вартість, двічі клікнувши на нього.

У формулу = СУМ (Таблиця 1 [Вартість введемо символ] (закриває квадратну дужку) і натиснемо клавішуENTER. У результаті отримаємо суму по стовпцю Вартість.

Нижче наведено інші види структурованих посилань: Посилання на заголовок стовпця: =Таблиця1[[#Заголовки];[Вартість]] Посилання на значення в тому ж рядку =Таблиця1[[#Ця рядок];[Вартість]]

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

Спочатку розрахуємо загальну вартість за допомогою формули = СУМ (Таблиця1 [Вартість]). Формулу складемо, як показано в попередньому розділі.

Тепер за допомогою Маркера заповнення скопіюємо формулу вправо, вона буде автоматично перетворена на формулу = СУМ (Таблиця1 [Вартість з ПДВ])

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

Стилі таблиць

Для таблиць, створених у форматі EXCEL 2007 ( Вставка/ Таблиці/ Таблиця ) існує можливість використовувати різні стилі для надання таблицям певного виду, у тому числі з надстроковим виділенням. Виділіть будь-яку комірку таблиці, далі натисніть Конструктор/Стилі таблиць та виберіть відповідний стиль.