Основні поняття Excel і пошук даних за допомогою функції ВПР

Табличний редактор Microsoft Excel (або просто Excel, а по-російськи Ексель) створений і використовується для роботи з таблицями. З таблицями можуть працювати і інші редактори, наприклад, текстовий редактор Microsoft Word (або просто Word, а по-російськи Ворд).
Однак саме в екселя закладені найширші можливості по обробці табличних даних.

4.
5.
6.

7.
8.
9.

10.
11.
12.

Лист і таблиця (и) на аркуші в Excel

Порівнюючи між собою текстовий редактор Word і табличний редактор Excel, раніше вже було розглянуто питання, як виглядають таблиці в ворде і в екселя. У редакторі Word таблиці вставляють в текст, задають в них кількість рядків і стовпців, і потім заповнюють клітини цих таблиць. Виходить красиво і наочно.

Після запуску редактора Excel відкривається так званий «лист», який спочатку виглядає як таблиця з фактично нескінченним кількістю стовпців і рядків (рис. 1).

Таблиця – це шматочок листа Excel. Уявіть собі великий аркуш міліметрівки. За аналогією з Ексель це буде просто лист. На аркуші міліметрівки можна накреслити одну таблицю або кілька таблиць. Також і на аркуші Excel може бути одна-єдина таблиця або кілька.

Мал. 1. Чистий аркуш табличного редактора Excel з курсором, поставленим на осередок B2.

Нумерація рядків і стовпців в таблиці Excel

Стовпці книги позначаються латинськими (англійськими) літерами від A до Z. Потім букви подвоюються від AA до ZZ, після чого потроюється від AAA до ZZZ і так далі (практично, до нескінченності).

Рядки таблиці позначаються цифрами від 1 і теж, практично, до нескінченності.

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

Що таке осередок і що таке таблиця Excel

Дані поміщаються в клітини листа, іменовані осередками. Кожна клітинка (в просторіччі, клітина) має своє унікальне ім’я. На рис. 1 курсор табличного редактора Ексель стоїть на осередку B2 (по вертикалі стовпець, що позначається латинською буквою B, а по горизонталі – це рядок з номером 2).

Так що ж тоді «таблиця» в екселя, якщо «лист» вже сам по собі є таблиця?

«Таблиці» в Microsoft Excel називають будь-яку область «листа», що представляє собою прямокутник з «осередків», обмежений зверху, знизу, праворуч і ліворуч. Будь-яка подібна область «листа» є «таблиця».

Наприклад, виділимо область на «листі» (рис. 1), провівши мишкою між осередками B2 і F3 при безперервно натиснутій лівій кнопці мишки. З’явиться виділена область, в яку входять клітини B2, C2, D2, E2, F2, B3, C3, D3, E3, F3 (рис. 2).

Мал. 2. Виділена область на аркуші табличного редактора Excel між осередками B2 і F3.

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

Як виділити таблицю в Excel

Раз «таблиць» може бути безліч на одному «аркуші» табличного редактора Excel, то ці таблиці потрібно якось відрізняти один від одного. Звичайно, одну таблицю можна просто виділити для наочності, як показано на рис. 2.

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

Знову ж, припустимо, потрібно виділити одну або більше таблиць. І що з цим можна робити? Можна буде тільки наочно бачити ці таблиці очима, не більше того. Але наочний вид таблиць набагато краще в текстовому редакторі Word, тому не варто змагатися з ним в тому, де Excel програє спочатку.

Як позначають таблицю в Excel

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

Спробуємо позначити таблицю, виділену на рис. 2. У даній таблиці верхній лівий кут – це осередок, що має позначення B2. А в нижньому правому куті таблиці знаходиться осередок з позначенням F3. Всі інші осередки виділеної таблиці знаходяться як би між перерахованими двома осередками B2 і F3. Таке компактне і зручне розташування елементів таблиці, виділеної на рис. 2, строго між B2 і F3 дає нам можливість позначити дану таблицю не інакше, як B2: F3.

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

