Примеры использования функции области для диапазонов excel

Как сделать ВПР в Excel: понятная пошаговая инструкция.

Для начала на простом примере разберем, как работает функция ВПР в Excel. Предположим, у нас есть две таблицы. Первая – это прайс-лист с наименованиями и ценами. Вторая – это заказ на покупку некоторых из этих товаров. Искать в прайс листе нужный товар и руками вписывать в заказ его цену – занятие очень утомительное. Ведь прайс с ценами может насчитывать сотни строк. Нам необходимо сделать всё автоматически.

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

Наш прайс-лист расположен в столбцах А и В. Список покупок – в E-H. Допустим, первая позиция в списке покупок – бананы. Нам нужно в столбце A, где указаны все наименования, найти этот товар, затем его цену поместить в ячейку G2.

Для этого в G2 запишем следующую формулу:

А теперь разберем подробно, как сделать ВПР.

Мы берем значение из E2.
Ищем точное совпадение (поскольку четвертым параметром указан 0) в диапазоне $A$2:$B$7 в первой его колонке (крайней левой)

Обратите внимание, что лучше сразу же использовать абсолютные ссылки на прайс-лист, чтобы при копировании этой формулы ссылка не «соскользнула».
Если товар будет найден, то нужно перейти во второй столбец диапазона (на это указывает третий параметр = 2).
Взять из него цену и вставить ее в нашу ячейку G2.

Получилось? Теперь просто скопируйте формулу из G2 в G3:G8.

Отчет о продажах готов.

Также чтобы понять, что такое точное совпадение, попробуйте в A5 или в E2 изменить наименование товара. К примеру, добавьте пробел в конце. Внешне ничего не изменилось, но вы сразу же получите ошибку #Н/Д. То есть, товар не был обнаружен. В то же время, таких случайных ошибок можно легко избежать, о чем мы поговорим отдельно.

Особо остановимся на четвертом параметре. Мы указали ноль (можно было написать ЛОЖЬ), что означает «точный поиск». А что, если забыть его указать и закончить номером столбца, из которого извлекаются нужные данные?

Давайте еще раз шаг за шагом разберем, что в этом случае будет происходить.

1. Берем значение из E2.
2. Начинаем его искать в крайней левой колонке диапазона $A$2:$B$7, то есть в столбце A. Поскольку в A2 совпадение не найдено, смотрим дальше: что находится ниже.
3. Там обнаруживаем товар «Сливы». При этом предполагается, что наш список отсортирован по алфавиту. Ведь именно это – главное условие поиска приблизительного совпадения.
4. Поскольку в сортированном списке «сливы» находятся ниже, чем «бананы», то функция принимает решение, что дальше искать слово, начинающееся на «Б» нет смысла. Процесс можно остановить. И остаться на букве «А». То есть, там и находится наиболее близкое значение.
5. Поскольку поиск завершен, переходим из A2 во второй столбец, то есть в B. Вставляем данные из B2 в G2 как результат вычислений.

К сожалению, «бананы» были в нашем прайс-листе ниже, но до них просто «не дошел ход». И в список покупок теперь записана неправильная цена.

При помощи этой инструкции мы рассмотрели только основы. А как реально этим можно пользоваться?

Таблица подстановки с двумя изменяющимися переменными

Установить курсор в ячейку Е20 и ввести формулу =СУММ(Н3:Н10).

В ячейку Е21 ввести с клавиатуры 60, в ячейку Е22 ввести 80, в ячейку Е23 – 100, в ячейку F20 – 50, в G20 – 70, в Н20 — 90 (обратить внимание, что введенные значения количества продаж видеомагнитофона располагаются в ячейках столбца Е непосредственно под формулой, по которой определяется выручка магазина, а значения количества продаж видеокамеры – в ячейках строки справа от формулы).

Выделить блок ячеек Е20:Н23, выбрать команду меню Данные|Таблица подстановки.

В открывшемся диалоговом окне Таблица подстановки, установив курсор в опции Подставлять значения по столбцам, щелкнуть в ячейке Е9, а в опции Подставлять значения по строкам щелкнуть левой кнопкой мыши в ячейке Е3 и нажать ОК (рис.32)

Как заполнить диапазон

Для заполнения диапазона определенными значениями необходимо предпринимать такие действия:

1. Ввести нужное значение в ячейку B2. Оно может быть как числовым, так и текстовым. Также возможно введение формулы. В нашем случае это цифра 2.

