Основные понятия Excel и поиск данных в таблице с использованием функции ВПР

Табличный редактор Microsoft Excel (или просто Excel, а по-русски Эксель) создан и используется для работы с таблицами. С таблицами могут работать и другие редакторы, например, текстовый редактор Microsoft Word (или просто Word, а по-русски Ворд).
Однако именно в экселе заложены широчайшие возможности по обработке табличных данных.

Содержание:
1. Лист и таблица на листе в Excel
2. Нумерация строк и столбцов в таблице Excel
3. Что такое ячейка и что такое таблица Excel
4. Как выделить таблицу Excel
5. Как обозначают таблицу в Excel
6. Ввод данных в таблицу
7. Для чего нужны формулы в эксель
8. Как пишут формулы в Excel
9. Ищем данные в столбце Excel с помощью формулы
10. Функция ВПР и ее аргументы: как пользоваться
11. ВПР: меняем условия для поиска данных в таблице Excel
12. Упражнения по Excel

Лист и таблица(ы) на листе в 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. Извлечение фамилии из ФИО в Excel: способ перевода на язык цифр

3. Что такое переменная в программировании и чем она отличается от константы

4. Что такое переменная с индексами, массив, комментарий, цикл и счетчик в программировании на конкретном примере

5. Деление в Excel двух чисел и работа над ошибками при вводе данных