Mini-ats102.ru

ООО “Мультилайн”
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Использование функции ПРОСМОТР в Excel

Допустим, у нас есть таблица с наименованиями товаров, их ценой, количеством и суммой.

Пример таблицы в Эксель

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

  • Числа: … -2, -1, 0, 1, 2…
  • Буквы: от А до Я, от A до Z и т.д.
  • Логические выражения: ЛОЖЬ, ИСТИНА.

Для упорядочивания информации можно воспользоваться сортировкой.

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

Метод 1: векторная форма

Пользователи Excel чаще всего используют именно этот способ. Вот, в чем он заключается:

  1. Рядом с исходной таблицей создаем еще одну, шапка которой содержит столбцы с названиями “Искомое значение” и “Результат”. На самом деле, это не является обязательным условием, однако, так проще работать с функцией. Названия заголовков, также, могут быть другими.Заготовка таблицы для использования функции ПРОСМОТР в Эксель
  2. Встаем в ячейку, в которой планируем выводить результат, после чего щелкаем по значку “Вставить функцию” слева от строки формул.Вставка функции в ячейку таблицы Эксель
  3. Перед нами появится окно Мастера функций. Здесь мы выбираем категорию “Полный алфавитный перечень”, пролистываем список вниз, находим оператор “ПРОСМОТР”, отмечаем его и жмем OK.Выбор оператора ПРОСМОТР в Мастере функций Эксель
  • “Искомое_значение” – здесь мы указываем координаты ячейки (прописываем вручную или просто щелкаем по нужному элементу в самой таблице), в которую будем вводить параметр, по которому будет выполняться поиск. В нашем случае – это “F2”.
  • “Просматриваемый_вектор” – указываем диапазон ячеек, среди которых будет производиться поиск искомого значения (у нас это “A2:A8”). Здесь мы, также, можем прописать координаты вручную, либо выделить требуемую область ячеек в таблице с помощью зажатой левой кнопки мыши.

Метод 2: форма массива

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

  1. Вставляем в ячейку для вывода результата функцию ПРОСМОТР – как и в первом методе, но теперь выбираем список аргументов для массива.Выбор списка аргументов функции ПРОСМОТР в Excel
  2. Указываем аргументы функции и жмем кнопку OK:
    • “Искомое_значение” – заполняется так же, как для векторной формы.
    • “Массив” – задаем координаты всего массива (или выделяем его в самой таблице), включающего просматриваемый диапазон и область результатов.Заполнение аргументов функции ПРОСМОТР в Excel (форма диапазонов)
  3. Чтобы воспользоваться функцией, как и в первом методе вводим наименование товара и жмем Enter, после чего результат автоматически появится в ячейке с формулой.Пример использования функции ПРОСМОТР в Excel

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

Аппроксимация в Excel статистических данных аналитической функцией.

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

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

1. Включаем Excel и помещаем на лист таблицу с данными статистики.

Таблица Excel со статистическими данными.

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

Зависимость общего выпуска металлоконструкций от количества переработанных уголков

О том, как построить подобную диаграмму, подробно рассказано в статье «Как строить графики в Excel?».

3. «Наводим» мышь на любую из точек на графике и щелчком правой кнопки вызываем контекстное меню (как говорит один мой хороший товарищ — работая в незнакомой программе, когда не знаешь, что делать, чаще щелкай правой кнопкой мыши…). В выпавшем меню выбираем «Добавить линию тренда…».

4. В появившемся окне «Линия тренда» на вкладке «Тип» выбираем «Линейная».

Окно MS Excel "Линия тренда" вкладка "Тип"

5. Далее на вкладке «Параметры» ставим 2 галочки и нажимаем «ОК».

Окно MS Excel "Линия тренда" вкладка "Параметры"

6. На графике появилась прямая линия, аппроксимирующая нашу табличную зависимость.

Аппроксимация табличной зависимости прямой линией