Напевно, тепер буде зрозуміло, як виглядала б таблиця B2: C5, якщо її виділити на рис. 2. Вона б включала в себе осередки B2, C2, B3, C3, B4, C4, B5, C5. Подібне позначення таблиці в редакторі Excel в форматі «осередок: осередок» без лапок (наприклад, B2: F3, B2: C5, A3: G7 і т.п.) далі допоможе розібратися, як можна шукати дані в таблицях Excel. І як можна працювати з таблицями Excel за допомогою виразних засобів, закладених в табличний редактор Excel.

Заносимо дані в таблицю Excel

Щоб перейти до пошуку даних в таблиці Excel, спочатку потрібно заповнити таблицю даними. Візьмемо за основу таблицю B2: F3, показану на рис. 2, і занесемо в неї дані – це список гіпотетичних учнів гіпотетичного класу, скажімо 8А класу.

Як заносять дані в таблицю Excel, вже було. Тому достатньо лише повторити цей «урок» і занести дані в 10 осередків невеликий, фактично навчальної таблиці B2: F3, на якій будуть показані деякі прийоми вилучення даних. В результаті отримаємо таблицю, показану на рис. 3.

Мал. 3. Таблиця B2: F3 в екселя з заповненими осередками B2, C2, D2, E2, F2, B3, C3, D3, E3, F3.

У таблиці, показаної на рис. 3, насправді є ще дані в осередках B1, C1, D1, E1, F1. При цьому таблиця позначена як B2: F3.

Чому ж осередок B1 була призначена в якості самої верхньої лівої комірки таблиці? Та тому, що дані в першому рядку таблиці неінформативні. Там розміщені заголовки стовпців таблиці. Дані ж на учнів знаходяться в осередках B2: F3, звідси і таке позначення таблиці.

Навіщо потрібні формули в Ексель

Тепер варто спробувати занести будь-які дані з таблиці B2: F3 на інше місце листа. Наприклад, давайте спробуємо автоматично записати в осередок B5 прізвище «Петрова» з таблиці B2: F3.

Чого здавалося б простіше? Розміщуємо курсор в осередок B5 (один раз натискаємо по осередку лівою кнопкою мишки). Осередок виділяється. Але в ній поки порожньо, жодного прізвища «Петрова» там немає (рис. 4).

Мал. 4. Порожня комірка B5 в табличному редакторі Excel.

Звичайно, можна просто в цей осередок надрукувати за допомогою клавіатури слово «Петрова». І тоді там з’явиться це прізвище. Але? Дивимося очима на таблицю B2: F3, бачимо там прізвище «Петрова» і друкуємо цю ж прізвище в клітці B5 нашого листа. Але де тут роль табличного редактора Excel з автоматичного занесенню даних? Фактично, користувач екселя працює за Ексель …

Потрібно зробити, гадаю, щоб дані з комірки B3 таблиці B2: F3 автоматично потрапили б в клітинку B5. Як це зробити? Тут на допомогу приходять формули табличного редактора Excel.

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

У нашому конкретному випадку хочемо домогтися, щоб в клітинку B5 були поміщені дані з комірки B3. Адже так? У B3 зберігається прізвище «Петрова». У B5 поки нічого немає. І потрібно, щоб в клітинку B5 були поміщені дані з комірки B3. Подібна дія робиться за допомогою формул. У осередок B5 ми повинні помістити не словом «Петрова», а якусь поки нам невідому формулу, за допомогою якої Ексель автоматично візьме дані з комірки B3 і помістить їх в осередок B5.

Як пишуть формули в Excel

Формули пишуть, починаючи зі знака рівності «=» (без лапок).

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

В даному випадку потрібно в клітинку B5 записати одну з найпростіших формул Excel, яка виглядає наступним чином: «= B3» (без лапок). Що це означає? «Так само» – це ознака формули. А сама формула складається з єдиного позначення єдиною осередки листа – це позначення елементу B3.

Формули екселя пишуть або прямо в виділеної осередку листа, як ніби туди вводяться дані. Але, повторюся, формула починається зі знака рівності – це її єдина відмінність від звичайних даних. Або можна формулу писати в спеціальному віконечку для введення формул (позначено цифрою 1 на рис. 5).

Приклад формули Excel

Тепер пропоную вписати формулу «= B3» в віконце для введення формул (рис. 5):

Мал. 5. Введення формули «= B3» в віконце для введення формул табличного редактора Microsoft Excel до моменту натискання на клавішу (до виконання формули).

Як тільки написана формула, осередок B3 таблиці B2: F3 забарвилася в синій колір, а у формулі позначення елементу B3 теж забарвилося в такий же колір: так нам Ексель дає «підказки» в процесі складання формули. Зручно, чи не так? До речі, можна вручну не вводити «B3» (без лапок), а в процесі введення формули помістити курсор на осередок B3 і клікнути один раз лівою кнопкою мишки. Ексель «розуміє» такий клік точно так, як «помістити позначення зазначеної осередку в формулу». Можна спробувати клікнути замість ручного введення.

Щоб Excel справив обчислення за формулою, потрібно після введення формули натиснути на клавішу <Enter>. Це буде сигналом для проведення обчислень.

Що отримаємо в результаті «обчислення» за вказаною формулою? За допомогою цієї формули Excel знайде дані в осередку B3 і помістить їх в осередок B5. Як Ексель про це «здогадається»? Та дуже просто. У формулі вказана осередок B3 – це факт. А сама формула варто (записана) в осередку B5, значить, результат роботи формули потрібно записати в клітку B5. Ось таким нехитрим способом вдається дані з комірки B3 відтворити в осередку B5: тепер і в B3, і в B5 записано «Петрова» (рис. 6).

Мал. 6. Після обчислення за формулою «= B3», розташованої в осередку B5, в осередку B5 з’явилося прізвище «Петрова», перенесена туди з осередку B3.

Як знайти дані в стовпці Excel за допомогою формули

У наведеному вище прикладі введення в осередок B5 даних з комірки B3 листа Ексель є один істотний недолік. Застосована нами формула «= B3» прямо посилалася на осередок B3. А чому саме на цей осередок послалися у формулі? Та тому, що саме в цьому осередку стояло прізвище «Петрова».

Візуально (очима) побачили, що в B3 варто «Петрова», і тому вказали осередок B3 у формулі. Це добре, що навчальна таблиця B2: F3 складається лише з 2-х рядків. А якби це була не навчальна, а реальна таблиця, скажімо всіх учнів міста? І в ній би була тисяча рядків. Довелося б візуально шукати «Петрову» серед тисячі учнів? А якби це був би список учнів цілої області, країни, нарешті?

Табличний редактор Excel дозволяє працювати з величезними таблицями, з тисячами і тисячами рядків і стовпців. Тому варіант формули «= B3» (без лапок) – це найпростіший спосіб, який зручний в якості навчального прикладу, але може зовсім не спрацювати в реальній практиці.

Так як же знайти в таблиці «Петрову”, не вказуючи конкретну осередок, де це прізвище записана, а посилаючись цілком на таблицю, на всю таблицю B2: F3?

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

а) в таблиці B2: F3,

б) в стовпці, який є першим в таблиці B2: F3.

Функція ВПР в Excel: як користуватися

Побудуємо відповідну формулу екселя для пошуку даних в таблиці B2: F3. Для цього використовуємо функцію екселя «ВПР» (без лапок), яка дозволяє шукати дані в таблицях. Саме для пошуку в таблицях призначена функція ВПР, а не для пошуку в усьому аркуші Excel.

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

