Як правильно користуватися довідкою в Excel
У Office 2007 з'явився новий тип довідкової системи. Нова система (офіційно відома як Довідка: Excel) є частиною і Office 2010 та 2013. Один із компонентів довідкової системи — функція підказки. При підведенні вказівника миші до команди на стрічці ви побачите її опис (рис. 26.1).
Однак реальний вміст довідкової системи можна знайти у вікні Довідка: Excel. Нижче наведена інформація допоможе вам ознайомитися з довідковою системою Excel.
Мал. 26.1. Підказка описує кожну команду стрічки
- У системи Довідка: Excel є одна точка входу. Щоб відобразити довідкову систему, натисніть клавішу F1 або клацніть на піктограмі із зображенням питання, розташованому під рядком заголовка Excel.
- Вміст довідки зберігається у двох місцях: на жорсткому диску та на сервері Microsoft. У правому нижньому куті вікна відображається інформація про статус довідки: автономна робота (відображається вміст лише з комп'ютера) або встановлено підключення до сайту Office.com (показується вміст з Інтернету). Ви можете змінити статус, натиснувши кнопку і вибравши у меню, що відкрилося, відповідний пункт. У деяких випадках вибір підключення до сайту дозволить отримати більше інформації про навчання excel.
- Підключення до Office.com відкриває доступ до додаткового вмісту, який недоступний під час автономної роботи. Зокрема, там можна знайти шаблони або навчальні матеріали, а також отримати доступ до інформації для розробників. На рис. 26.2 показані варіанти пошуку, що надаються розкривним списком Пошук.
Мал. 26.2. Вказівка, де шукати допомоги
Мал. 26.3. Налаштування панелі інструментів довідки Excel
Довідник EXCEL
Довідник складається з двох таблиць: довідкової таблиці, у рядках якої містяться докладні записи про деякі об'єкти (співробітники, товари, банківські реквізити тощо) та таблиці, до якої заносяться дані пов'язані з цими об'єктами. Вказавши в осередку лише ключове слово, наприклад, прізвище співробітника чи код товару, можна вивести у суміжних осередках додаткову інформацію з довідкової таблиці. Іншими словами, структура Довідник знижує кількість ручного введення та зменшує кількість друкарських помилок.
Створимо Довідник на прикладі заповнення накладної.
У накладній вибиратимемо найменування товару, а ціна, одиниця виміру та ПДВ, будуть підставлятися в потрібні осередки автоматично з довідкової таблиці Товари , що містить перелік товарів із зазначенням, відповідно, ціни, одиниці виміру, ПДВ.
Таблиця Товари
Цю таблицю створимо на аркуші Товари з допомогою меню Вставка/ Таблиці/ Таблиця , тобто. у форматі EXCEL 2007 (див. файл прикладу). За умовчанням новій таблиці EXCEL надасть стандартне ім'я Таблиця1 . Змініть його на ім'я Товари , наприклад, через Диспетчер імен ( Формули/ Певні імена/ Диспетчер імен )
До таблиці Товари , Як до довідкової таблиці, пред'являється одна жорстка вимога: наявність поля з значеннями, що не повторюються. Це поле називається ключовим . У нашому випадку ключовим буде поле, що містить найменування Товару. Саме цим полем будуть вибиратися інші значення з довідкової таблиці для підстановки в накладну.
Для гарантованого забезпечення унікальності найменувань товарів використовуємо Перевірку даних (Дані/ Робота з даними/ Перевірка даних):
- виділимо діапазон А2: А9 на аркуші Товари ;
- викличемо Перевірку даних ;
- у полі Тип даних оберемо Інший і введемо формулу, що перевіряє значення, що вводиться на унікальність:
При створенні нових записів про товари (наприклад, в осередку
А10
), EXCEL автоматично скопіює правило Перевірки даних з комірки
А9
– у цьому виявляється одна перевага таблиць, створених у форматі Excel 2007, порівняно зі звичайними діапазонами осередків. Перевірка даних спрацьовує, якщо після введення значення в комірку натиснуто клавішу ENTER . Якщо значення скопійовано з Буфер обміну або скопійовано через Маркер заповнення, то Перевірка даних не спрацьовує, а лише позначає комірку маленьким зеленим трикутником у лівому верхньому кутку комірки.
Через меню Дані/ Робота з даними/ Перевірка даних/ Обвести невірні дані можна отримати інформацію про наявність даних, які були введені з порушенням вимог Перевірки даних .
Для контролю унікальності можна також використовувати Умовне форматування (див. статтю Виділення повторюваних значень ).
Тепер, створимо Іменований діапазон Список_Товарів, містить всі найменування товарів :
- виділіть діапазон А2: А9 ;
- Викличте меню Формули/ Певні імена/ Присвоїти ім'я
- у поле Ім'я введіть Список_Товарів ;
- переконайтеся, що в полі Діапазон введено формулу = Товари[Найменування]
- натисніть кнопку ОК.
Таблиця Накладна
До таблиці Накладна також пред'являється одна жорстка вимога: всі значення в стовпці (полі) Товар повинні утримуватися у ключовому полі таблиці Товари . Іншими словами, в накладну можна вводити ті товари, які є в довідковій таблиці Товарів інакше сенс створення Довідника пропадає. Для формування Випадаючого списку для введення назв товарів використовуємо Перевірку даних :
- виділіть діапазон C4:C14 ;
- викличте Перевірку даних ;
- у полі Тип даних виберіть Список;
- як формулу введіть посилання на раніше створений Іменований діапазон Список_товарів , тобто. = Список_Товарів.
Тепер товари в накладній можна буде вводити лише з таблиці Товари .
Тепер заповнимо формулами стовпці накладної од.ізм., Ціна і ПДВ . Для цього використовуємо функцію ВПР():
чи аналогічну їй формулу
Перевага цієї формули перед функцією ВПР() полягає в тому, що ключовий стовпець Найменування у таблиці Товари ні бути самим лівим у таблиці, як у разі використання ВПР() .
У стовпцях Ціна і ПДВ введіть відповідно формули: =ЯСЛИПОМИЛКА(ВПР(C4;Товари;3;БРЕХНЯ);"") =ЕСЛИПОМИЛКА(ВПР(C4;Товари;4;БРЕХНЯ);"")
Тепер у накладній при виборі найменування товару автоматично будуть підставлятися його одиниця виміру, ціна та ПДВ.