Мы видим кроме самой линии уравнение этой линии и, главное, мы видим значение параметра R 2 – величины достоверности аппроксимации! Чем ближе его значение к 1, тем наиболее точно выбранная функция аппроксимирует табличные данные!

7. Строим линии тренда, используя степенную, логарифмическую, экспоненциальную и полиномиальную аппроксимации по аналогии с тем, как мы строили линейную линию тренда.

Линейная, степенная, логарифмическая, экспоненциальная и полиномиальная аппроксимации

Лучше всех из выбранных функций аппроксимирует наши данные полином второй степени, у него максимальный коэффициент достоверности R 2 .

Однако хочу вас предостеречь! Если вы возьмете полиномы более высоких степеней, то, возможно, получите еще лучшие результаты, но кривые будут иметь замысловатый вид…. Здесь важно понимать, что мы ищем функцию, которая имеет физический смысл. Что это означает? Это означает, что нам нужна аппроксимирующая функция, которая будет выдавать адекватные результаты не только внутри рассматриваемого диапазона значений X, но и за его пределами, то есть ответит на вопрос: «Какой будет выпуск металлоконструкций при количестве переработанных за месяц уголков меньше 45 и больше 168 тонн!» Поэтому я не рекомендую увлекаться полиномами высоких степеней, да и параболу (полином второй степени) выбирать осторожно!

Итак, нам необходимо выбрать функцию, которая не только хорошо интерполирует табличные данные в пределах диапазона значений X=45…168, но и допускает адекватную экстраполяцию за пределами этого диапазона. Я выбираю в данном случае логарифмическую функцию, хотя можно выбрать и линейную, как наиболее простую. В рассматриваемом примере при выборе линейной аппроксимации в excel ошибки будут больше, чем при выборе логарифмической, но не на много.

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

9. В завершении добавим к точкам табличных данных планки погрешностей. Для этого правой кнопкой мыши щелкаем на любой из точек на графике и в контекстном меню выбираем «Формат рядов данных…» и настраиваем данные на вкладке «Y-погрешности» так, как на рисунке ниже.

Окно MS Excel "Формат ряда данных" вкладка "Y-погрешности"

10. Затем щелкаем по любой из линий диапазонов погрешностей правой кнопкой мыши, выбираем в контекстном меню «Формат полос погрешностей…» и в окне «Формат планок погрешностей» на вкладке «Вид» настраиваем цвет и толщину линий.

Окно MS Excel "Формат планок погрешностей" вкладка "Вид"

Аналогичным образом форматируются любые другие объекты диаграммы в Excel!

Окончательный результат диаграммы представлен на следующем снимке экрана.

Аппроксимация табличной зависимости логарифмической кривой

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

Экстраполяция и интерполяция прописаны как варианты основного расчета проектных работ в «Методических указаниях по применению справочников базовых цен на проектные работы в строительстве» 2009 года. Подробнее о формулах расчета в видео ниже.

Рассмотрим наиболее распространённые сложные случаи расчета, которые не относятся к типовым.

Экстраполяция

1. Объем меньше, предусмотренного расценкой из СБЦ, более чем в 2 раза

(Х заданный меньше Х минимального, более чем в 2 раза)

Очень долго единственным вариантом расчета для таких случаев был расчет по трудоемкости. Этот вариант очень неудобен, поскольку документов с нормативной трудоемкостью нет.

В порядке исключения и только в ситуации, когда Х заданный меньше Х минимального более, чем в 2 раза, допускалось считать по разъяснения ЦЕНТРИНВЕСТпроекта с применением понижающего расчетного коэффициента.

Формула в этом случае выглядит так:

  • (A + B * (0.4 * Xмин + 0.6 * (Xмин / 2))) * Количество * Ктек*Кпон
  • А и В- основные показатели из СБЦ
  • Хмин – минимальное значение переменной из СБЦ
  • Количество – количество объектов
  • Ктек- коэффициент перехода в текущие цены
  • Кпон = Х заданный фактический/ Х заданный расчетный

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

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