2. Далее нажимаем маркер автозаполнения (как раз тот самый квадратик, который мы ранее просили не нажимать) и перетаскиваем его вниз до конца диапазона.

В результате получится следующее. Здесь мы заполнили все нужные ячейки цифрами 2.

6

Автозаполнение – одна из самых востребованных функций Excel. Она позволяет осуществлять запись в ячейки диапазона не только одного значения, но и целого набора данных, которые соответствуют определенной закономерности. Например, числовой ряд 2, 4, 6, 8, 10 и так далее.

Для этого нам нужно ввести первые два значения последовательности в расположенные рядом по вертикали ячейки и передвинуть маркер автозаполнения на необходимое количество ячеек.

78

Аналогично можно заполнять диапазон нужными датами, которые также подчиняются определенной закономерности. Для этого, давайте, введем дату 13 июня 2013 года и дату 16 июня 2013 года в американском формате.

9

После этого осуществляем уже знакомое нами перетаскивание.

10

Ячейка

В окне рабочей области находится содержимое текущего листа. Его рабочая область разбивается на ячейки. Ячейка — мельчайшая структурная единица электронной таблицы. Столбцы обозначаются с помощью букв латинского алфавита, а строки — с помощью цифр. В результате адрес ячейки формируется из имени столбца и номера строки, на пересечении которых она располагается.

Ещё один способ обозначения ячеек — это система адресов RC: от английских названий строки «Row» и столбцов «Column». Например, ячейка C3 в такой системе будет находиться по адресу R3C3.

Для того чтобы задать ячейке имя, следует использовать последовательность символов, которая будет отличаться от адресов ячеек. Кроме того, названия ячеек не могут содержать в себе пробелы и начинаться с цифр. Чтобы назвать ячейку, нужно выделить её, вписать данные в поле «Имя» и нажать «Enter».

Функция ЕСЛИ в MS Excel с одним условием.

Функцией ЕСЛИ в Excel, первый пример.

Рассмотрим простой пример применения функции ЕСЛИ в MS Excel. У нас есть столбец №1, в котором содержаться числа от 1 до 10.

Необходимо проверить, соответствует ли числа находящиеся в столбце №1 нашему критерию. Критерий: является ли числа находящиеся в столбце №1 числами, значения которых больше числа 4.

Взываем функцию ЕСЛИ в ячейку С3.

В диалоговом окне Аргументы функции видим три поля для заполнения:

логическое_выражение —  B3>4, здесь указан наш критерий проверки. В данном случае проверяем ячейку В3. Функция определяет, является ли число, которое содержится в этой ячейки, числом больше числа 4.

значение_если_истина — «правда», это слово появиться в ячейки С3, если число содержащиеся в ячейки В3 больше числа 4. То есть выполняется условие заданное в логическом_выражении.

значение_если_ложь — «неправда», это слово появиться в ячейки С3, если число содержащиеся в ячейки В3 меньше числа 4. То есть не выполниться условие заданное в логическом_выражении.

Важно: текст указанный в полях «значение_если_истина» и «значение_если_ложь», должны быть заключены в кавычки. Иначе функция выдаст ошибку. . Нажимаем ОК в диалоговом окне Аргументы функции. 

Нажимаем ОК в диалоговом окне Аргументы функции. 

Как видим, число содержащиеся в ячейки В3 не является числом, которое больше числа 4. Функция ЕСЛИ вернула значение_если_ложь (неправда).  Протянем функцию ЕСЛИ вниз по столбцу, чтобы проверить значения находящиеся в остальных ячейках столбца №1, на соответствие нашему критерию (логическое_выражение).

Как видно из результата, ячейки в диапазоне В7:В12 содержат в себе числа, которые больше числа 4. Функция ЕСЛИ вернула значение_если_истина (правда).

Функция ЕСЛИ в Excel, второй пример.

Рассмотрим еще один пример. Предположим, что у нас есть таблица. В ней фамилии студентов, которые сдавали зачет. Часть студентов сдали зачет, а часть не сдали.

Теперь добавим в нашу таблицу столбец: Допуск к экзамену. Используем функцию ЕСЛИ для того, чтобы определить допущен студент к экзамену или нет. Диалоговое окно Аргументы функции будет выглядеть вот так:

Важно: текстовый критерий в поле логическое_выражение должен быть заключен в кавычки. Иначе функция выдаст ошибку

