Що можна вміти робити в Екселі?

Таблиці Excel – дуже потужний інструмент. Вони більше 470 прихованих функцій. Спочатку це лякає: здається, на те, щоб розібратися з усім, підуть роки. Насправді, це не так. Усього десятка функцій та гарячих клавіш уже вистачить для того, щоб сильно спростити собі життя. Розкажемо про деякі з них (скоро стартує другий потік курсу "Магія Excel").

Інтерфейс

Налаштовуємо панель швидкого доступу

Почнемо з найпростішого — додавання опцій, що найчастіше використовуються, на панель швидкого доступу. Щоб зробити це, заходьте до параметрів Excel — «Налаштувати стрічку» — та шукайте параметри «Панель швидкого доступу».

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

Інший варіант — просто клацнути інструментом на стрічці правою кнопкою миші і натиснути «Додати…»:

Переміщаємося стрічкою без мишки

Натисніть Alt. На стрічці інструментів з'явилися цифри та літери — у кожного інструмента на панелі швидкого доступу та у кожної вкладки на стрічці відповідно:

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

Введення даних

Тепер розглянемо кілька інструментів для швидкого введення даних.

Автозаміна

Якщо вам часто потрібно вводити якесь словосполучення, адресу, емейл і так далі - придумайте для нього коротке позначення та додайте до списку автозаміни в параметрах:

Прогресія

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

Просування

Уявіть, що вам потрібно витягти якісь дані з цілого стовпця або переписати їх в іншому вигляді (наприклад, прізвище з ініціалами замість повних ПІБ). Задайте Excel один осередок із зразком - що хочете отримати:

Виділіть всі комірки, які хочете заповнити за зразком, і натисніть Ctrl+E. І магія станеться (ну, як правило).

Перевірка помилок

Перевірка даних дозволяє уникнути помилок під час введення інформації в комірки.

Які бувають типові помилки в Excel?

• Текст замість чисел
• Негативні числа там, де їх бути не може
• Числа з дробовиною там, де мають бути цілі
• Текст замість дати
• Різні варіанти написання одного й того самого значення. Наприклад, скорочення («ЕБ» замість «Електронна бібліотека»), зайві прогалини в кінці текстового значення або між словами — цього достатньо, щоб перетворити текстові значення на різні і, відповідно, щоб вони оброблялися Excel некоректно.

Інструмент перевірки даних

Щоб використати інструмент перевірки даних, потрібно виділити комірки, до яких хочете його застосувати, вибрати на стрічці «Дані» → «Перевірка даних» та налаштувати параметри перевірки у діалоговому вікні:

Якщо у графі «Повідомлення про помилку» ви вибрали варіант «Зупинка», то після перевірки до осередків не можна буде ввести значення, які не відповідають заданому правилу.

Якщо ви вибрали "Попередження" або "Повідомлення", то при спробі ввести невірні дані буде з'являтися попередження, але його можна буде проігнорувати і все одно ввести будь-що.

Ще неправильні дані можна обвести, щоб побачити, де є помилки:

Видалення прогалин

Для видалення зайвих прогалин (на початку, наприкінці та всіх крім одного між слів) використовуйте функцію СЖПРОБЕЛИ/TRIM. Її єдиний аргумент - текст (посилання на комірку з текстом, як правило).

Якщо після очищення даних функцією СЖПРОБЕЛИ або іншої обробки вам не потрібний вихідний стовпець, вставте дані, отримані в окремому стовпці за допомогою функцій, як значення на місце вихідних даних, а стовпець з формулою видаліть:

Дата та час

За будь-якою датою в Excel ховається ціле число. Датою його робить формат.

Аналогічно з часом: одна одиниця – це день, а частина одиниці (число від 0 до 1) – час, тобто частина дня.

Це не означає, що так має сенс вводити дати і час у осередки, вводьте їх у будь-якому зі стандартних форматів — Excel одразу відформатує їх як дати:

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

Додати до дати число і отримати дату, яка настане через відповідну кількість днів.

Пошук та підстановка значень

Функція ВПР/VLOOKUP

Функція ВПР/VLOOKUP (вертикальний перегляд) потрібна, щоб зв'язати кілька таблиць – «підтягнути» дані з однієї в іншу за якимось ключем (наприклад, назвою товару чи бренду, прізвища співробітника чи клієнта, номер транзакції).

=ВПР (що шукаємо; таблиця з даними, де «що шукаємо» має бути в першому стовпці; номер стовпця таблиці, з якого потрібні дані; [інтервальний перегляд])

Вона має два режими роботи: інтервальний перегляд і точний пошук.

Інтервальний перегляд - це пошук інтервалу, в який потрапляє число.Якщо у вас прогресивна шкала податку або знижок, потрібно конвертувати оцінку з однієї системи в іншу і таке інше — використовується саме цей режим. Для інтервального перегляду потрібно пропустити останній аргумент ВПР або задати його рівним одиниці (або ІСТИНА).

У більшості випадків ми пов'язуємо таблиці за текстовими ключами — у такому разі потрібно обов'язково явно вказувати останній аргумент «інтервальний_перегляд» рівним нулю (або БРЕХНЯ). Тільки тоді функція коректно працюватиме з текстовими значеннями.

Функції ПОШУКПОЗ / MATCH та ІНДЕКС / INDEX

У ВПР є істотний недолік: ключ (потрібне значення) може бути у першому стовпці таблиці з даними. Все, що лівіше за цей стовпчик, через ВПР «підтягнути» неможливо.

У реальних умовах структура таблиць буває різною і не завжди можна змінити порядок стовпців. Тому важливо вміти працювати з будь-якою структурою.

Функція ПОШУКПОЗ/MATCH визначає порядковий номер значення в діапазоні. Її синтаксис:

=ПОШУКПОЗ (що шукаємо; де шукаємо ; 0)

На виході - число (номер рядка або стовпця в межах діапазону, в якому знаходиться значення).

ІНДЕКС / INDEX виконує інше завдання - повертає елемент за його номером.

= ІНДЕКС (діапазон, з якого потрібні дані; порядковий номер елемента)

Відповідно, ми можемо визначити номер рядка, в якому знаходиться потрібне значення, за допомогою ПОШУКПОЗ. А потім підставити цей номер до ІНДЕКСу на місце другого аргументу, щоб отримати дані з будь-якого потрібного нам стовпця.

Виходить наступна конструкція:

= ІНДЕКС (діапазон, з якого потрібні дані; ПОШУКПОЗ (що шукаємо; де шукаємо ; 0))

Оформлення

Потрібно оформити комірки у книзі Excel у єдиному стилі? Для цього є однойменний інструмент – «Стилі».

На стрічці інструментів натисніть на «Стилі осередків» та виберіть відповідний. Він буде застосований до виділених осередків:

А найголовніше — якщо ви застосували стиль до багатьох осередків (наприклад, до всіх заголовків на 20 аркушах книги Excel) і захотіли переробити щось, клацніть правою кнопкою миші і натисніть «Змінити». Зміни будуть застосовані до всіх потрібних осередків у документі.

На курсі «Магія Excel» буде два модулі – для новачків та просунутих. Записуйтесь →

Фішки Екселя: можливості та корисні функції для роботи в Excel

Практично кожен знає, що в Microsoft Excel роблять звіти та обчислення бухгалтери, аналітики та дослідники у різних галузях науки та маркетингу, ним користуються студенти та досвідчені фахівці. Робота в ній може спершу здатися складною для розуміння, проте при уважному підході з найкориснішими функціями зуміє розібратися навіть початківець: у цій статті ми розглянемо цікаві фішки та можливості програми Екселя з прикладами, освоїмо секрети роботи з великими даними та покроково вивчимо трюки та хитрості, якими зможе користуватися навіть новачок.