Планируется также зафиксировать минимальное значение понижающего коэффициента на уровне 0,1.

2. Объем больше, предусмотренного расценкой из СБЦ, более чем в 2 раза.

В декабре 2019г. ЦЕНТРИНВЕСТпроект ответил на этот вопрос. Мы можем пойти четырьмя путями

  • • Расчет по 3П (Неудобен, так как нет нормативов).
  • • Расчет по аналогии возможно использование данные проектной документации, получившей положительное заключения государственной экспертизы, по сметной стоимости проектирования аналогичных систем (система, имеющая функциональные, конструктивные, параметрические сходства). Минус расчета в том, что эти данные надо сначала найти, а они не всегда есть в открытых источниках.
  • • Расчет по экстраполяции с Хзаданным расчетным. Каким бы ни был реальный объем, в смете объем принимается условно равным Х максимальный*2
  • • Разбить весь объем на участки/группы. Х заданный каждого участка не должен превышать Хмаксимальный*2, начиная со второго участка/группы применяется коэффициент привязки (понижающий)

3.В расценке есть только А, в наименовании указано «до…» или «свыше… до…»

Расчет сделан на основе Разъяснения ОАО «ЦЕНТРИНВЕСТ проект» за август 2011года.

В случае, когда реальный объем отличается от предусмотренного расценкой у нас есть 2 варианта расчета.

  • • Рассчитать цену за объект, вне зависимости от объема/количества мест
  • • Рассчитать по экстраполяции в сторону уменьшения

(An + (An-Aпред) / (Хn-Хпред) * (Хзад-Xn) * 0.6) * Ктек при Х>Хn

(A1 — (Aслед-A1) / (Хслед-Х1) * (Х1-Хзад) * 0.6) * Ктек при Х<Х1

Для сравнения в формулах экстраполяции из Методических указаний 2009г. расчет приводится только для двух переменных А и В . не учитывая ситуации, когда есть только показатель А.

Интерполяция

Интерполяция прописана в Методических указаниях 2009г. Пример 2. В примере приводится расчет, но не прописаны формулы. На основании этого расчета мы определили основные схемы.

  • • Если показатель мощности Хзад = Х указанному в расценке, то Ц = A*Ктек*Кст
  • • Если Хn-1<Х<Хn, то Ц = (An-1+((An-An-1)/(Хn-1-Хn)) * (Хзад-Хn-1))*Ктек*Кст
  • • Если Хзад меньше Х1, то Ц = (A1-((A2-A1)/(Х2-Х1)) * (Х1-Хзад)*0,6)*Ктек*Кст
  • • Если Хзад больше Хn, то Ц = (An+((An-A(n-1))/(Хn-Х(n-1)) )* (Хзад-Хn)*0,6)*Ктек*Кст

А-ценовой показатель из СБЦ

Х- величина переменной

Это базовые формулы, но встречаются и особенные расценки, Например, табл.9 СБЦ Коммунальные инженерные сети и сооружения, 2012г. В данном случае следует учитывать 2 переменных, в том числе и для интерполяции

Расчет стоимости проектирования тепловых сетей

Расчет сделан с учетом Разъяснения ОАО «ЦЕНТРИНВЕСТпроект» за ноябрь 2012 г.

Формулы для нетиповых случаев:

Cпред = Aпред + Bпред * Xзад

Cслед = Aслед + Bслед * Xзад

(Cслед — (Сслед — Спред) / (dслед — dпред) * (dслед — d)) * * Ктек

Cпред = Aпред + Bпред * Xзад

Cслед = Aслед + Bслед * Xзад

(Cпред — (Сслед — Спред) / (dслед — dпред) * (dпред — d) * 0.6) * Ктек