Если студен сдал зачет, он допущен к экзамену, если нет, то не допущен. Нажимаем ОК в диалоговом окне Аргументы функции и протягиваем функцию ЕСЛИ вниз, по всему столбцу Допуск к экзамену. Получаем:

Видим, что в зависимости от результатов сдачи зачета, студент допущен к экзамену, либо не допущен.

Обращаю внимание, что при работе с функцией ЕСЛИ, можно использовать разные варианты равенств: больше (>), меньше(=), меньше или равно ()

Конкатенация

Формула: =(ячейка1&» «&ячейка2)

=ОБЪЕДИНИТЬ(ячейка1;» «;ячейка2)

За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&» «&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между » «, чтобы между объединёнными данными появился пробел.

Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;» «; B1) или в англоязычном варианте =concatenate(A1;» «; B1).

Кстати, все перечисленные формулы можно применять и в Google‑таблицах.

Эта статья является лишь верхушкой айсберга в изучении Excel. Для профессионального использования программы рекомендуем учится у профессионалов на курсах по Microsoft Excel.

Создание «Умной таблицы»

Для создания «Умной таблицы» необходимо выбрать любую ячейку внутри таблицы без форматирования или выделить произвольный диапазон, в котором планируется создать такую таблицу, и нажать кнопку «Форматировать как таблицу» на вкладке «Главная». Откроется окно выбора формата будущей «Умной таблицы»:

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

В примере заголовки уже присутствуют внутри диапазона с таблицей, поэтому галочку «Таблица с заголовками» оставляем. Нажав «OK», получим следующую «Умную таблицу»:

Теперь при записи формулы создаются адреса с именами колонок, а при нажатии «Enter» формула автоматически копируется во все ячейки этой графы:

Адреса с именами колонок создаются, если аргументы для формулы берутся из той же строки. Аргументы, взятые из других строк, будут отображены обычными ссылками.

Когда «Умная таблица» уже создана, подобрать для нее подходящее цветовое оформление становится легче. Для этого нужно выбрать любую ячейку внутри таблицы и снова нажать кнопку «Форматировать как таблицу» на вкладке «Главная». При наведении курсора на каждый образец форматирования, «Умная таблица» будет менять цветовое оформление в режиме предпросмотра. Остается только выбрать и кликнуть на подходящем варианте.

При выборе любой ячейки внутри «Умной таблицы» на панели инструментов появляется вкладка «Работа с таблицами Конструктор». Перейти в нее можно, нажав на слово «Конструктор».

На вкладке «Конструктор» отображены все инструменты для работы с «Умной таблицей» (неполный перечень):

• редактирование имени таблицы;
• изменение цветового чередования строк на цветовое чередование столбцов;
• добавление строки итогов;
• удаление кнопок автофильтра;
• изменение стиля таблицы (то же, что и по кнопке «Форматировать как таблицу» на вкладке «Главная»);
• удаление дубликатов;
• добавление срезов*, начиная с Excel 2010;
• создание сводной таблицы;
• удаление функционала «Умной таблицы» командой «Преобразовать в диапазон».

*Срезы представляют из себя удобные фильтры по графам в отдельных окошках, работающие аналогично кнопкам автофильтра в строке заголовков. Создается срез (или срезы) нажатием кнопки «Вставить срез» и выбором нужной колонки (или колонок). Чтобы удалить срез, его нужно выбрать и нажать на клавиатуре «Delet» или пункт «Удалить (имя среза)» в контекстном меню.

Использование в расчетах вложенных функций

• В ячейку I2 ввести название новой графы «Наличие скидки».
• Открыть «Прайс-лист».
• В ячейку А12 ввести с клавиатуры «Скидка», в ячейку В12 ввести 5%.
• Выделить блок ячеек А12:В12 и выбрать пункт меню Вставка|Имя|Создать.
• В открывшемся окне Создать имена установить флажок В столбце слева и нажать ОК.
• Снять выделение с блока ячеек.
• Перейти на лист «Отчет».
• Установить курсор в ячейку I3 и щелкнуть по пиктограмме Вставка функции.
• В появившемся окне Мастер функций выбрать категорию функций Логические.
• В списке функций выбрать ЕСЛИ.
• Нажать ОК.
• Подвинуть появившееся на экране окно за пределы таблицы, чтобы не мешать указателю мыши работать с ячейками таблицы.
• Установить курсор в окно Логическое выражение и щелкнуть по кнопке со стрелкой списка функций, которая располагается в строке формул на месте поля имен (см.рис.17).

