Как обозначить вектор в excel

ПРОСМОТР (функция ПРОСМОТР)

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

Предположим, что вы знаете артикул детали автомобиля, но не знаете ее цену. Тогда, используя функцию ПРОСМОТР, вы сможете вернуть значение цены в ячейку H2 при вводе артикула в ячейку H1.

Используйте функцию ПРОСМОТР для поиска в одной строке или одном столбце. В приведенном выше примере рассматривается поиск цен в столбце D.

Советы: Рассмотрим одну из новых функций подытогов в зависимости от Office используемой версии.

Используйте функцию ВПР для поиска данных в одной строке или столбце, а также для поиска в нескольких строках и столбцах (например, в таблице). Это расширенная версия функции ПРОСМОТР. Посмотрите видеоролик о том, как использовать функцию ВПР.

Если вы используете Microsoft 365, используйте XLOOKUP — это не только быстрее, но и позволяет искать в любом направлении (вверх, вниз, влево, вправо).

Функцию ПРОСМОТР можно использовать двумя способами: в векторной форме и в форме массива.

Векторнаяформа: используйте эту форму просмотр для поиска значения в одной строке или в одном столбце. Используйте векторную форму, если нужно указать диапазон, содержащий значения, которые вы хотите найти. Например, если вы хотите найти значение в столбце A, вниз до строки 6.

Форма массива:мы настоятельно рекомендуем использовать вместо формы массива функции ВЛП или Г ПРОСМОТР. Просмотрите это видео об использовании ВКП. Форма массива обеспечивает совместимость с другими программами электронных таблиц, но ее функциональность ограничена.

Массив — это набор значений в строках и столбцах (например, в таблице), в которых выполняется поиск. Например, если вам нужно найти значение в первых шести строках столбцов A и B, это и будет поиском с использованием массива. Функция ПРОСМОТР вернет наиболее близкое значение. Чтобы использовать форму массива, сначала необходимо отсортировать данные.

Векторная форма

При использовании векторной формы функции ПРОСМОТР выполняется поиск значения в пределах только одной строки или одного столбца (так называемый вектор) и возврат значения из той же позиции второго диапазона.

Синтаксис

ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов])

Функция ПРОСМОТР в векторной форме имеет аргументы, указанные ниже.

Искомое_значение. Обязательный аргумент. Значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.

Просматриваемый_вектор Обязательный аргумент. Диапазон, состоящий из одной строки или одного столбца. Значения в аргументе просматриваемый_вектор могут быть текстом, числами или логическими значениями.

Важно: Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.

result_vector Необязательный. Диапазон, содержащий только одну строку или столбец. Размер result_vector должен быть того же размера, что и lookup_vector. Размер должен быть одинаковым.

Замечания

Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему.

Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, функция ПРОСМОТР возвращает значение ошибки #Н/Д.

Примеры векторов

Чтобы лучше разобраться в работе функции ПРОСМОТР, вы можете сами опробовать рассмотренные примеры на практике. В первом примере у вас должна получиться электронная таблица, которая выглядит примерно так:

Скопируйте данные из таблицы ниже и вставьте их в новый лист Excel.

Скопируйте эти данные в столбец A

Скопируйте эти данные в столбец B

Теперь скопируйте формулы ПРОСМОТРА из приведенной ниже таблицы в столбец D своего листа.

Скопируйте эту формулу в столбец D

Ниже описано, что эта формула означает

=ПРОСМОТР(4,19; A2:A6; B2:B6)

Поиск значения 4,19 в столбце A и возврат значения из столбца B, находящегося в той же строке.

=ПРОСМОТР(5,75; A2:A6; B2:B6)

Поиск значения 5,75 в столбце A, соответствующего ближайшему наименьшему значению (5,17), и возврат значения из столбца B, находящегося в той же строке.

=ПРОСМОТР(7,66; A2:A6; B2:B6)

Поиск значения 7,66 в столбце A, соответствующего ближайшему наименьшему значению (6,39), и возврат значения из столбца B, находящегося в той же строке.

=ПРОСМОТР(0; A2:A6; B2:B6)

Поиск значения 0 в столбце A и возврат значения ошибки, так как 0 меньше наименьшего значения (4,14) в столбце A.

Чтобы эти формулы выводили результат, может потребоваться выделить их на листе Excel и нажать клавишу F2, а затем — ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Форма массива