Cпред = Aпред + Bпред * Xзад

Cслед = Aслед + Bслед * Xзад

(Cслед + (Сслед — Спред) / (dслед — dпред) * (d — dслед) * 0.6) * Ктек

Примерно половина из более 50 нетиповых расчетов, связаны с расчетами по экстраполяции или интерполяции.

В программе «АДЕПТ: ПРОЕКТ» применение нужной формулы происходит автоматически при проставлении переменной/объема, что избавляет пользователя от ошибок в расчетах, а также от необходимости держать в уме большое количество разъяснений для различных ситуаций.

Все типовые расчеты и ситуации их применения подробно расписаны Методических указаниях 2009. В программе АДЕПТ: ПРОЕКТ эти Методические указания находится в разделе Текстовые документы, подраздел Документы, регламентирующие порядок применения СЦ, СБЦ.

Если мы хотим иметь в виду интерполяцию пропущенных значений, нам нужно сделать это в два этапа. Сначала мы генерируем базовую сетку данных, используяозначать().Это создает сетку с NaNs в качестве значений. После этого мы заполняем NaN интерполированными значениями, вызываяинтерполировать ()метод в столбце прочитанного значения:

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

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

  • Интерполяция
  • Регрессионный анализ

Wikimedia Foundation . 2010 .

Полезное

Смотреть что такое «Интерполяция» в других словарях:

ИНТЕРПОЛЯЦИЯ — 1) способ определять по ряду данных величин какого либо математического выражения промежуточные его величины; так напр., по дальности полета ядра при угле возвышения оси пушечного канала в 1°, 2°, 3°, 4° и т. д. можно определить помощью… … Словарь иностранных слов русского языка

интерполяция — вставка, интерполирование, включение, отыскание Словарь русских синонимов. интерполяция см. вставка Словарь синонимов русского языка. Практический справочник. М.: Русский язык. З. Е. Александрова. 2 … Словарь синонимов

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

Интерполяция — (interpolation) Оценка значения неизвестной величины, находящейся между двумя точками ряда известных величин. Например, зная показатели населения страны, полученные при проведения переписи населения, проводившейся с интервалом в 10 лет, можно… … Словарь бизнес-терминов

Интерполяция — с латинского собственно «подделка». Так называются ошибочные поправки или позднейшие вставки в рукописях, сделанные переписчиками или читателями. Особенно часто этот термин употребляется в критике рукописей античных писателей. В этих рукописях… … Литературная энциклопедия

Интерполяция — нахождение промежуточных значений некоторой закономерности (функции) по ряду известных ее значений. По английски: Interpolation См. также: Преобразования данных Финансовый словарь Финам … Финансовый словарь

интерполяция — и, ж. interpolation f. < лат. interpolatio изменение; переделка, искажение. 1. Вставка позднейшего происхождения в каком л. тексте, не принадлежащая оригиналу. БАС 1. В древних рукописях много интерполяций, внесенных переписчиками. Уш. 1934. 2 … Исторический словарь галлицизмов русского языка

ИНТЕРПОЛЯЦИЯ — (interpolatio), пополнение эмпйрич. ряда значений какой либо величины недостающими промежуточными значениями ее. Интерполирование может быть произведено тремя способами: математич., графич. и логическим. В основе их лежит общая им гипотеза о том … Большая медицинская энциклопедия

ИНТЕРПОЛЯЦИЯ — (от латинского interpolatio изменение, переделка), отыскание промежуточных значений величины по некоторым известным ее значениям. Например, отыскание значений функции y = f(x) в точках x, лежащих между точками x0 и xn, x0 … Современная энциклопедия

ИНТЕРПОЛЯЦИЯ — (от лат. interpolatio изменение переделка), в математике и статистике отыскание промежуточных значений величины по некоторым известным ее значениям. Напр., отыскание значений функции f(x) в точках x, лежащих между точками xo x1 . xn, по… … Большой Энциклопедический словарь