• В открывшемся списке выбрать Другие функции.
• В появившемся окне Мастер функций выбрать категорию функций Логические.
• В списке функций выбрать И.
• Нажать ОК. Открылось следующее окно (рис.18):

• В появившемся окне Аргументы функции (функции ЕСЛИ) перевести курсор в поле Значение_если_истина.
• Нажать функциональную клавишу F3 на клавиатуре и в открывшемся окне выбрать имя блока «Скидка». Нажать ОК.
• Перевести курсор в окно Значение_если ложь и нажать клавишу Пробел на клавиатуре, что будет означать отсутствие скидки (рис.21).

=ЕСЛИ(И(F3>500;E3=»Нал»);Скидка;» «)

Анализ данных с помощью сводных таблиц

Средство Сводная таблица представляет собой еще один инструмент организации и подведения итогов данных. Этот инструмент комбинирует возможности работы со списками.Мастера

• Установить курсор в любую ячейку с данными исходной таблицы « Отчет ООО «Техносервис» о продаже аудио- и видеотехники магазинам-закупщикам в 1 квартале».
• Выбрать пункт меню Данные|Сводная таблица.
• В группе Создать таблицу на основе данных, находящихся установить переключатель в Списке или базе данных Microsoft Excel.
• В группе Вид создаваемого отчета установить переключатель Сводная таблица и нажать кнопку Далее.
• Проверить, что в появившемся окне в опции Диапазон указан весь диапазон ячеек исходной таблицы, и нажать Далее.
• В опции Поместить таблицу в установить переключатель в новый лист и нажать кнопку Макет.

• При помощи мыши перетащить поле «Наименование магазина» (4-е сверху) в область Страница, поле «Вид продукции» — в область Строка, поле «Вид оплаты» — в область Столбец, поле «Сумма» — в область Данные и нажать ОК.
• Нажать Готово.

Ячейки и диапазоны

Некоторые из Вас, должно быть, обратили внимание на такой инструмент Excel как Paste Special (Специальная вставка). Многим, возможно, приходилось испытывать недоумение, если не разочарование, при копировании и вставке…

Большинство из нас используют разрывы строк даже на задумываясь. Разрывы могут быть использованы для начала нового абзаца в Microsoft Word, в повседневных ситуациях, когда пишите письмо по электронной…

Как закрепить строку, столбец или область в Excel? – частый вопрос, который задают начинающие пользователи, когда приступают к работе с большими таблицами. Excel предлагает несколько инструментов, чтобы сделать…

Если в Вашей таблице Excel присутствует много пустых строк, Вы можете удалить каждую по отдельности, щелкая по ним правой кнопкой мыши и выбирая в контекстном меню команду Delete…

Иногда мы аккуратно и старательно вводим данные в столбцах Excel и только к концу понимаем, что гораздо удобнее расположить их горизонтально, т.е. не в столбцах, а в строках….

Бывают случаи, когда необходимо удалить строку или столбец на листе Excel, однако, Вы не хотите удалять их окончательно. На этот случай Excel располагает инструментом, который позволяет временно скрыть…

Если Вы работаете над большой таблицей Excel, где все строки и столбцы не помещаются на экране, существует возможность закрепить некоторые строки и столбцы, чтобы было удобней работать с…

Представьте, что Вы оформили все заголовки строк и столбцов, ввели все данные на рабочий лист Excel, а затем обнаружили, что таблица смотрелась бы лучше, если ее перевернуть, т.е….

Microsoft Excel позволяет применять форматирование не только к содержимому, но и к самой ячейке. Вы можете настроить границы у ячеек, а также задать цвет заливки. Кроме этого, Excel…

Microsoft Excel позволяет выравнивать текст в ячейках самыми различными способами. К каждой ячейке можно применить сразу два способа выравнивания – по ширине и по высоте. В данном уроке…

Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ

Если произвести поиск по функциям подстановки, Google покажет, что ВПР намного популярнее функции ИНДЕКС. Оно и понятно, ведь чтобы придать функции ИНДЕКС тот же функционал, что и ВПР, необходимо воспользоваться еще одной формулой – ПОИСКПОЗ. Что касается меня, было всегда непросто попробовать и освоить две новые функции одновременно. Но они дают больше возможностей и гибкости в создании электронных таблиц. Но обо всем по порядку.

Функция ВПР()

Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; ). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».

Но случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