Совет: Мы настоятельно рекомендуем использовать вместо формы массива функции ВЛП или Г ПРОСМОТР. Посмотрите это видео о ВЛКП, в котором есть примеры. Форма массива функции ПРОСМОТР обеспечивает совместимость с другими программами электронных таблиц, но ее функциональность ограничена.

Форма массива функции ПРОСМОТР просматривает первую строку или первый столбец массив, находит указанное значение и возвращает значение из аналогичной позиции последней строки или столбца массива. Эта форма функции ПРОСМОТР используется, если сравниваемые значения находятся в первой строке или первом столбце массива.

Синтаксис

Функция ПРОСМОТР в форме массива имеет аргументы, указанные ниже.

Искомое_значение. Обязательный аргумент. Значение, которое функция ПРОСМОТР ищет в массиве. Аргумент искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.

Если функции ПРОСМОТР не удается найти искомое_значение, то в массиве выбирается наибольшее значение, которое меньше искомого_значения или равно ему.

Если искомое_значение меньше, чем наименьшее значение в первой строке или первом столбце (в зависимости от размерности массива), то функция ПРОСМОТР возвращает значение ошибки #Н/Д.

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

Форма массива функции ПРОСМОТР очень похожа на функции ГПР и ВПР. Различие заключается в том, что функция ГПР ищет искомое_значение в первой строке, функция ВПР — в первом столбце, а функция ПРОСМОТР выполняет поиск в соответствии с размерностями массива.

Если ширина массива больше его высоты (т. е. есть массив имеет больше столбцов чем строк), то функция ПРОСМОТР ищет искомое_значение в первой строке.

Если высота массива больше его ширины (т. е. массив имеет больше строк, чем столбцов), то функция ПРОСМОТР выполняет поиск в первом столбце.

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

Важно: Значения в массиве должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.

Векторное произведение в EXCEL

history 19 декабря 2015 г.

Группы статей
• Векторы

Найдем векторное произведение 2-х векторов с помощью функций MS EXCEL. Также создадим таблицу для проверки векторов на коллинеарность.

Сначала немного теории. Векторным произведением двух векторов а и b , заданных в прямоугольной системе координат трехмерного пространства, называется такой вектор c , что:

1. он перпендикулярен обоим векторам а и b ;
2. длина векторас равна произведению длин векторов а и b на синус угла между ними;
3. вектор с направлен так, что тройка векторов а , b и с является правой ( с конца вектора с кратчайший поворот от вектора a к вектору b виден наблюдателю против часовой стрелки ).

Почему такое сложное определение? Дело в том, что результатом векторного произведения [ a х b ], в отличие от скалярного , является вектор. А для того, чтобы однозначно определить вектор нужно задать его длину (второй пункт определения) и направление (первый и третий пункты определения).

Векторное произведение двух векторов a = < a _x ; a _y ; a _z > и b = < b _x ; b _y ; b _z > в декартовой системе координат можно вычислить, используя формулы:

или в матричной форме:

Теперь вычислим векторное произведение в MS EXCEL. Встроенная функция к сожалению отсутствует. Кроме того, формула должна возвращать три значения, т.е. 3 координаты вектора. Это может быть реализовано только формулой массива (вариант, когда 3 координаты рассчитываются независимо, с использованием 3-х различных формул, очевиден, но не интересен, хотя и приведен файле примера ).

Пусть даны координаты векторов а и b , записанные в строках 8 и 9 (см. файл примера ).

Обратим внимание, что запись в матричной форме напоминает вычисление обратной матрицы методом алгебраических дополнений . Вместо единичных векторов i, j, k запишем вспомогательный вектор с координатами <1; 1; 1>и поместим его в строке 7 над векторами. Теперь у нас есть квадратная матрица А третьего порядка, для которой можно вычислить обратную матрицу.

Попробуем использовать функцию МОБР() для вычисления векторного произведения. Заметим, что три слагаемых из определения векторного произведения в матричной форме совпадают со значениями верхней строки матрицы алгебраических дополнений.

Примечание : Напомним, что алгебраическое дополнение A _ij вычисляется по формуле A _ij =(-1) i+j *М _ij (где М — соответствующий минор, т.е. определитель, состоящий из элементов матрицы А за исключением всех элементов, расположенных на строке i и в столбце j).

Так как обратная матрица вычисляется по формуле:

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

Это реализовано с помощью формулы массива =ТРАНСП(МОБР(B7:D9))*МОПРЕД(B7:D9)