• Створення таблиці у Microsoft Excel
• Основні елементи редагування
• Використання функцій Excel
  • Як зробити «ВПР»
  • Зведені таблиці
  • Створення діаграм
  • Формули в Excel - найкорисніші функції та цікаві фішки
  • Функція «ЯКЩО»
  • Макроси
  • Умовне форматування
  • Функція ПЛТ
  • Вибір параметра
  • Формула «ІНДЕКС»
  • Використання «Захисту осередків»
  • Закріплення заголовків рядків та стовпців
  • Використання «Спеціальної вставки» для транспонування
  • Використання «Миттєвого заповнення»

  Створення таблиці у Microsoft Excel

  При запуску програми нам запропонують створити нову книгу – так називають усі файли, які мають розширення .xlsx чи .xls.

  Далі відкриється порожній лист із сітчастою розміткою: горизонтальна шкала позначена цифрами (від 1 до 1048576), а вертикальна – латинськими літерами (від A до XFD).

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

  Якщо у вас вже є набір даних, перетворити їх на потрібне значення буде не важко. Достатньо виділити будь-яку область та вибрати на панелі вкладку «Головна» → «Стилі» → «Форматувати як таблицю» або застосувати гарячі клавіші Ctrl+T.

  Щоб створити класичну УТ з нуля, відкриваємо на верхній стрічці розділ «Вставка» та вибираємо відповідний інструмент. Затискаємо лівий курсор миші та розтягуємо пунктирну рамку. У діалоговому вікні вам запропонують підтвердити створення елемента в певному місці, яке буде позначено через координати верхньої лівої комірки та нижньої правої.

  Редактура зовнішніх параметрів здійснюється через «Конструктор», там знаходимо пакет готових стилів для оформлення.

  Зауважимо, що якщо вас цікавить створення не розумної сітки, а звичайної, у програмі Ексель це можна зробити теж швидким і зручним способом:

  • затискаємо ЛК миші;
  • виділяємо необхідну область;
  • викликаємо контекстне меню правою кнопкою;
  • вибираємо пункт «Формат осередків»;
  • у вкладці «Кордони» натискаємо на «Зовнішні» та «Внутрішні».

  Основні елементи редагування

  Багато опцій із робочої панелі повторюють текстовий редактор із пакета Microsoft Office. Вони винесені на верхню стрічку і згруповані за призначенням і розташовуються в тематичних закладках, внизу є підписи-підказки.

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

  Праворуч від поля імені знаходиться рядок функції (fx), куди вводяться формули для розрахунків.

  Внизу відображаються ярлики аркушів, які створюються за замовчуванням у кількості від 1 до 3, залежно від версії програми. Нові сторінки додаються за допомогою піктограми праворуч на нижній панелі.

  Контент-маркетинг у соціальних мережах від студії SEMANTICA – комплексний метод взаємодії та вибудовування довірчих відносин із вашою цільовою аудиторією. Розробимо стратегію, складемо та реалізуємо контент-план, адмініструватимемо аккаунт і запустимо таргетовану рекламу для залучення потенційних клієнтів.

  Використання функцій Excel

  Робити табличні звіти можна у багатьох редакторах, проте програма Excel вигідно відрізняється тим, що має багато цікавих розширених можливостей та фішок. Вона має велику кількість готових обчислювальних виразів, які викликаються двома способами: через інструмент «Вставити функцію» у розділі «Формули» або при натисканні на «fx». Нижче ми розглянемо кілька фішок, які сильно спростять вам підрахунки і аналітику і збережуть вашу нервову систему.

  Як зробити «ВПР»

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

  Для початку додамо до першої таблички стовпчики для категорій Ціна та Вартість. Після цього виділяємо верхню комірку стовпця, куди слід перенести відомості (у нас це C2), клацаємо на значок fx і вибираємо ВПР. Вискочить вікно заповнення аргументів, а поруч із fx з'явиться =ВПР().

  • У «Шукане значення» вводимо діапазон, яким буде проводитися пошук у прайсі. У нас це найменування іграшок, тобто A2: A6.
  • У наступній графі вказуємо програму, де знаходиться джерело, з якого підтягуватиметься інформація (у нашому випадку E2:F6), і фіксуємо це посилання значками $, щоб вираз коректно спрацював для всіх рядків і дані не зміщувалися. Вийде $E$2:$F$6
  • Далі пишемо номер стовпчика у прайсі. У прикладі це 2.
  • В останньому пункті вказуємо «БРЕХНЯ» або 0, оскільки нам потрібні точні, а не наближені до якогось числа чи дати значення.

  Клацаємо «OK» і розповсюджуємо правило для всіх інших елементів, потягнувши за хрестик у нижньому правому кутку. В результаті Ексель переніс усі ціни з одного списку до іншого, незважаючи на те, що предмети в них йшли по-різному. Готово, ви чудові. Тепер, якщо в прайсі зміниться ціна, це також автоматично відбудеться і в іншому зведенні.

  Зведені таблиці

  Дозволяють швидко перетворити масу сирої інформації на готові звіти. Активуємо будь-яку ділянку у джерелі, клацаємо на вкладку «Вставка» та вибираємо «Зведена таблиця» (СТ). У віконце, що з'явилося, вписуємо проміжок або назву джерела і вирішуємо, чи буде цей елемент розташовуватися на новій сторінці книги.

  Далі праворуч екрана відкриється поле для роботи з заголовками СТ. У верхній частині ми бачимо список доступних варіантів, які відповідають джерелу. Кожен із них можна перетягнути. Наприклад, якщо ми візьмемо "Регіон" і додамо його в область для назви стовпців, а в поле значення поставимо "Виручку", у нас автоматично сформується невеликий звіт щодо прибутку в кожній локації.

  А так це буде виглядати, якщо перетягнути "Регіон" в область для рядків.

  Редагувати СТ потрібно через спеціальний пункт «числовий формат», щоб зміни стосувалися всієї інформації.

  Створення діаграм

  При складанні звітів часто потрібно показати динаміку даних або їхню структуру наочно.

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

  Для побудови будь-якої з них достатньо виділити таблицю-джерело, у вкладці «Вставка» знайти розділ «Діаграми» та вибрати потрібну піктограму.

  При виборі відкриється список з варіантами шаблонів.

  Після цього діаграма одразу з'явиться поряд. Редагування стилів і структури відбувається досить інтуїтивно через «Конструктор» та «Формат» або на кліку правої кнопки миші прямо на макеті. Також графік можна перенести на окремий аркуш. За таким же принципом працюємо з рештою графічних макетів.

  Зміст діаграми налаштовується через інструмент «Вибрати дані» у контекстному меню.

  Формули в Excel - найкорисніші функції та цікаві фішки

  Інформація Ексель вноситься вручну або підставляється автоматично залежно від застосовуваного правила. Розглянемо кілька найбільш затребуваних із них.

  • МАКС і МІН – перша знаходить найбільше в діапазоні, а друга – найменше. Синтаксис: = МАКС/МІН (координати осередків).
  • СРЗНАЧ – складає всі виділені числа і поділяє результат з їхньої кількість. Синтаксис: = СРЗНАЧ(координати).
  • РАХУНОК - допоможе підрахувати в обраному проміжку кількість числових значень. Синтаксис: = РАХУНОК (координати елементів).

  Функція «ЯКЩО»

  Тут йдеться вже не про найпростіші обчислення, а перевірку на дотримання певних умов. Якщо вони виконуються, Ексель сприймає вміст осередку як істину, а якщо ні, то як брехня. Нам необхідно прописати не лише умову, а й те, які дані видавати у кожному зі сценаріїв. Синтаксис такий: =ЯКЩО(лог_вираз; [значення_якщо_істина]; [значення_якщо_брехня]).

  Наприклад, премія співробітнику видається лише у разі виконання плану продажу та становить 5000 рублів. У рядок "лог_вираз" ми введемо умову, у нас це E5

  Макроси

  Екселе має вбудований макрорекордер, який може одноразово записати те, що ви робите так, щоб ви змогли застосовувати послідовність дій скільки завгодно разів.

  Зазвичай макроси приховані за замовчуванням, тому правою кнопкою миші клацаємо по стрічці та вибираємо "Налаштування панелі швидкого доступу". У вікні переносимо з лівого стовпчика пункт «Макроси» або «Розробник» у деяких версіях.

  Після цього на стрічці з'явиться відповідна закладка або розділ у категорії «Вигляд». Тепер вирішимо, який алгоритм слід автоматизувати.

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

  Починаємо запис макросу.Спочатку вам запропонують запровадити ім'я макрокоманди, налаштувати для неї гарячі клавіші та придумати опис.

  Після того, як ми розпочали запис, виконуємо потрібні дії, допомагаємо собі формулами, які вже розглянули вище.

  Коли все виконано, натискаємо на «Зупинити запис». Тепер, навіть якщо ми видалимо наш новий стовпець, програма автоматично відтворить всі записані дії, для цього достатньо в розділі макросами вибрати той, що називається «Обчислити вартість». А якщо хочете користуватися макрокомандою в різних документах, збережіть її на етапі створення особистої книги макросів.

  Умовне форматування

  Ексель дозволяє автоматично перетворити зовнішній вигляд елементів залежно від їхньої відповідності заданим умовам. Правила можна створювати і самостійно, але для простих сценаріїв у Excel існують зручні та ефективні шаблони. У вкладці «Головна» знаходимо розділ зі стилями «Умовне форматування». При натисканні випадає меню із готовими варіантами.

  Наприклад, нам потрібно виділити в стовпці з вартістю іграшок ті ціни, які перевищують 3000. Для цього ми вибираємо актуальний проміжок та серед правил клацаємо на «Більше…». У діалоговому вікні вписуємо граничне значення та вибираємо колір для оформлення.

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

  Функція ПЛТ

  З її допомогою можна швидко розрахувати найпростіші кредитні завдання. Розглянемо з прикладу: Віка планує взяти у банку 300 000 рублів і повернути в протягом 2 років. При цьому кредитна ставка на цю суму складає 7% річних.Щоб з'ясувати, скільки доведеться платити банку, щоб укластися в строк, складемо наочну таблицю.

  Заходимо в «Формули» або пишемо вручну ПЛТ().

  • для «Ставки» координати річного відсотка та ділимо на 12 місяців;
  • для "Кріп" термін виплати;
  • для "ПС" загальну суму;
  • для «БС» нуль або БРЕХНЯ.

  Підсумкове число має вийти негативним. Тут можна дізнатися, яка переплата буде за кредитом за таких умов. Для цього достатньо перемножити внесок, що вийшов, і значення для «Креп», а потім додати розмір кредиту. У нашому випадку вираз виглядатиме ось так: = ПЛТ (D5/12; D6; D7; 0) * D6 + D7. Переплата при щомісячному внеску в 13432 рубля складе 22363.

  Вибір параметра

  Він входить до блоку «Аналіз “що якщо”» у вкладці «Дані» та використовується для пошуку невідомої, яку потрібно ввести в одиночну формулу, щоб отримати бажаний (відомий) результат.

  Розглянемо, як це працює на класичному прикладі. Розрахуємо процентну ставку, якщо ми знаємо розмір кредиту (2 мільйони) та термін виплати (2 роки).

  У рядок із щомісячним платежем вставляємо ПЛТ, про яку розповідали вище. Виходить, що за нульової ставки слід протягом двох років виплачувати щомісяця майже 84 тисячі. Але світ не є ідеальним, тому банк вимагає оплату в розмірі не нижче 90 000. Щоб з'ясувати відсоток, вибираємо одну з трьох функцій аналізу «Підбір параметра». У першому полі вікна, що відкрилося, вводимо координати місця, де знаходиться формула для розрахунку, у нас це C5. У другому вказуємо суму, яку забиратиме банк (не забудьте про мінус). А в третьому даємо координати, де має бути ставка.

  Ексель дав нам результат 7,5%.Це означає, що при цьому значенні та щомісячному внеску 90 тисяч за два роки можна виплатити кредит у два мільйони.

  Формула «ІНДЕКС»

  Це класний спосіб швидко знаходити дані з їх координат. У базовому вигляді тут лише два аргументи: діапазон та порядковий номер у ньому. Наприклад, у нас є ТОП найпопулярніших пісень на радіо, а нас просять знайти, яка з них займає 5 рядок. Викликаємо функцію і в першій діалоговій рамці вибираємо перший пункт.

  Для аргументу «масив» виділяємо досліджуваний проміжок, у нас це D3: D12, вписуємо номер рядка, що шукається, для стовпця в нашому випадку вказуємо 0 або БРЕХНЯ.

  Якщо ж ми, навпаки, хочемо з'ясувати, на якому місці ТОП знаходиться, наприклад, пісня Фелліні, то додається ще одна формула - ПОШУКПОЗ. Для цього починаємо знову з ІНДЕКСУ, тільки як масив пошуку вказуємо стовпець з місцями в чарті.

  • потрібне значення (у нашому випадку Фелліні);
  • масив, в якому воно знаходиться ( стовпчик з піснями);
  • нуль або БРЕХНЯ для точного типу зіставлення.

  Використання «Захисту осередків»

  Іноді на заповнення документа йде багато часу і сил, буде прикро, якщо ваша кішка випадково щось натисне і все зникне. Є кілька способів це запобігти.

  • За допомогою пункту «Формат комірок» у контекстному меню натисніть правою кнопкою миші. Відкриється вікно, де потрібно вибрати вкладку «Захист». Там бачимо підказку, як активувати інструмент.

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

  Тепер при спробі змінити щось, програма видасть таке повідомлення.

  • Якщо потрібний захист не всієї сторінки, а тільки якоїсь ділянки, то виділяємо її і натискаємо в тій же вкладці «Дозволити зміну діапазонів» і вибираємо «Створити» у вікні, що з'явилося.

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

  Проведемо повний, комплексний SEO аудит сайту, включаючи: технічну перевірку, оптимізацію, комерційні фактори, зовнішні характеристики. Жодної води у звіті! Тільки опис існуючих проблем та їх ефективних рішень.

  Закріплення заголовків рядків та стовпців

  При роботі з великою таблицею її верхні частини прокручуються за межі екрана. Добре, що Excel можна зробити простий лайфхак, який дозволить цього уникнути. Щоб верхня частина стала шапкою нашого документа, клацаємо на стрічці «Вид», вибираємо інструмент «Закріпити область» та пункт, що вам підходить.

  Той самий принцип працює і по горизонталі: при прокручуванні праворуч, у нас є можливість постійно бачити перший стовпець. Окей, а що робити, якщо актуальні елементи не знаходяться у верхніх рядках? Тут нам знадобиться пункт "Закріпити області".

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

  Використання «Спеціальної вставки» для транспонування

  Буває так, що потрібно поміняти місцями заголовки по горизонталі та вертикалі.Щоб не переписувати все вручну, Ексель має зручний лайфхак, який дозволить це автоматизувати.

  • Спочатку виділіть вашу таблицю та скопіюйте через контекстне меню або гарячі клавіші Ctrl+C.
  • Далі визначте на аркуші місце, куди буде розміщено транспонований об'єкт. Виділіть його.
  • Викличте контекстне меню та виберіть «Спеціальна вставка».
  • Позначте галочкою "транспонувати".
  • У нових версіях транспонування зображено у вигляді піктограми, як вказано на малюнку нижче.

  Ось, як у результаті заголовки змінилися місцями.

  Використання «Миттєвого заповнення»

  Ця функція зустрічається у версіях 2013 року та пізніше.

  • витягувати з масивів тексту потрібні слова чи цифри;
  • переводити слова-числові в числа;
  • перетворює дати на UNIX формат;
  • міняти частини тексту місцями;
  • виправляти регістр та формат тощо.

  Подивимося, як це працює практично. Наприклад, у нас є дві колонки з текстами, які через дефіс потрібно поєднати у третій. Потрібно ввести два-три приклади вручну, щоб програма здогадалася, чого ми від неї хочемо і підсвітила ці варіанти сірим. Також миттєве заповнення є на стрічці інструментів або через гарячі клавіші Ctrl + E.

  Це можна зробити і за допомогою формули, але так виходить набагато швидше. Не важливо, чи будете ви вносити дані в стовпець праворуч або ліворуч, головне, щоб не було відступів, так Ексель зуміє зрозуміти патерн дій користувача.

  Елементи розмітки сторінки

  Змінити формат документа можна в один клік. Для цього знаходимо у правому нижньому кутку іконки біля повзунка з масштабом.

  Крайня ліворуч активує звичне робоче поле сервісу, інші – посторінкові версії для друку.Переключитися між ними можна також за допомогою вкладки Вигляд.

  Ці інструменти допомагають відформатувати вигляд документа, який ви хочете роздрукувати: ширину відступів, наявність підкладки, розмір листа, примусові розриви сторінок.

  Перемикання між таблицями

  Часто документи містять десятки сторінок, серед яких користувач змушений навчитися орієнтуватися. Існує три способи переміщатися між ними. Розглянемо кожен із них, щоб ви вибрали найбільш зручний.

  • Варіант номер один – гарячі клавіші Ctrl+Page Up/Page Down. При натисканні цих комбінацій відбувається перегортання однією сторінку вперед чи назад. Спосіб підходить для невеликих книг. Для об'ємних документів підійдуть наступні два лайфхаки.
  • Перемикання за допомогою смуги прокручування. У нижній частині робочого простору ліворуч від ярликів з номерами сторінок знаходяться стрілки. Якщо натиснути на них правою кнопкою миші, відкриється зміст, яким можна швидко переміщатися.
  • І третій спосіб - створення гіперпосилань, які ведуть з одного аркуша на інший. У цьому нам допоможуть описані вище ІНДЕКС і ПОШУКПОЗ. Наприклад, у нас є сітка на одній сторінці та табличка до неї на іншій.

  Щоб виставити правильно гіперпосилання необхідно:

  • Додати стовпець на першій сторінці і ввести туди = ІНДЕКС (діапазон, з якого витягуватимемо дані). У нас це перший стовпчик.
  • Тепер необхідно обчислити порядковий номер осередку в цьому стовпці і для цього нам знадобиться ПОШУКПОЗ, в якому всього три аргументи: що шукаємо, де і наскільки ретельно. У нашому випадку знайти потрібну назву продукту, в першій колонці збіг точний. Вийде такий вираз.
  • Щоб перетворити текст, що вийшов, у посилання на табличку, загортаємо наше правило в функцію осередку і вибираємо адресу в списку. Не забудьте закрити дужку наприкінці.
  • Замість назв тепер стоятимуть вказівки координат. Наводимо їх до звичного вигляду за допомогою формули гіперпосилання. У неї два аргументи: адреса, яка у нас вже є, та назва. У нас буде «подивитися у зведеній».
  • Щоб Ексель коректно зрозумів усі вирази, перед осередком додаємо “#” і знак склеювання &.
  • Тепер, коли ми клацнемо за посиланням, нас автоматично перекине в потрібний рядок на відповідній сторінці книги.

  Застосування вікна контрольного значення

  Редагування однієї таблиці призводить до змін до іншої, пов'язаної з нею. Якщо вони знаходяться на різних аркушах або навіть книгах, користувач може бути незручно постійно перемикатися між ними, щоб контролювати ключові показники.

  • Виберіть діапазон, який бажаєте спостерігати.
  • На стрічці знайдіть вкладку «Формули» та клацніть на піктограму з підписом «Вікно контрольного значення».
  • Тепер цей елемент постійно перебуває у полі зору поверх робочого простору.

  Висновок

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