Когда ключевое поле находится правее данных, которые вы хотите получить, ВПР не поможет. Если, конечно, была бы возможность задать номер_столбца -1, тогда проблем бы не было. Одним из распространенных решений является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнить табельные номера с помощью ВПР, сохранить их как значения и удалить временную колонку A.

Функция ИНДЕКС()

Чтобы решить нашу проблему в один шаг, необходимо воспользоваться формулами ИНДЕКС и ПОИСКПОЗ. Сложность данного подхода заключается в том, что требуется применить две функции, которые, возможно, вы никогда не применяли до этого. Для упрощения понимания решим эту задачу в два этапа.

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; ).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

Функция ПОИСКПОЗ()

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; ).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

Ну, в принципе, все. Функция ПОИСКПОЗ указывает функции ИНДЕКС, в какой строке искать значение. Замените знак вопроса в формуле ИНДЕКС формулой ПОИСКОПОЗ и вы получите эквивалент ВПР с возможностью поиска данных, находящихся левее ключевого столбца. Получиться так:

В начале было непривычно, был огромный соблазн вставить еще одну колону и работать, как всегда работал. Но со временем использование функции ИНДЕКС вошло в привычку. Оказалось, что это быстрее и требует меньших манипуляций. Так что в следующий раз, когда у вас возникнет желание задать номеру столбца отрицательное число в ВПР, воспользуйтесь сочетанием двух странных функций ИНДЕКС и ПОИСКПОЗ, чтобы решить свою проблему.

Функция ПОИСК в формуле массива

Примеры выше, где буквы перечислены явно в строковом массиве, занимает довольно много места. Буквы при этом идут подряд, что наводит на мысль, что их можно как-то иначе выразить как диапазон.

И действительно, это возможно с помощью комбинации с функциями СТРОКА и ПОИСК:

{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1))>0}

Отличие этой формулы массива от предыдущих — ее нужно вводить без фигурных скобок, они появятся при вводе формулы сочетанием Ctrl+Shift+Enter (вместо обычного Enter). В формуле выше, где явно прописаны все буквы, фигурные скобки вводятся вручную — это явное указание строкового массива.

Что происходит в этой формуле?

• Функция СТРОКА с численным аргументом «65:90» возвращает массив чисел с 65 по 90 включительно. Как раз в этом диапазоне в таблице ASCII находятся все символы латиницы;
• Функция СИМВОЛ возвращает для каждого числового значения в этом массиве его символ, таким образом создавая массив латинских символов;
• Функция ПОИСК производит поиск каждого из этих символов в строке и возвращает либо число, либо ошибку, таким образом создавая массив чисел и ошибок
• Функция СЧЁТ считает числовые значения в полученном массиве. Если результат больше нуля, значит, хотя бы один символ латиницы был найден. Если нет (все поиски вернули ошибку), значит, не был

Аналогичная формула для кириллицы:

{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1))>0}

Подробнее о поиске и извлечении кириллицы и латиницы в Excel можно почитать тут:

Найти латиницу или кириллицу в тексте

Есть еще множество комбинаций функции ПОИСК с другими функциями Excel, смотрите разделы: Функция ИЛИ Функция И Функция ЗНАЧЕН Удалить первое слово в ячейке Excel

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

Совместное использование функций ИНДЕКС и ПОИСКПОЗ в Excel – хорошая альтернатива ВПР, ГПР и ПРОСМОТР. Эта связка универсальна и обладает всеми возможностями этих функций. А в некоторых случаях, например, при двумерном поиске данных на листе, окажется просто незаменимой. В данном уроке мы последовательно разберем функции ПОИСКПОЗ и ИНДЕКС, а затем рассмотрим пример их совместного использования в Excel.

Более подробно о функциях ВПР и ПРОСМОТР.

Функция ПОИСКПОЗ в Excel

Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов:

• – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
• 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
• -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в Excel ее очень часто используют вместе с функцией ИНДЕКС.

Функция ИНДЕКС в Excel

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функции ИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

Если Вы уже работали с функциями ВПР, ГПР и ПРОСМОТР в Excel, то должны знать, что они осуществляют поиск только в одномерном массиве. Но иногда приходится сталкиваться с двумерным поиском, когда соответствия требуется искать сразу по двум параметрам. Именно в таких случаях связка ПОИСКПОЗ и ИНДЕКС в Excel оказывается просто незаменимой.

На рисунке ниже представлена таблица, которая содержит месячные объемы продаж каждого из четырех видов товара. Наша задача, указав требуемый месяц и тип товара, получить объем продаж.

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

На этой прекрасной ноте мы закончим. В этом уроке Вы познакомились еще с двумя полезными функциями Microsoft Excel – ПОИСКПОЗ и ИНДЕКС, разобрали возможности на простых примерах, а также посмотрели их совместное использование. Надеюсь, что данный урок Вам пригодился. Оставайтесь с нами и успехов в изучении Excel.

Структура таблицы

Структуры данных таблицы зависит от ее типа:

• разъяснительные (могут в сжатом виде преподнести большой объем теоретического материала);
• сравнительные (осуществляют сравнение различных параметров нескольких объектов);
• обобщающие (тематические) (позволяют произвести систематизацию текстовых сообщений).

Любая таблицы состоит из следующих элементов:

1. Номер таблицы – нумерационный заголовок.
2. Общий заголовок – имя таблицы.
3. Верхний заголовок (его еще называют «головкой») – первая строка таблицы, предназначенная для ввода наименований столбцов.
4. Боковой заголовок (или «боковик») – левый столбец, в котором записываются имена строк.
5. Прографка – совокупность горизонтальных и вертикальных полос таблицы, в которой находятся данные, относящиеся к головке и боковику.
6. Ячейка – основной табличный элемент, образующийся пересечением строки и столбца.
7. Строка – ряд смежных ячеек, ограниченный линиями сверху и снизу.
8. Графа (столбец) таблицы – вертикальная колонка данных.

Структура таблицы

Для того, чтобы корректно составить таблицу, необходимо знать некоторые правила оформления таблицы:

• Оформление таблиц начинается с написания номера таблицы, представляющий собой сочетание слова«Таблица» + порядковый номер («Таблица 1»). Размещается он в правом верхнем углу. Это делается для упрощения ссылки на конкретную сводку в документе.
• Общий заголовок располагается по центру. Он призван давать четкое представление о хранящейся в таблице информации.
• Наименования строк и столбцов должны быть короткими и понятными. Они записываются с заглавной буквы.В конце заголовков и данных в табличных ячейках точки не принято ставить.
• Имена строк и колонок можно печатать в свободном порядке. В том случае, когда наименований достаточно много целесообразнее их объединить в группы или перечислить в алфавитной последовательности.
• Единицы измерений (в том случае, если в прографке указываются числовые данные), записываются только в заголовке. Они вводятся после наименования,через«,». Например, «Температура, °С».
• Вся область прографки заполняется полностью. Если возникают трудности с заполнением конкретной клетки, то используют прочерк или другой условный знак. Часто используемые символы:

? – неустановленные сведения;

ⅹ – заполнение неосуществимо;

↓ – необходимо использование информации из вышерасположенной ячейки.

Сортировка по нескольким столбцам

• Выполнить команду меню Данные|Сортировка.
• В поле Сортировать по выбрать «Наименование магазина» (это поле называется первым ключом сортировки) и установить флажок По возрастанию. В поле Затем по (второй ключ сортировки) выбрать «Вид продукции» и установить флажок По убыванию. В поле В последнюю очередь по (третий ключ сортировки) выбрать «Вид оплаты (нал./безнал.)» и установить флажок По возрастанию. Второй ключ используется, если обнаруживаются повторения в первом, а третий – если повторяется значение и в первом, и во втором ключе.
• Нажать ОК.
• Снять выделение с таблицы и просмотреть результат на экране.

ПараметрысортировкиСортировать столбцы диапазона

Случайное число в excel — как сгенерировать, примеры

При решении задач в Microsoft Office Excel с помощью макросов иногда необходимо сгенерировать произвольные числа для проверки правильности работы программы. Или нужно создать большой массив различных цифр без повторений. Для подобных заданий в редакторе заложены специализированные функции, и сегодня разберемся, как задать случайное число в excel.

Excel содержит две функции, которые позволяют генерировать произвольные цифры –СЛЧИС и СЛУЧМЕЖДУ. Рассмотрим каждую из них более подробно.

Функция СЛЧИС не имеет аргументов, поэтому просто вписываете ее в строку формул и нажимаете Enter.

Создание и ведение таблиц Excel

Итак, таблица в Excel – это прямоугольная область листа, в которой каждая строка представляет собой набор данных, а в ячейке на пересечении данной строки и каждого столбца находится единица данных. Каждому столбцу присваивается уникальное имя. Столбцы таблицы называются полями, а строки – записями. В таблице не может быть записей, в которых нет данных ни в одном поле.

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