Коллинеарность векторов

Если два вектора коллинеарны, т.е. лежат на параллельных прямых, то их векторное произведение равно 0. В файле примеров приведена таблица для проверки векторов на коллинеарность.

Нахождение длины вектора с — результата векторного произведения

Из определения векторного произведения длина вектора с равна произведению длин векторов а и b на синус угла между ними.

Примечание : Как вычислить длины векторов по их координатам показано в статье Вычисление длины (модуля) вектора в MS EXCEL .

Синус угла найдем через тригонометрическую формулe sin 2 x+cos 2 x=1

Конечно, можно также сначала найти векторное произведение, а затем длину полученного вектора. Естественно, оба метода расчета дают одинаковые результаты.

Примеры функции ПРОСМОТР для быстрого поиска в диапазоне Excel

Функция ПРОСМОТР в Excel возвращает искомое значение из массива данных, строки либо столбца таблицы. Она позволяет быстро найти искомое значения без необходимости ручного поиска среди больших объемов информации. Особенности использования функции будут указаны ниже в примерах.

Функция ПРОСМОТР в Excel и особенности ее использования

Функция ПРОСМОТР упрощает поиск данных в строке, столбце таблицы и массиве данных наряду с ее аналогами:

Обратите внимание: результат работы функции ПРОСМОТР может оказаться некорректным, если данные в массиве или столбце таблицы не отсортированы в порядке возрастания числового значения или алфавитном порядке. Если сортировка невозможна в силу различных причин, рекомендуется использовать перечисленные выше аналоги данной функции.

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

1. Векторная форма записи. Вектором данных в Excel принято считать диапазон данных, содержащих лишь одну строку либо столбец таблицы. Соответственно, функция ПРОСМОТР используется для поиска определенного значения в одной строке или одном столбце. Синтаксис:

=ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов])

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

Описание версии 3-х аргументов:

1. Искомое_значение – объект числовых, текстовых, ссылочных или логических данных. Функция ПРОСМОТР выполняет поиск значения этих данных в векторе данных.
2. Просматриваемый_вектор – диапазон данных, который представляет собой столбец таблицы или строку. Вектор данных может содержать числовые, текстовые и логические значения. Все значения вектора данных должны быть отсортированы в порядке возрастания величин (А-Я; ЛОЖЬ, ИСТИНА; -2, 0, 103, 1000).
3. Вектор_результатов – необязательный аргумент, представляющий собой диапазон данных из одной строки либо столбца таблицы. Размеры просматриваемого и вектора результатов должны быть тождественны.

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

Все аргументы в данной форме записи являются обязательными.

Описание версии 2-х аргументов:

1. Искомое_значение — объект текстовых, логических, числовых или ссылочных данных, значение которого функция ПРОСМОТР ищет в определенном массиве данных. Если искомое_значение отсутствует в указанном массиве, функция выбирает наибольшее значение из массива, которое меньше или равно искомому. Ошибка #Н/Д будет возвращена, если значение первого элемента массива больше, чем искомое_значение.
2. Массив – массив данных в Excel (текстовые, числовые, логические), сравниваемый с искомым значением. Функция ПРОСМОТР производит поиск в соответствии с размерностями массива, то есть в первой строке либо первом столбце таблицы, если она содержит больше столбцов чем строк либо больше строк чем столбцов соответственно.

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

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

Пример 1. Физик определял ускорение свободного падения эмпирическим путем: с определенной высоты запускал обтекаемый предмет и засекал время, которое требовалось на прохождение пути от точки запуска до момента соприкосновения с поверхностью земли. Затем по формуле g=2S/t2 определял искомую величину. После проведения нескольких опытов были получены следующие результаты:

Необходимо определить, находится ли среди полученных результатов значение, соответствующее общепризнанному значение g=9,81.

Для решения запишем в ячейку D2 следующую формулу:

1. C2 (9,81) – явно введенное искомое значение;
2. B2:B7 – массив данных, среди которых производится поиск.

То есть, среди результатов вычислений находилась искомая величина.

Примечание: значения в столбце Результат не отсортированы в порядке возрастания. Как было сказано ранее, функция возвращает наибольшее значение из массива, которое меньше либо равно искомому. Если бы производился поиск, например, числа 10, в данном случае было бы возвращено значение 9,4, что не является верным результатом (максимальное значение в столбце – 9,5). Для корректной работы функции необходимо выполнить сортировку массива данных.

Вторая версия функции ПРОСМОТР в Excel

Пример 2. В банк обратились 5 клиентов с целью получения кредита на определенные различные между собой суммы. Банк определяет процент за использование кредита с учетом суммы запрошенных средств в долг. Каждый клиент должен вернуть банку сумму денег, которая телу кредита и процентов в пересчете на денежные средства. Введем исходные данные в таблицу:

Задача состоит в поиске процента возврата с учетом зависимости между процентом и суммой кредита, а также вычисление суммы возврата. Определим искомые величины для клиента с фамилией Иванов. Для этого в ячейке C2 введем следующую формулу:

1. B2 – сумма взятого клиентом кредита.
2. $A$12:$A$17 – массив сумм, среди которых производится поиск эквивалентного или ближайшего значения к искомому.
3. $B$12:$B$17 – массив соответствующих процентов.

Примечание: знак «$» использован для «фиксации» ссылок на ячейки.

То есть, Иванову был выдан кредит под 6% годовых.

Для определения суммы возврата введем формулу:

1. B2 – сумма (тело) кредита, взятого Ивановым;
2. B2*C2 – сумма процентов за использование в денежном эквиваленте.

То есть, клиент Иванов обязан вернуть 127,2 денежных единиц. Подобным методом производится расчет задолженности для остальных клиентов.

Пример 3. В офисе работают 5 сотрудников различного возраста. Необходимо найти возраст Виталия.

Внесем исходные данные в таблицу:

Для определения возраста самого младшего сотрудника введем формулу в ячейке E3:

1. D3 – имя сотрудника, возраст которого необходимо определить;
2. A2:A6 – просматриваемый вектор имен;
3. B2:B6 – вектор соответствующих возрастов.

Значит, возраст сотрудника Виталия составляет 43 года.

В данном примере мы ознакомились с двумя версиями функции ПРОСМОТР на 2 и 3 аргумента для заполнения входящими данными.

Функция ПРОСМОТР применяется, когда необходимо из одного столбца/строки найти соответствующее значение в той же позиции (то есть, напротив значения) из другого столбца/строки.

Практическая работа функции ПРОСМОТР в Excel

Как работает функция ПРОСМОТР? Синтаксис функции выглядит следующим образом:

Первый аргумент «Искомое_значение» может быть любого типа в зависимости от данных таблицы и нужного результата. Аргумент «Вектор_результатов» является необязательным, он должен иметь размер, одинаковый с аргументом «Просматриваемый_вектор». Второй вариант синтаксиса используется не так часто и предназначен для совместимости з другими программами. Лучше всего вместо массива использовать функцию ВПР. На первом примере рассмотрим принцип работы ПРОСМОТР и все нюансы выполнения задачи. У нас есть таблица с данными о граничном значении частоты звука и его характеристике. Для начала выполним простейшее задание – нам нужно найти характеристику для частоты 100 Гц. Указываем число «100», затем выбираем диапазон А3:А6, в котором находится искомое значение. Последним этапом указываем В3:В6, из которого возвратим характеристику:

В результате получили характеристику «Низкие частоты», поскольку именно это находится на одной позиции с числом «100» и совпадение произошло с одним из значений. Теперь проверим как работает формула, когда ищет значение, которое не имеет совпадений из данных. Например, укажем искомое число 2000 среди диапазона:

Результат мы все же получили. Формула сработала следующим образом – она нашла характеристику напротив ближайшего к числу «2000» значению в сторону уменьшения. То есть, «2000» попадает в диапазон «1000-4000», но «1000» меньше за «4000», поэтому возвратилось значение, соответствующее цифре «1000». Следующим шагом выберем число, больше за максимальное в столбце «Граница частоты, Гц», например, «5000»:

В ячейке В12 возвратилось «Средние частоты». Логика выполнения очень похожа на предыдущий пример – формула устремилась к меньшему ближайшему числу и выдала соответствующую характеристику. И последний пример – укажем число, которое будем искать, меньшее за минимальное, например, «10»:

Теперь у нас появилась ошибка, поскольку среди данных нет числа, меньшего за «10», формуле не на что опираться, и она возвратила ошибку.



Как использовать функцию ПРОСМОТР для разных задач

В следующем примере у нас будет таблица с продажами фирм в разные периоды. И будут также недели, в которые не было продаж, например, фирма Roche не имела продаж на 6 и 7 неделях:

