Какие функции используются в Excel при работе с векторами
Обновлено: 04.07.2024
Важным шагом к пониманию любой информационной системы является определение ее структурного элемента или наименьшего общего знаменателя.
Какой самый детальный уровень информации нельзя детализировать? Как взаимодействуют эти строительные блоки, формируя более высокие уровни информации?
Ячейка — это основная единица информации в книге Excel. Строительным блоком R является вектор. Теоретически векторы похожи, но в R они используются несколько иначе, чем в Excel.
Ячейки и векторы: пример
Допустим, вы хотите найти наибольшее значение в столбце из пяти чисел.
Excel: одно значение в ячейке
В Excel каждое из этих пяти чисел находится в отдельной ячейке, а в другой ячейке находится функция поиска наибольшего из пяти:
Ячейки являются основным источником информации, и каждая ячейка содержит одно значение.
Теперь проделаем ту же операцию в R.
Векторы: несколько значений в одном
Думайте о векторе как о строке или столбце мини-Excel. В нашем случае этот вектор будет набором из пяти чисел. Но векторы могут принимать и другие формы информации. В Excel мы помещаем одно значение в ячейку. Но вектор R может содержать более одной части информации.
Здесь мы воссоздаем нашу строку из пяти чисел, найденную в электронной таблице.
Функция «c» означает «объединить» и преобразует числа в скобках в вектор. Обратите внимание, что консоль (нижняя половина экрана) показывает результирующий вектор после запуска линии.
Указание вашего вектора на переменную
Чтобы выполнять какие-либо действия с ячейками Excel, просто укажите и щелкните другую ячейку и запишите свое вычисление.
В R рекомендуется указать вектор на переменную, а затем выполнить расчет. Я назову нашу новую переменную «x».
Обратите внимание, что когда я пишу и запускаю переменную "x", я теперь получаю значения вектора.
С этого момента мы на финишной прямой! Функция MAX в R работает так же, как и в Excel.
Эй, должно быть, это сработало — каждый раз мы получали один и тот же ответ!
Но подождите, это еще не все (данные)…
Я надеюсь, что этот урок даст вам представление о том, чем отличается R от Excel. R создан для работы с данными, и вектор — отличный первый способ использовать эту мощь.
Вы хотите выполнять вычисления на основе векторов , такие как векторные перекрестные и скалярные произведения, но не можете найти какие-либо векторные функции в Excel.
Excel не предоставляет никаких встроенных функций для выполнения векторных вычислений; однако вы можете без особых усилий добавить свои собственные функции.
Поскольку VBA позволяет создавать собственные функции, которые затем можно вызывать непосредственно из электронной таблицы Excel, вы можете легко добавлять функции для выполнения векторных вычислений.
На рис. 7-4 показан пример электронной таблицы, содержащей два вектора, u и v, чьи компоненты x, y и z хранятся в отдельных ячейках.
Рисунок 7-4. Пример вектора
В ячейках D7 и E7 показаны величины этих векторов. Эти величины вычисляются с помощью пользовательской функции v_Mag. Синтаксис v_Mag: =v_Mag(диапазон ячеек), где диапазон ячеек – это ссылка на ячейку, состоящая из трех ячеек, содержащих компоненты вектора: x, y и z. . В примере 7-1 показан код VBA для v_Mag. (Дополнительную информацию о написании функций VBA см. в рецепте 2.2.)
Пример 7-1. v_Mag
Единственным параметром этой функции является диапазон ячеек v, представляющий выбранный диапазон ячеек, содержащий три векторных компонента: x, y и z. Первые три строки этой функции объявляют три локальные переменные для хранения компонентов вектора. Вам действительно не нужно использовать локальные переменные в этом примере, но я сделал это, чтобы сделать оставшуюся часть кода более читаемой.
Следующие три строки устанавливают значения локальных переменных в значения, содержащиеся во входном диапазоне ячеек. Поскольку v имеет тип Range, вы можете получить доступ к его свойству Cells, чтобы получить значение для каждой ячейки. Каждая ячейка в диапазоне упоминается с использованием индекса массива, начинающегося с 1, как показано.
Наконец, последняя строка этой функции вычисляет величину вектора и возвращает результат в виде скалярного значения типа Double.
Функция VBA для квадратного корня — Sqr, а функция Excel для квадратного корня — SQRT. Это еще один пример несоответствия между именами функций VBA и Excel.
Вычисление векторного векторного произведения немного сложнее, чем простое вычисление величины вектора.Для перекрестного произведения нам нужно вернуть три компонента результирующего вектора в виде массива в Excel. Это означает, что нам нужно написать функцию VBA, которая возвращает массив вместо скаляра. Результирующая функция будет функцией массива, которую необходимо ввести в электронную таблицу с помощью Ctrl-Shift-Enter точно так же, как встроенные функции массива Excel.
На рис. 7-4 ячейки с D10 по D12 содержат формулу массива с именем v_CrossProduct. Это пользовательская функция, которую я создал для вычисления векторного векторного произведения двух векторов, переданных как диапазоны ячеек. Если вы посмотрите на строку формул на рис. 7.4, то увидите, как используется эта функция; в частности, формула , где фигурные скобки указывают, что функция является функцией массива.
Для входа в эту функцию необходимо выбрать диапазон, состоящий из трех смежных ячеек, ориентированных либо вертикально, либо горизонтально. Функция, которую мы напишем, определит, как ориентированы входные ячейки, и вернет соответствующие результаты.
В примере 7-2 показан код VBA для v_CrossProduct.
Пример 7-2. v_CrossProduct
v_CrossProduct принимает два параметра. Оба являются диапазонами ячеек, которые должны содержать три смежные ячейки, которые сами содержат компоненты x, y и z для каждого вектора. Первые шесть строк этой функции объявляют шесть локальных переменных для хранения компонентов каждого входного вектора.
Предпоследняя локальная переменная, называемая ReturnArray, представляет собой трехэлементный массив, который используется для хранения результата операции векторного векторного произведения. Последняя локальная переменная, DoTranspose, представляет собой логический флаг, который используется для управления транспонированием результирующего вектора при выводе.
Следующий блок кода с комментариями проверяет, является ли выбранный выходной диапазон вектором-строкой или вектором-столбцом. Если выбранный выходной диапазон представляет собой вектор-столбец, то его количество строк будет больше 1, и нам придется транспонировать результирующий вектор. В противном случае выбранный выходной диапазон является вектором-строкой, и нам не нужно транспонировать результат.
Следующий блок кода сохраняет компоненты входных векторов в локальных переменных. Затем он вычисляет перекрестное произведение с каждым компонентом результирующего вектора, сохраненным в трех элементах локального массива ReturnArray, который мы объявили для хранения результата.
Последний шаг — проверка флага DoTranspose. Если оно равно true, то ReturnArray необходимо транспонировать с помощью функции рабочего листа transpose. Если оно ложно, вы можете просто вернуть ReturnArray без дальнейших изменений.
Обсуждаемые здесь методы можно использовать для создания пользовательских функций для выполнения всех обычных векторных операций.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Используйте ПРОСМОТР, одну из функций поиска и ссылок, когда вам нужно просмотреть одну строку или столбец и найти значение из той же позиции во второй строке или столбце.
Например, предположим, что вы знаете номер детали для автомобиля, но не знаете цену. Вы можете использовать функцию ПРОСМОТР, чтобы вернуть цену в ячейке H2, когда вы вводите номер автозапчасти в ячейку H1.
Используйте функцию ПРОСМОТР для поиска в одной строке или одном столбце. В приведенном выше примере мы ищем цены в столбце D.
Советы. Рассмотрите одну из новых функций поиска, в зависимости от используемой версии Office.
Используйте функцию ВПР для поиска в одной строке или столбце или для поиска в нескольких строках и столбцах (например, в таблице). Это значительно улучшенная версия LOOKUP. Посмотрите это видео о том, как использовать функцию ВПР.
Если вы используете Microsoft 365, используйте XLOOKUP — это не только быстрее, но и позволяет выполнять поиск в любом направлении (вверх, вниз, влево, вправо).
Есть два способа использования ПРОСМОТРА: векторная форма и форма массива
Векторная форма. Используйте эту форму ПРОСМОТРА для поиска значения в одной строке или одном столбце. Используйте векторную форму, если вы хотите указать диапазон, содержащий значения, которые вы хотите сопоставить. Например, если вы хотите найти значение в столбце А, перейдите к строке 6.
Форма массива. Мы настоятельно рекомендуем использовать функцию ВПР или ГПР вместо формы массива. Посмотрите это видео об использовании функции ВПР. Форма массива предназначена для совместимости с другими программами для работы с электронными таблицами, но ее функциональные возможности ограничены.
Массив — это набор значений в строках и столбцах (например, в таблице), в которых вы хотите выполнить поиск. Например, если вы хотите выполнить поиск в столбцах A и B, до строки 6. ПРОСМОТР вернет ближайшее совпадение. Чтобы использовать форму массива, ваши данные должны быть отсортированы.
Векторная форма
Векторная форма ПРОСМОТР ищет значение в диапазоне из одной строки или одного столбца (известного как вектор) и возвращает значение из той же позиции во втором диапазоне из одной строки или одного столбца.
Синтаксис
ПРОСМОТР(искомое_значение, искомый_вектор, [результативный_вектор])
Синтаксис векторной формы функции ПРОСМОТР имеет следующие аргументы:
lookup_value Обязательно. Значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
lookup_vector Обязательно. Диапазон, содержащий только одну строку или один столбец. Значения в lookup_vector могут быть текстовыми, числовыми или логическими значениями.
Важно: значения в lookup_vector должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . AZ, ЛОЖЬ, ИСТИНА; в противном случае ПРОСМОТР может не вернуть правильное значение. Текст в верхнем и нижнем регистре эквивалентен.
result_vector Необязательно. Диапазон, содержащий только одну строку или столбец. Аргумент result_vector должен быть того же размера, что и lookup_vector. Он должен быть одинакового размера.
Примечания
Если функция ПРОСМОТР не может найти искомое_значение, функция сопоставляет наибольшее значение в искомом_векторе, которое меньше или равно искомое_значение.
Векторные примеры
Вы можете попробовать эти примеры на своем листе Excel, чтобы узнать, как работает функция ПРОСМОТР. В первом примере вы получите электронную таблицу, похожую на эту:
Скопируйте данные из следующей таблицы и вставьте их в новый лист 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, а затем нажать Enter. При необходимости измените ширину столбцов, чтобы увидеть все данные.
Форма массива
Совет. Мы настоятельно рекомендуем использовать функцию ВПР или ГПР вместо формы массива. Посмотрите это видео о ВПР; он предоставляет примеры. Массивная форма ПРОСМОТРА предоставляется для совместимости с другими программами для работы с электронными таблицами, но ее функциональные возможности ограничены.
Форма массива ПРОСМОТР ищет указанное значение в первой строке или столбце массива и возвращает значение из той же позиции в последней строке или столбце массива. Используйте эту форму ПРОСМОТРА, когда значения, которые вы хотите сопоставить, находятся в первой строке или столбце массива.
Синтаксис
Синтаксис формы массива функции ПРОСМОТР имеет следующие аргументы:
lookup_value Обязательно. Значение, которое функция ПРОСМОТР ищет в массиве. Аргумент lookup_value может быть числом, текстом, логическим значением, именем или ссылкой, которая ссылается на значение.
Если ПРОСМОТР не может найти значение искомое_значение, используется наибольшее значение в массиве, которое меньше или равно искомое_значение.
Обязательный массив. Диапазон ячеек, содержащий текст, числа или логические значения, которые вы хотите сравнить с искомым_значением.
Форма массива функции ПРОСМОТР очень похожа на функции ГПР и ВПР. Разница заключается в том, что ГПР ищет значение искомое_значение в первой строке, ВПР ищет в первом столбце, а ПРОСМОТР ищет в соответствии с размерами массива.
Если массив покрывает область, ширина которой превышает высоту (столбцов больше, чем строк), функция ПРОСМОТР ищет значение искомое_значение в первой строке.
Если массив квадратный или его высота превышает ширину (строк больше, чем столбцов), функция ПРОСМОТР ищет в первом столбце.
С помощью функций ГПР и ВПР вы можете индексировать вниз или поперек, но ПРОСМОТР всегда выбирает последнее значение в строке или столбце.
Важно: значения в массиве должны располагаться в порядке возрастания: . -2, -1, 0, 1, 2, . AZ, ЛОЖЬ, ИСТИНА; в противном случае ПРОСМОТР может не вернуть правильное значение. Текст в верхнем и нижнем регистре эквивалентен.
Некоторое время назад мне было интересно узнать, как создать векторный график в Excel. Лично у меня в этом не было необходимости, но я подумал, что это будет интересная задача и может быть полезной для кого-то еще. Вот что у меня получилось, полностью созданное в Excel:
Создание векторных данных
У меня не было под рукой векторных данных, поэтому мне пришлось что-то придумывать. Для этого упражнения наше векторное поле будет определено следующим образом:
Первым шагом было определение сетки для векторного поля. Я выбрал равномерную сетку размером 0,3 х 0,3. Я также решил для простоты разместить на сетке хвосты каждого вектора, а не среднюю точку. Таким образом, первым шагом было создание двух столбцов для значений x1 и y1 или положения хвостов каждого вектора.
В столбцах, непосредственно следующих за x1 и y1, я создал столбцы x2 и y2. Эти точки будут головкой или стрелкой векторов. Голова вектора должна располагаться в позициях x1+fx и y1+fy. Но эта абсолютная позиция делает векторы ДЕЙСТВИТЕЛЬНО БОЛЬШИМи относительно шага сетки.
Обычно на векторном графике нас интересует относительный размер векторов по сравнению друг с другом. Чтобы решить эту проблему, я добавил коэффициент масштабирования при расчете x2 и y2. В этом случае хорошо работает значение 0,1. Формула для x2 показана ниже.
Если вы пытаетесь построить векторный график из существующих данных, вы можете добавить еще один столбец, чтобы умножить ваши данные на коэффициент масштабирования.
Теперь, когда у нас есть четыре столбца значений, которые полностью определяют векторы, мы можем создать наш векторный график в Excel.
Создание векторного графика в Excel
Векторный график создан на основе точечной диаграммы в Excel. Итак, я начал с того, что вставил пустой лист на лист.
Каждый вектор будет представлен серией данных. Чтобы начать заполнять диаграмму, я щелкнул ее правой кнопкой мыши и выбрал в меню «Выбрать данные». Затем я добавил новый ряд, выбрав первую строку значений x1 и x2 в качестве «значений серии X» и первую строку значений y1 и y2 в качестве «значений серии Y».
Затем я добавил к вектору стрелку, изменив его форматирование. Сначала я выбрал ряд данных, а затем перешел на вкладку «Инструменты для работы с диаграммами» > «Формат», чтобы добавить стрелку.
Теперь у меня на графике есть что-то похожее на вектор. Я также сделал область графика квадратной, чтобы она не искажала векторы.
Осталось повторить этот процесс для каждого вектора. Легко, правда?
У меня есть дела поважнее, чем целый день нажимать кнопки, чтобы добавить все отдельные векторы на эту диаграмму. 🙂 Думаю, вы тоже! Итак, давайте сделаем это немного умнее, не так ли?
Использование VBA для добавления ряда данных в точечную диаграмму Excel
Вместо того, чтобы добавлять каждый вектор вручную, нажимая кнопки в Excel, я создал подпрограмму для добавления каждой серии. VBA для подпрограммы ниже:
- Активирует диаграмму (у меня она называлась «Диаграмма7») и выбирает область графика.
- Запускает цикл "For" для перебора каждой из 49 строк значений.
- Определите значения x как ячейки в текущей строке, а столбцы C и D как строку, которую можно передать в точечную диаграмму.
- Определите значения Y как ячейки в текущей строке и столбцах E и F.
- Добавить на диаграмму новый ряд, используя значения x и значения y
- Добавьте наконечник стрелки.
- Сделать цвет черным
Результат после удаления меток осей и настройки пределов таков:
Похоже на векторный график, не так ли? 😉
Каждая строка значений x и y из таблицы представлена в виде вектора, причем хвост каждого вектора выровнен по заранее заданной сетке. Чтобы сделать векторную диаграмму так, чтобы середина вектора находилась на сетке, мне нужно было бы определить третью точку для каждого вектора.
Что дальше?
Я хочу улучшить векторный график, добавив цветовую шкалу, чтобы цвет векторов менялся в зависимости от их величины. По сути, самые маленькие векторы будут синими, а самые большие — красными. Это потребует некоторой линейной интерполяции со значениями RGB, как показано ниже:
Примечания:
Этот метод будет работать только для векторных графиков, где количество векторов меньше 255, из-за ограничения серии диаграмм Excel. На диаграмме допускается не более 255 рядов данных.
Читайте также: