Как найти вектор в excel

Рис. 16. Иллюстрация к упражнению 6.2.3.

У
Решение:
пражнение 6.2.4. Вычислить абсолютные отклонения величин в матрицах.

В блок А9:С11 ввести табличную формулу <= abs (A-AО)>.

Пример вычисления определителя матрицы

А, введен­ной в формулу как массив констант: =МОПРЕД(<-73; 78; 24:

Задача 6.2.2 . При каком значении элемента а₃₃ определитель матрицы А обратится в нуль.

Задача 6.2.3. Дана матрица S = . Вычислить матрицу 2SS Т – Е, где Т — операция транспо­нирования,

Е — единичная матрица.

Задача 6.2.4. Вычислить обратную матрицу для

и применить форматирование, чтобы элементы матрицы пред­ставляли собой правильные дроби. Выбрать формат на основе величины определителя матрицы.

 Набор матричных операций в Excel беден.

Если нужно серьезно работать с матрицами, лучше прибегнуть к помощи таких математических пакетов, как MatLAB (Matrix LABoratory), Mathematica, Derive .

Вычисление длины (модуля) вектора в EXCEL

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

Группы статей
• Пользовательский формат
• Векторы

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

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

1. Вычисление длины вектора по его координатам

Если даны координаты вектора в плоской (двухмерной) прямоугольной системе координат, т.е. известны a _x и a _y , то длину вектора можно найти по формуле

В случае вектора в пространстве добавляется третья координата

В MS EXCEL выражение =КОРЕНЬ(СУММКВ(B8:B9)) позволяет вычислить модуль вектора (предполагается, что координаторы вектора введены в ячейки B8:B9 , см. файл примера ).

Функция СУММКВ() возвращает сумму квадратов аргументов, т.е. в данном случае эквивалентна формуле = B8*B8+B9*B9 .

В файле примера также вычислена длина вектора в пространстве.

Альтернативной формулой является выражение =КОРЕНЬ(СУММПРОИЗВ(B8:B9;B8:B9)) .

2. Нахождение длины вектора через координаты точек

Если вектор задан через координаты точек его начала и конца, то формула будет другой =КОРЕНЬ(СУММКВРАЗН(C28:C29;B28:B29))

В формуле предполагается, что координаты точек начала и конца введены в диапазоны C28:C29 и B28:B29 соответственно.

Функция СУММКВРАЗН() в озвращает сумму квадратов разностей соответствующих значений в двух массивах.

По сути, в формуле сначала вычисляются координаты вектора (разности соответствующих координат точек), затем вычисляется сумма их квадратов.

3. Нахождение длины вектора по теореме косинусов

Если требуется найти длину вектора по теореме косинусов, то обычно заданы 2 вектора (их модули и угол между ними).

Найдем длину вектора с используя формулу =КОРЕНЬ(СУММКВ(B43:C43)-2*B43*C43*COS(B45))

В ячейках B43:B43 содержатся длины векторов а и b, а в ячейке В45 – угол между ними в радианах (в долях числа ПИ() ).

Если угол задан в градусах, то формула будет немного отличаться =КОРЕНЬ(B43*B43+C43*C43-2*B43*C43*COS(B46*ПИ()/180))

Примечание : для наглядности в ячейке со значением угла в градусах можно применить пользовательский формат , см. например, статью Отображение широты и долготы в MS EXCEL

4. Нахождение длины вектора через координаты точек треугольника

Пусть заданы 3 точки треугольника, образованного векторами.

Найдем длину вектора ВС через координаты соответствующих точек (аналогично 2-й задаче, рассмотренной выше) по формуле =КОРЕНЬ(СУММКВРАЗН(C54:C55;D54:D55)) .

Зная координаты точек можно найти все длины сторон (длины векторов) и углы треугольника (по теореме косинусов).

5. Нахождение координат вектора через координаты точек

Сделаем в MS EXCEL удобную форму для вычисления координат вектора и его длины через координаты точек. Также отобразим как сами точки, так и сам вектор.

Векторы и матрицы в Excel

Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.

«Снятие эмоционального напряжения
у детей и подростков с помощью арт-практик
и психологических упражнений»

Сертификат и скидка на обучение каждому участнику

Векторы и матрицы в Excel

C овокупность n чисел , заданных в определенном по­рядке, называется n -мерным вектором. Числа a _i – компонент s или координат s вектора, n —размерностью вектора.

Два n -мерных вектора и называются равными, если все их соответствующие компоненты равны: .

Суммой двух n -мерных векторов и называется n -мерный вектор

.

Операция сложения векторов обладает свойствами коммутативности и ассоциативности .

Вектор , все компоненты которого равны нулю, называется нуль-вектором. Нуль-вектор ведет себя при сложения векторов аналогично числу нуль в арифметике.

Вектор называется противоположным вектору . Очевидно,

Операция вычитания векторов определяется как сложение с противоположным вектором .

Под произведением вектора на число  понимают вектор .

Умножение вектора на число обладает свойством ассоциативности и свойством дистрибутивности относительно векторного и числового сомножителей .

Модуль (норма, длина) вектора .

Пример вычисления модуля вектора (2, 5, 3, -4) приведен на рисунке 1.

Р
исунок 1 – Вычисление длины вектора

Здесь применены функция = КОРЕНЬ ( число ), где аргументом функции может быть либо конкретное число, либо адрес ячейки, в которой оно записано, и функция = СУММКВ ( число1 ; число2 ;…), где аргументами функции являются адреса ячеек (адрес массива) с координатами вектора.

В общем случае скалярное произведение двух векторов , где – угол между векторами. Скалярным произведение двух n -мерных векторов и может быть определено как сумма произведений одноименных координат данных векторов:

.

Операция скалярного умножения векторов обладает следующими свойствами:

.

В Excel скалярное произведение векторов вычисляется с помощью функции = СУММПРОИЗВ ( массив1 ; массив2;… ), где массив1 ; массив2;…- от 2 до 30 массивов, чьи компонент нужно перемножить, а затем сложить полученные произведения. Все массивы должны иметь одну и то же размерность (пример на рисунке 2).

Векторным произведением вектора на вектор называется вектор , длина которого численно равна площади параллелограмма построенного на векторах и , перпендикулярный к плоскости этих векторов и направленный так, чтоб наименьшее вращение от к вокруг вектора c осуществлялось против часовой стрелки, если смотреть с конца вектора (рисунок 3).

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

Р
исунок 2 – Определение скалярного произведения двух векторов

Значение векторного произведения определяется следующим образом:

На рисунке 4 приведен пример вычисления векторного произведения векторов, площади параллелограмма, треугольника. Проверка правильности вычисления векторного произведения заключается в проверке соответствия нулю величины скалярных произведений векторов и

Р
исунок 4 – Вычисление векторного произведения векторов

Перейдем к рассмотрению основных операций матричного исчисления.

Числа, расположенные в виде прямоугольной таблицы, состоящей из m строк и n столбцов, образуют матрицу размера m х n :

Две матрицы A и B одного и того же размера m × n являются равными, если равны все их соответствующие элементы:

Матрица, состоящая из одного столбца (т. е. если n = 1) или из од- ной строки (т. е. если m = 1), называется вектором — столбцом или, соответственно, вектором — строкой.

Матрица называется нулевой, если все ее элементы равны нулю. Нулевая матрица обозначается

При n = m матрица называется квадратной, а число ее строк (столбцов) – порядком матрицы. Элементы квадратной матрицы образуют ее главную диагональ.

Квадратная матрица называется треугольной, если все ее элементы, расположенные по одну сторону от главной диагонали, равны нулю:

Квадратная матрица называется единичной, если все элементы ее главной диагонали равны единице, а остальные — нулю:

Если в матрице А заменить строки столбцами, сохранив их порядок, то получится новая матрица

называемая транспонированной по отношению к матрице А.

Если А=А Т , то такая матрица называется симметричной.

В Excel для транспонирования матриц используется функция =ТРАНСП(массив) – рисунок 5.

Р
исунок 5 – Вызов функции ТРАНСП

Пример. Имеем исходную матрицу

.

Из определения ясно, что транспонированной будет матрица А Т :

.

Решение задачи в Excel представлено на рисунке 6

Рисунок 6 – Транспонирование матрицы

Порядок решения следующий:

– определить место для транспонированной матриц (в рассматриваемом примере это G2:I4);

– в ячейку размещения первого элемента транспонированной матрицы ввести формулу =ТРАНС(С2:E5);

– выделить массив ячеек, в которых будут размещаться все элементы транспонированной матрицы;

– нажать Shit + Ctrl + Enter .

Суммой матриц А и В одинакового размера является матрица С , элементы которой равны сумме соответствующих элементов суммируемых матриц:

Произведение матрицы на число  – то матрица, элементы которой получаются умножением всех элементов исходной матрицы на данное число:

Умножение матрицы на матрицу определяется только при условии, что число столбцов первого сомножителя А равно числу строк второго сомножителя В . Под произведением матрицы размером m x k на матрицу размером k x n понимают матрицу размером m x n , элемент которой равен скалярному произведению i -й строки матрицы на j -й столбец матрицы :

В Excel для вычисления произведения матриц используется функция

= МУМНОЖ ( массив1 ; массив2 ), где массивы – совокупности элементов перемножаемых матриц (рисунок 7).

Р
исунок 7 – Умножение матриц

Формула для расчета произведения матриц должна быть введена как формула массива!

Пусть даны матрицы

Вычислим их произведение в Excel (рисунок 8).

– шаг1 – определение области размещения результата (на рисунке 8 выделена пункитом);

– шаг 2 – ввод в начальную ячейку результирующего массива формулы умножения матриц;

–
шаг 3 – выделить результирующий массив и нажать F2;

–
шаг 3 – нажать Shift+Ctrl+Enter.

Рисунок 8 – Вычисление произведения матриц

Действие умножения матрицы на матрицу обладает свойствами:

Отметим, что в общем случае

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

При умножении квадратной матрицы саму на себя получаем квадратную матрицу второй степени, при n -кратном умножении получим квадратную матрицу n -го порядка ( n -й степени).

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

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

Для матрицы 2х2 определитель вычисляется как

Для матриц более высоких порядков n x n определитель можно вычислить, применив следующую рекурсивную формулу:

, где – дополнительный минор к элементу .

Возможно разложение как по строкам, так и по столбцам.

В
Excel определитель вычисляется с помощью функции = МОПРЕД ( массив ), где массив есть совокупность элементов матрицы (рисунок 9).

Рисунок 9 – Расчет определителя матрицы

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

Детерминант треугольной матрицы равен произведению ее диагональных элементов

Обратной матрицей к матрице называют такую матрицу, для которой
А А -1 = E

Обратную матрицу можно найти по следующей формуле:

, где – определитель матрицы, – транспонированная матрица.

Н
а рисунке 10 приведен пример определения обратной матрицы с помощью функции Excel = МОБР ( массив ).

Рисунок 10 – Расчет обратной матрицы

Заметим, что функция применяется к массиву как в ранее приведенных примерах.

Проверим выполнение условия А А -1 = E (рисунок 11)

Р
исунок 11- Произведение матрицы на обратную матрицу

Собственным числом квадратной матрицы

называется такое число , которое обращает определитель матрицы в 0: .

Или, по-другому, собственными числами матрицы А являются корни уравнения и только они.

Матрица называется характеристической матрицей матрицы А , многочлен называется характеристическим многочленом матрицы А , уравнение называется характеристическим уравнением матрицы А.

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

Необходимо найти такие значения  , при котором

Оформим лист Excel следующим образом (рисунок 12):

Рисунок 12 – Вычисление собственного числа матрицы

В ячейку B2 введено =2-F2; в ячейку С3 – =-6-F2; в ячейку D4 – =1-F2.

Из рисунка 12 видно, что при  =0 определитель также равен 0, т.е.  =0 есть первое собственное число матрицы.

Д

ля определения других собственных числе воспользуемся поиском (Меню Сервис-Поск решения …) – рисунок 13, установив целевую ячейку $E$2, в которой вычисляется значение определителя матрицы. Требуемое значение определителя – 0. Поиск осуществляется путем подбора значения  , отображаемом в ячейке $F$2.

Рисунок 13 – Вычисление собственного числа матрицы

О щелчку на кнопке Выполнить, появляется окно Результат поиска решения (рисунок 14).

Рисунок 14 – Результат поиска решения

Выбираем Сохранить найденное решение и Тип отчета – Результаты . Щелкаем на Ок. Получаем ожидаемый результат  =0.

П
овторим выполненные действия, введя в окне Поиск решения ограничение $F$2>=1 (рисунок 15):

Рисунок 15 – Ввод ограничения

В результате поиска получаем второе значение собственного числа:  =3.

Повторим поиск при ограничении.

Если установить в ограничениях  >=4, то поиск не находит решения. Ищем отрицательное собственное число и устанавливаем в ограничениях 

П
ри добавлении в систему ограничений Е1>=-10 (рисунок 16) поиск нашел третье собственное число, равное -6 (рисунок 17)

Р
исунок 16 – Поиск собственного числа при двухстороннем ограничении

Рисунок 17 – Результат поиска третьего собственного числа

Собственным вектором соответствующим собственному числу λ называют такой вектор , который удовлетворяет матричному равенству:

Найдем собственный вектор матрицы

Данная матрица имеет собственные числа: λ1 = 0 λ2 = 3 λ3 = -6.

1. Заносим содержимое ячеек матрицы в ячейки таблицы (B2:D4).

2. В ячейку (B6) вводим λ для которого необходимо найти собственный вектор. Пусть λ = 3.

3. В ячейки (F2:F4) поместим любые числа: F2 = 1; F3 = 1; F4 = 1.

4. В ячейки (G2:G4) заносим произведение матрицы (ячейки В2:В4) на вектор (ячейки F2:F4).

5. В ячейки (H2:H4) заносим умножение столбца на собственное число λ находящийся в ячейки (B6).

6. В ячейки (I2:I4) заносим разность столбцов (F2:F4) и (H2:H4).

7. В главном меню открываем Сервис – Поиск решения . Вводим следующие данные: Целевая ячейка $I$2, Равной значению (0); Изменяя ячейки $F$2:$F$4; Ограничения $I$3=0; $I$4=0.

Нажать кнопку « Выполнить ».

В
ячейках (F2:F4) появятся числа, эти это и есть собственный вектор для данного собственного числа (рисунок 18).

Рисунок 18 – Определение собственного вектора матрицы

Последовательно выполнить операции п.п. 2, 3, 7 при остальных значениях собственных чисел матрицы.

Задания для самостоятельной работы

Повторить решение всех примеров, приведенных в Лекции №5.

Сформировать случайным образом два вектора, состоящих из 5 элементов. Элементы векторов должны быть в диапазоне -5…+15

Определить длину векторов.

Вычислить сумму и разность векторов.

Определить скалярное произведение этих векторов.

Определить угол между векторами.

Определить векторное произведение двух векторов.

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

Сформировать случайным образом матрицу размером 4х4 и матрицу 4х3. Элементы матрицы должны быть в диапазоне -10…+20.

Получить транспонированные матрицы исходных матриц.

Проверить правильность решения путем умножения исходной матрицы на транспонированную.

Определить произведение исходных матриц.

Найти матрицу 3-го порядка для исходной квадратной матрицы.

Определить детерминант исходной квадратной матрицы.

[spoiler title=”источники:”]

http://excel2.ru/articles/vychislenie-dliny-modulya-vektora-v-ms-excel

http://infourok.ru/vektori-i-matrici-v-ecel-1119397.html

[/spoiler]

Найдем векторное произведение 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

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

[

a

х

b

] = {

a

_y

b

_z

–

a

_z

b

_y

;

a

_z

b

_x

–

a

_x

b

_z

;

a

_x

b

_y

–

a

_y

b

_x

}

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

Теперь вычислим векторное произведение в 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

²

x+cos

²

x=1

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

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

Практическая работа функции ПРОСМОТР в 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

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

Содержание

• Применение оператора ПРОСМОТР
  • Способ 1: векторная форма
  • Способ 2: форма массива
• Вопросы и ответы

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

Применение оператора ПРОСМОТР

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

Способ 1: векторная форма

Данный способ наиболее часто применим среди пользователей при использовании оператора ПРОСМОТР.

1. Для удобства строим вторую таблицу с колонками «Искомое значение» и «Результат». Это не обязательно, так как для данных целей можно использовать любые ячейки на листе. Но так будет удобнее.



2. Выделяем ячейку, куда будет выводиться итоговый результат. В ней и будет находиться сама формула. Кликаем по пиктограмме «Вставить функцию».



3. Открывается окно Мастера функций. В перечне ищем элемент «ПРОСМОТР» выделяем его и кликаем по кнопке «OK».



4. Далее открывается дополнительное окно. У других операторов оно редко встречается. Тут нужно выбрать одну из форм обработки данных, о которых шёл разговор выше: векторную или форму массива. Так как мы сейчас рассматриваем именно векторный вид, то выбираем первый вариант. Жмем на кнопку «OK».



5. Открывается окно аргументов. Как видим, у данной функции три аргумента:
   • Искомое значение;
   • Просматриваемый вектор;
   • Вектор результатов.

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

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

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

   В поле «Искомое значение» вводим координаты ячейки, куда будем записывать параметр, по которому будет проводиться поиск. Мы во второй таблице назвали так отдельную ячейку. Как обычно, адрес ссылки прописывается в поле либо вручную с клавиатуры, либо путем выделения соответствующей области. Второй вариант намного удобнее.



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



7. В поле «Вектор результатов» вносятся координаты диапазона, где находятся значения, которые нам нужно найти.





8. После того, как все данные введены, жмем на кнопку «OK».



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

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

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

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

Урок: Мастер функций в Excel

Способ 2: форма массива

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

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



2. Открывается окно аргументов. Как видим, данный подтип функции имеет всего два аргумента – «Искомое значение» и «Массив». Соответственно её синтаксис следующий:

   =ПРОСМОТР(искомое_значение;массив)

   В поле «Искомое значение», как и при предыдущем способе, вписываем координаты ячейки, в которую будет вводиться запрос.



3. А вот в поле «Массив» нужно указать координаты всего массива, в котором находится как просматриваемый диапазон, так и диапазон результатов. При этом, просматриваемый диапазон обязательно должен быть крайней левой колонкой массива, иначе формула будет работать некорректно.



4. После того, как указанные данные введены, жмем на кнопку «OK».



5. Теперь, как и в прошлый раз, для того, чтобы использовать данную функцию, в ячейку для искомого значения вводим одно из наименований просматриваемого диапазона.

Как видим, после этого автоматически в соответствующую область выводится результат.

Внимание! Нужно заметить, что вид формулы ПРОСМОТР для массива является устаревшим. В новых версиях Excel он присутствует, но оставлен только в целях совместимости с документами, сделанными в предыдущих версиях. Хотя использовать форму массива можно и в современных экземплярах программы, рекомендуется вместо этого применять новые более усовершенствованные функции ВПР (для поиска в первом столбце диапазона) и ГПР (для поиска в первой строке диапазона). Они ничем не уступают по функционалу формуле ПРОСМОТР для массивов, но работают более корректно. А вот векторный оператор ПРОСМОТР является актуальным до сих пор.

Урок: Примеры функции ВПР в Эксель

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

Еще статьи по данной теме:

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