Наша задача состоит в том, чтобы извлечь те данные, которые были последними по каждой фирме. В случае, если бы таблица была полностью заполнена, информация по свежим продажам всегда была бы в столбце «7 нед». В этом примере работа фирм отличается. Из таблицы можем сделать вывод, что последние продажи фирмы Bayer были на 6 неделе, но фирм много, и для решения этой задачи воспользуемся функцией ПРОСМОТР. Искомое значение должно быть числом, которое будет больше любого имеющегося в таблице. Необязательно вычитывать, какое же число точно будет больше любого из таблицы, просто сразу укажем величину «9999». Затем для аргумента «Просматриваемый вектор» укажем диапазон «В19:Н19»:

Мы получили желаемое значение – информацию о самых свежих продажах, на 5 неделе были продажи объемом 76 условных единиц. Принцип работы формулы следующий: ПРОСМОТР просматривает содержимое каждой ячейки в диапазоне В19:Н19, сравнивая их с искомым значением. Если бы число из таблицы совпало с искомым, функция возвратила бы это число. Поскольку значение каждой следующей ячейки не совпадало идентично, ПРОСМОТР переходила от одной к следующей ячейке и возвратила значение последней проверяемой ячейки. Скопируем формулу до конца столбца и проверим, выполнили ли мы задание:

Как видим, по каждой фирме возвратились данные по последним продажам. Пусть вместо числовых значений у нас будут буквенные:

Принцип работы похожий, только теперь вместо очень большого числа нам нужно указать очень много букв – текст «яяяя». Дело в том, что «большой» текст – это код, который состоит из букв в следующем возрастании:

Заменяем искомый текст, оставляем тот же диапазон и смотрим результат:

ПРОСМОТР так же отлично сработал на поиск последней заполненной ячейке по строке. Копируем формулу до конца столбца, чтобы выполнить задачу:

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

Первым этапом будет создание выпадающего списка из десяти фирм. Для начала убедитесь, что вы находитесь на нужной вам ячейке (клик по ячейке К32»). Переходим на вкладку «Данные», в группе «Работа с данными» выбираем «Проверка вводимых значений». Появится окно, в котором нужно среди элементов «Типа данных» выбрать список, а в «Источнике» указать диапазон. В нашем случае диапазоном будет список десяти фирм К19:К28:

Сейчас возле ячейки К32 должен появиться указатель выпадающего списка, благодаря которому можно выбрать любую из десяти фирм:

Теперь нам нужно организовать автоматическое подтягивание всех данных по выбранной фирме. В ячейке L32 пишем формулу ПРОСМОТР. В качестве искомого значения у нас будет ячейка К32, поскольку она будет меняться, и в зависимости от этих изменений будет меняться и совпадения «Фирма N = Название». Вторым аргументом будет диапазон К19:L28, в котором формула будет просматривать совпадения:

Сейчас видим результат Bayer, поскольку первая таблица содержит информацию, что Фирма 5 – это фирма с названием Bayer. Дальше так же мы строим формулы для прибыли, кода, и страны-партнера:

В формулах, где мы возвратим данные по прибыли, коду и стране-партнера второй аргумент разделится на два – просматриваемый вектор и вектор результатов. Первый будет неизменный – диапазон К19:К28, а второй – будет меняться в зависимости от столбца, из которого нужно возвратить данные. Как видим, по фирме 5 все получилось правильно – все элементы совпали. Теперь выберем любую другую фирму из списка, например, 2:

Сравниваем взглядом все совпадения. Наша таблица исправно выполняет работу. Так же мы можем немного расширить информацию во второй таблице. Пусть нам нужно сверять данные по двум фирмам одновременно. Для этого нам нужно, чтобы сразу две фирмы можно было выбирать и читать их характеристики. Сначала выполняем копирование ячейки К32 на один пункт вниз. Возле ячейки К33 так же должен быть указатель выпадающего списка. Затем копируем так же ячейки L32, M32, N32, O32. Однако, перед копированием нужно указать абсолютные ссылки для диапазонов, иначе формула съедет на одну позицию вниз и у нас будут ошибки:

Теперь совершаем копирование и у нас есть два ряда с выпадающими списками фирм и информацией о них. Выберем фирму 4 и фирму 7:

Скачать пошаговые примеры использования функции ПРОСМОТР в Excel

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

Помогла ли Вам статья?