Як використовувати довідкові функції в Excel 2016

Довідкові функції Excel 2016 у розкривному списку командної кнопки «Пошук і посилання» на вкладці «Формули» на стрічці призначені для роботи з різними аспектами посилань на клітинки на аркуші. Ця група функцій включає:

• ADDRESS, щоб повернути посилання на клітинку як текстовий запис у клітинці аркуша

• AREAS, щоб повернути кількість областей у списку значень ( області визначаються як діапазон суміжних комірок або одна клітинка посилання на клітинку)

• COLUMN, щоб повернути число, що представляє позицію стовпця посилання на клітинку

• COLUMNS, щоб повернути кількість стовпців у посиланні

• FORMULATEXT, щоб повернути формулу, на яку посилаються як текстовий рядок

• GETPIVOTDATA для повернення даних, збережених у зведеній таблиці Excel

• ГІПЕРПОСИЛАННЯ, щоб створити посилання, яке відкриває інший документ, що зберігається на вашому комп’ютері, мережі або в Інтернеті (це також можна зробити за допомогою команди Вставити→Гіперпосилання)

• НЕПРЯМИЙ, щоб повернути посилання на клітинку, визначене текстовим рядком, і перенести вміст клітинки, на яку воно посилається на цю клітинку

• LOOKUP для повернення значення з масиву

• OFFSET, щоб повернути посилання на діапазон комірок, визначений кількістю рядків і стовпців із клітинки або діапазону комірок

• ROW, щоб повернути номер рядка посилання на клітинку

• ROWS, щоб повернути кількість рядків у діапазоні комірок або масиві

• RTD для повернення даних у реальному часі із сервера, на якому запущена програма, яка підтримує автоматизацію COM (Component Object Model)

• TRANSPOSE, щоб повернути вертикальний масив як горизонтальний і навпаки

Отримайте худі по стовпцях і рядках

Функції COLUMNS і ROWS повертають кількість стовпців і рядків у певному діапазоні комірок або масиві. Наприклад, якщо у вас є діапазон комірок у електронній таблиці з назвою product_mix, ви можете дізнатися, скільки стовпців він містить, ввівши цю формулу:

=COLUMNS(суміш_продуктів)

Якщо ви хочете дізнатися, скільки рядків використовує цей діапазон, введіть цю формулу:

=ROWS(суміш_продуктів)

Ви можете використовувати разом функції COLUMNS і ROWS для обчислення загальної кількості комірок у певному діапазоні. Наприклад, якщо ви хочете дізнатися точну кількість комірок, що використовуються в діапазоні комірок product_mix, ви створюєте таку просту формулу множення за допомогою функцій COLUMNS і ROWS:

=COLUMNS(суміш_продукту)*РОВКИ(суміш_продуктів)

Не плутайте функцію COLUMNS (множина) з функцією COLUMN (однина), а функцію ROWS (множина) з функцією ROW (однина). Функція COLUMN повертає номер стовпця (як ніби Excel використовує систему посилань R1C1) для посилання на клітинку, яке ви вказуєте як єдиний аргумент. Аналогічно, функція ROW повертає номер рядка для посилання на клітинку, який ви вказуєте як його аргумент.

Транспонування діапазонів клітинок

Функція TRANSPOSE дозволяє змінити орієнтацію діапазону комірок (або масиву). Ви можете використовувати цю функцію для транспонування вертикального діапазону комірок, де дані йдуть по рядках суміжних стовпців, у такий, де дані проходять через стовпці сусідніх рядків, і навпаки. Щоб успішно використовувати функцію TRANSPOSE, потрібно не тільки вибрати діапазон, який має протилежну кількість стовпців і рядків, а й ввести його як формулу масиву.

Наприклад, якщо ви використовуєте функцію TRANSPOSE для транспонування діапазону клітинок 2 x 5 (тобто діапазону, який займає два суміжні рядки і п’ять суміжних стовпців), ви повинні вибрати порожній діапазон клітинок 5 x 2 (тобто , діапазон, який займає п’ять суміжних рядків і два суміжні стовпці) на аркуші, перш ніж використовувати кнопку «Вставити функцію», щоб вставити функцію TRANSPOSE у першу клітинку. Потім, вибравши діапазон клітинок 2 x 5, який містить дані, які потрібно транспонувати в текстовому полі Масив діалогового вікна Аргументи функції, потрібно натиснути Ctrl+Shift+Enter, щоб закрити це діалогове вікно та ввести функцію TRANSPOSE у весь вибраний діапазон комірок у вигляді формули масиву (взято в фігурні дужки).

Припустимо, що ви хочете транспонувати дані, введені в діапазон комірок A10:C11 (масив 2 x 3), у порожній діапазон комірок E10: F12 (масив 3 x 2) робочого аркуша. Коли ви натискаєте Ctrl+Shift+Enter, щоб завершити формулу масиву, після вибору діапазону комірок A10:C11 як аргументу масиву , Excel поміщає таку формулу масиву в кожну клітинку діапазону:

{=TRANSPOSE(A10:C11)}

Наступний малюнок ілюструє використання функції TRANSPOSE. Діапазон комірок B2:C4 містить масив 3 x 2. Щоб перетворити цей масив 3 x 2 в діапазоні комірок B2:C4 в масив 2 x 3 в діапазоні B6:D7, виконайте такі дії:

Виберіть порожній діапазон комірок B6:D7 на аркуші.

Клацніть командну кнопку «Пошук і посилання» на вкладці «Формули» стрічки, а потім виберіть параметр TRANSPOSE у спадному меню кнопки.

Excel вставляє =TRANSPOSE() на рядок формул і відкриває діалогове вікно Аргументи функції, де вибрано текстове поле аргументу масиву.

Перетягніть діапазон комірок B2:C4 на аркуші так, щоб текстове поле аргументу масиву містило B2:C4, а формула на рядку формул тепер має вигляд =TRANSPOSE(B2:C4).

Натисніть Ctrl+Shift+Enter, щоб закрити діалогове вікно Вставити аргументи (не натискайте OK) і вставити формулу масиву TRANSPOSE у діапазон комірок B6:D7, як показано тут.

Використання функції TRANSPOSE для зміни орієнтації простого масиву.

Натискання кнопки «ОК» у діалоговому вікні «Аргументи функції» вставляє функцію TRANSPOSE в активну клітинку поточного виділення клітинки. Це повертає #VALUE! значення помилки в комірку. Не забудьте натиснути Ctrl+Shift+Enter, щоб закрити діалогове вікно та помістити формулу у весь діапазон комірок.

Якщо все, що вам потрібно зробити, — це транспонувати заголовки рядків і стовпців або просту таблицю даних, вам не доведеться стикатися зі складністю створення формули масиву за допомогою функції TRANSPOSE. Просто скопіюйте діапазон клітинок, які потрібно транспонувати, за допомогою командної кнопки «Копіювати» на вкладці «Головна» стрічки. Помістіть курсор комірки в першу порожню клітинку, куди потрібно вставити транспонований діапазон, перш ніж натиснути параметр Транспонувати в спадному меню командної кнопки Вставити.