ИНТЕРПОЛЯЦИЯ — в филологии изменение первоначального текста; вставка переписчиком или переводчиком в текст слов или фраз, отсутствовавших в оригинале … Большой Энциклопедический словарь

Задав такой вопрос поисковику, вы получите весьма развёрнутый ответ от Wikipedia. Но в двух словах, интерполяция (интерполирование) — процесс нахождения промежуточных значений по ряду данных, для восполнения пробелов между точными значениями приближенными. Точные значения так же называют узловыми точками.

Линейная интерполяция Matlab

Не будем вдаваться в математические определения и термины, перейдём сразу к сути:

line interpolation

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

В Matlab такой способ реализован с помощью команды
interp1 ( x , y , xi , ‘linear’ ) или просто interp1 ( x , y , xi ) , где x и y массивы из табличных данных (координаты точек), xi — массив промежуточных точек, координаты которых требуется найти.

Интерполяционные полиномы

Есть разные интерполяционные полиномы — функции, определяющие как будут изменяться приближенные значения между узловыми точками:

  • Канонический полином
  • Полином Лагранжа
  • Полином Ньютона

Разберём для самого часто встречающегося полинома реализацию в Matlab. Вот пример использования:

Проинтерполировать функцию sin x на отрезке [1, 9] с шагом 2 и построим графики sin x и полученного интерполяционного полинома.

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

Теперь создайте ещё один файл и запишем в него само решение поставленной задачи:

Ссылки на файлы — исходники сможете найти в конце статьи. Более подробную информацию о полиноме Лагранжа вы сможете найти на официальном сайте Matlab.

Интерполяция сплайнами

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

Вычисление сплайн интерполяции в Matlab осуществляется с помощью команды spline(x, y, xx) , где х и у — массивы табличных данных, а хх — промежуточные значения по оси абцисс (Х). Вот небольшой пример:

Построить интерполяцию сплайнами функции Рунге.

Интерполяция сплайном

Вывод:

interp1

Большинство задач в Matlab реализуются с помощью этой команды. yi = interp1 (x,y,xi, metod), где х – массив абсцисс экспериментальных точек, у – массив ординат экспериментальных точек, xi — точки, в которых необходимо вычислить значения с помощью сплайна, metod – определяет метод построения сплайна. Этот параметр может принимать следующие значения:

  • ‘nearest’ – интерполяция по соседним точкам – этот метод построения кусочной функции, при котором значение в любой точке равно значению в ближайшей узловой точке – интерполяция полиномами 0-ой степени;
  • ‘linear’ – линейная сплайн-интерполяция — интерполяция полиномами 1-ой степени (применяется по умолчанию, если способ интерполирования не задан);
  • ‘cubic’ – интерполяция кубическим полиномом;
  • ‘spline’ – интерполяция кубическим сплайном;
  • ‘pchip’ — интерполяция кубическим эрмитовым сплайном.

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

Как построить спарклайн в таблицах Google

Процесс построения спарклайнов в таблицах Google несколько сложнее, чем в Excel, но по функциональности идентичный. При построении диаграмм в Google Spreadsheets вам доступен тип «Спарклайн». Но это не то, что нам надо. На самом деле это всего лишь тип диаграммы, который позволяет добавить много графиков, но разместить их в одной конкретной ячейке нельзя.

тип диаграммы, который позволяет добавить много графиков

Аналог спарклайнов, описанных в этой статье, в таблицах Google можно построить с помощью функции SPARKLINE, относящейся к группе функций Google. Синтаксис функции SPARKLINE:

  • данные — диапазон или массив, содержащий данные для построения спарклайна;
  • опции — диапазон или массив, содержащий данные о различных параметрах спарклайна.

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

голоса
Рейтинг статьи
Читайте так же:
Можно ли включить невидимку в контакте
Ссылка на основную публикацию
Adblock
detector