Аргументи будь-якої функції Excel відокремлюються одна від одної знаком «крапка з комою» ( «;» без лапок). У функції «ВПР» 4 аргументу. Значить, функція «ВПР» виглядає так (без лапок): «ВПР (аргумент1; аргумент2; аргумент3; аргумент4)». Все просто, чи не так ?! Залишається лише розібратися, що це за аргументи такі.

    • аргумент1 – це те, що потрібно шукати у першій колонці таблиці. У нашому прикладі шукаємо в таблиці прізвище «Петрова», чи не так? Шукаємо саме в першій колонці таблиці B2: F Відзначимо ще раз, що функція «ВПР» шукає дані ТІЛЬКИ в першій колонці таблиці, не у другій, не в третій, чи не в сто першої, а тільки в першій! Скільки б не було колонок в таблиці, все одно пошук того, що записано в першому аргументі функції ВПР, буде здійснюватися тільки в першій колонці таблиці.
    • аргумент2 – це позначення самої таблиці. У розглянутому прикладі таблиця позначається B2: F3 (без лапок, прогалин чи інших символів).
  • аргумент3 – це номер колонки таблиці, звідки потрібно витягти дані. Тут без довгих пояснень не обійтися.

Отже, перш за все за допомогою функції «ВПР» в ПЕРШОЇ колонці таблиці B2: F3 будуть знайдені дані, записані в першому аргументі функції, тобто буде знайдена прізвище «Петрова». Але результатом роботи функції ВПР не обов’язково повинні бути запис «Петрова». Результатом можуть бути дані з будь-якої іншої комірки, але тільки в тому рядку таблиці, де в першій колонці стоїть «Петрова».

Якщо хочемо помістити в клітку B5 знайдену в першій колонці таблиці B2: F3 прізвище «Петрова», то в якості 3-го аргументу потрібно вказати цифру 1. Тоді, знайшовши «Петрову» в першій колонці таблиці B2: F3, Ексель помістить в B5 прізвище «Петрова».

Ну, а якщо потрібно в B5 помістити, скажімо, ім’я Петрової «Юлія», то вже в якості третьої аргументу потрібно буде вказувати другу колонку таблиці, тобто поставити цифру 2 в якості 3-го аргументу функції «ВПР».

Поки ж зупинимося на цифрі 1 в якості третьої аргументу. З іншими цифрами поекспериментуємо трохи пізніше.

  • аргумент4 – це свого роду ознака «точності» пошуку даних в таблиці. Щоб отримати дані з точність 100%, як 4-го аргументу функції «ВПР» потрібно вказати «БРЕХНЯ». Ось так незвично в екселя потрібно вказувати достовірність пошуку: достовірно, значить БРЕХНЯ, хоча тут просто напрошується ІСТИНА. А навпаки, приблизна точність пошуку позначається словом ІСТИНА.

Плутанина виходить. Щоб не заплутатися, поки залишимо в спокої БРЕХНЯ і ИСТИНУ, просто запам’ятаємо, що останній аргумент в розглянутому прикладі повинен бути БРЕХНЯ. А що це таке ІСТИНА і НЕПРАВДА в екселя і взагалі в мовах програмування поки опустимо – це не так просто, і однією або двома фразами ці поняття не пояснити.

Приклад функції ВПР

Тепер можемо приступити до складання функції «ВПР» для даної задачі пошуку «Петрової» в таблиці B2: F3. Ця функція буде виглядати так:

ВПР ( «Петрова»; B2: F3; 1; ЛОЖЬ).

А формула в комірці B5 буде виглядати так само, але тільки зі знаком «дорівнює», що передують функцію «ВПР»:

= ВПР ( «Петрова»; B2: F3; 1; ЛОЖЬ)

Введемо представлену функцію в рядок формул для осередку B5. Для цього

  • помістимо курсор на клітку B5,
  • кликнемо один раз лівою кнопкою мишки,
  • перемістимо курсор в рядок формул і
  • знову кликнемо один раз лівою кнопкою мишки, після чого, нарешті,
  • в рядку формул наберемо формулу, як показано на рис. 7.

Мал. 7. Введення формули з функцією ВВР в клітинку B5 для пошуку даних в таблиці B2: F3 Excel.

Після натискання на клавішу <Enter> по завершенні введення формули, отримаємо в комірці B5 цілком очікуваний результат (рис. 8).

Мал. 8. Результат (після натискання на клавішу) використання функції ВПР в осередку B5 для пошуку даних в таблиці B2: F3 Excel.

Зверніть увагу, що результат, представлений на рис. 8, нічим не відрізняється від результату, представленого на рис. 6. Але в варіанті на рис. 6 дані для B5 були взяті прямо з B3. А результат, представлений на рис. 8, отриманий шляхом пошуку даних в першому стовпчику таблиці B2: F3. Тобто, у другому випадку (рис. 8) вдалося «змусити» Ексель шукати дані без явної вказівки конкретної комірки таблиці.

Міняємо в ВВР умови для пошуку даних в таблиці Excel

Давайте змінимо у формулі «= ВПР (…» третій аргумент. Ми обмежили себе цифрою 1 (це номер колонки таблиці, звідки потрібно витягти дані), щоб помістити в B5 прізвище «Петрова». Пропоную тепер спробувати поставити замість колишньої одинички, наприклад, двійку після другого знака «крапка з комою». Вийде:

= ВПР ( «Петрова»; B2: F3; 2; ЛОЖЬ)

Що бачимо після застосування даної формули? На рис. 9 тепер замість прізвища «Петрова» в осередку B5 стоїть ім’я Петрової – «Юлія». Excel знайшов слово «Петрова» в першій колонці таблиці, а потім взяв всю рядок, всі дані, приписані в таблиці до «Петрової», після чого вже з другої колонки виділив ім’я Петрової «Юлія».

Мал. 9. Зміна результату використання функції ВПР для пошуку даних при заміні третього аргументу функції з 1 на 2. В осередку B5 тепер записано ім’я Петрової «Юлія», а не її прізвище «Петрова».

Я сподіваюся, що Ви отримали деякі уявлення про те, як можна в таблицях редактора Excel шукати і знаходити дані. Не тільки в маленьких навчальних табличках, де і так все дані видно неозброєним оком. Але і в будь-яких інших великих і навіть величезних таблицях. Звичайно, це початок, лише підступи до можливостей екселя. Але, ймовірно, дещо стає зрозумілим.

Вправи по Excel

Можна тепер трохи самостійно «потренуватися». Спробуйте замінити третій аргумент на трійку замість колишніх одиниці або двійки. У Вас вийшло таким чином в B5 автоматично помістити по батькові Петрової «Степанівна», як показано на рис. 10?

Мал. 10. Зміна даних в осередку B5 таблиці Excel після проведення заміни третього аргументу функції «ВПР» у формулі клітини B5.

А якщо шукане слово «Петрова» в першому аргументі замінити на слово Іванов », то змінюється щось у результаті? Змінюється результат в осередку B5 на «Сергійович», як показано на рис. 11?

Мал. 11. Зміна даних в осередку B5 ексельной таблиці після проведення заміни першого аргументу функції «ВПР» у формулі для комірки B5.

Результат в осередку B5 повинен змінюватися, якщо все правильно зроблено! А чому не повинно змінюватися? Адже для того і застосовуються формули і функції табличного редактора Microsoft Excel, щоб можна було автоматично знаходити будь-які необхідні дані в таблицях екселя.

А можна ще щось зробити з наведеними даними? Визначити, наприклад, вік учнів? Порівняти, хто з них старше або молодше? Перевірити, чи немає у кого-то з них сьогодні дня народження? І тому подібне.

Відповідь на все перераховане вище: так, можна! Тільки потрібно глибше познайомитися з можливостями Excel при роботі з таблицями.

Додаткові матеріали:

1. Як визначити термін покупки залізничних квитків за допомогою Microsoft Excel

2. Як відкрити два вікна в Ворд одночасно

3. Що таке змінна в програмуванні і чим вона відрізняється від константи

4. Що таке змінна з індексами, масив, коментар, цикл і лічильник в програмуванні на конкретному прикладі

Отримуйте актуальні статті з комп’ютерної грамотності прямо на вашу поштову скриньку. вже більше 3.000 передплатників

.

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

Ссылка на основную публикацию