Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Соединение функций МАССИВ+ТРАНСП+ЛИНЕЙН+СТЕПЕНЬ+СТРОКА - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Соединение функций МАССИВ+ТРАНСП+ЛИНЕЙН+СТЕПЕНЬ+СТРОКА (Формулы/Formulas)
Соединение функций МАССИВ+ТРАНСП+ЛИНЕЙН+СТЕПЕНЬ+СТРОКА
ffpereverzev Дата: Среда, 31.03.2021, 19:36 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Доброго времени суток, уважаемые форумчане!
Помогите пожалуйста разобраться с действием следующей формулы:
Код
{=ТРАНСП(ЛИНЕЙН(E12:G12;СТЕПЕНЬ(E17:G17;СТРОКА($1:$2));ИСТИНА;ЛОЖЬ))}


Диапазон E12:G12 [1000 ; 500 ; 0]
Диапазон E17:G17 [290 ; 310 ; 330]

В приложенном файле указал 3 варианта замены чисел (которые фигурируют у меня в расчетах). При их замене изменяются коэффиценты описанные вышеуказанной формулой. Но я не понимаю как именно она работает.

К сожалению, не могу выложить файл целиком, - политики безопасности не позволяют.

Готов отвечать на наводящие вопросы. :)
К сообщению приложен файл: 1178296.xlsx (15.1 Kb)


Сообщение отредактировал ffpereverzev - Среда, 31.03.2021, 19:55
 
Ответить
СообщениеДоброго времени суток, уважаемые форумчане!
Помогите пожалуйста разобраться с действием следующей формулы:
Код
{=ТРАНСП(ЛИНЕЙН(E12:G12;СТЕПЕНЬ(E17:G17;СТРОКА($1:$2));ИСТИНА;ЛОЖЬ))}


Диапазон E12:G12 [1000 ; 500 ; 0]
Диапазон E17:G17 [290 ; 310 ; 330]

В приложенном файле указал 3 варианта замены чисел (которые фигурируют у меня в расчетах). При их замене изменяются коэффиценты описанные вышеуказанной формулой. Но я не понимаю как именно она работает.

К сожалению, не могу выложить файл целиком, - политики безопасности не позволяют.

Готов отвечать на наводящие вопросы. :)

Автор - ffpereverzev
Дата добавления - 31.03.2021 в 19:36
Pelena Дата: Среда, 31.03.2021, 19:59 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Функция ЛИНЕЙН() даёт коэффициенты a, b и c для квадратичной функции по заданным точкам.
Чтобы увидеть, как работает формула, можно воспользоваться клавишей F9.
Выделите в строке формул часть СТЕПЕНЬ(E17:G17;СТРОКА($1:$2)) и нажмите F9. Увидите {330;310;290:108900;96100;84100} - это те значения, которые ставятся в соответствие с первым рядом {1500;1250;1000}. По ним функция ЛИНЕЙН() рассчитывает коэффициенты
Как разобраться в работе сложной формулы


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Функция ЛИНЕЙН() даёт коэффициенты a, b и c для квадратичной функции по заданным точкам.
Чтобы увидеть, как работает формула, можно воспользоваться клавишей F9.
Выделите в строке формул часть СТЕПЕНЬ(E17:G17;СТРОКА($1:$2)) и нажмите F9. Увидите {330;310;290:108900;96100;84100} - это те значения, которые ставятся в соответствие с первым рядом {1500;1250;1000}. По ним функция ЛИНЕЙН() рассчитывает коэффициенты
Как разобраться в работе сложной формулы

Автор - Pelena
Дата добавления - 31.03.2021 в 19:59
ffpereverzev Дата: Четверг, 01.04.2021, 04:51 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Pelena, спасибо большое за ответ!
Спасибо за подсказку с F9. Попробую разобраться дальше самостоятельно.
 
Ответить
СообщениеPelena, спасибо большое за ответ!
Спасибо за подсказку с F9. Попробую разобраться дальше самостоятельно.

Автор - ffpereverzev
Дата добавления - 01.04.2021 в 04:51
ffpereverzev Дата: Четверг, 01.04.2021, 08:50 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Pelena, здравствуйте еще раз!
Начал разбираться с функцией и вот до чего дошел:

Исходная функция и разбор элементов

Код
{=ТРАНСП(ЛИНЕЙН(A18:C18;СТЕПЕНЬ(A19:C19;СТРОКА($1:$2));ИСТИНА;ЛОЖЬ))}

  • Функция описана как массив {ФОРМУЛА} и занимает 3 ячейки (расчет трех коэффициентов) квадратичной функции;

  • Функция ТРАНСП() для данного уравнения сделана исключительно для удобства, чтобы значения в ячейках располагать не горизонтально, а вертикально;

  • Третий аргумент функции ЛИНЕЙН({};{};ИСТИНА;{}) указывает на необходимость расчета свободного коэффициента C квадратичной функции

    Остались непонятными следующие вопросы

  • Если нажать F9 в основной функции, выделив функцию СТЕПЕНЬ(A19:C19;СТРОКА($1:$2)) то результатом будет =ЛИНЕЙН(A18:C18;{330;310;290:108900;96100;84100};ИСТИНА;ЛОЖЬ). Из этого понятно, что функция СТЕПЕНЬ(A19:C19;СТРОКА($1:$2)) возвела в квадрат все значения из диапазона A19:C19 и объединила их в один диапазон (это видно по символу : , находящемуся между значениями 290:108900. Я верно понимаю данную операцию? И после выполнения данной операции функция ЛИНЕЙН() приобрела следующий вид =ЛИНЕЙН({1500;1250;1000};{330;310;290:108900;96100;84100};ИСТИНА;ЛОЖЬ).

  • Как дальше производится расчет коэффициентов? Понимаю, что, возможно, этот вопрос уже выходит за рамки вопросов по табличному редактору Excel, но, может, кто-нибудь поможет найти ответ?

    В результате расчетов получились значения трех коэффициентов:
    4,21319E-17 12,5 -2625

    Так как функция линейная, то она определяется угловым коэффициентом k и точкой пересечения с осью Y.
    И если использовать функции НАКЛОН() и ОТРЕЗОК(), то как раз получатся следующие значения:
  • Функция =НАКЛОН(A18:C18;A19:C19)возвращает значение 12,5;
  • Функция =ОТРЕЗОК(A18:C18;A19:C19) возвращает значение -2625;

    Осталось понять как функция ЛИНЕЙН() рассчитала вот этот ужас: 4,21319E-17


  • Сообщение отредактировал ffpereverzev - Четверг, 01.04.2021, 09:09
     
    Ответить
    СообщениеPelena, здравствуйте еще раз!
    Начал разбираться с функцией и вот до чего дошел:

    Исходная функция и разбор элементов

    Код
    {=ТРАНСП(ЛИНЕЙН(A18:C18;СТЕПЕНЬ(A19:C19;СТРОКА($1:$2));ИСТИНА;ЛОЖЬ))}

  • Функция описана как массив {ФОРМУЛА} и занимает 3 ячейки (расчет трех коэффициентов) квадратичной функции;

  • Функция ТРАНСП() для данного уравнения сделана исключительно для удобства, чтобы значения в ячейках располагать не горизонтально, а вертикально;

  • Третий аргумент функции ЛИНЕЙН({};{};ИСТИНА;{}) указывает на необходимость расчета свободного коэффициента C квадратичной функции

    Остались непонятными следующие вопросы

  • Если нажать F9 в основной функции, выделив функцию СТЕПЕНЬ(A19:C19;СТРОКА($1:$2)) то результатом будет =ЛИНЕЙН(A18:C18;{330;310;290:108900;96100;84100};ИСТИНА;ЛОЖЬ). Из этого понятно, что функция СТЕПЕНЬ(A19:C19;СТРОКА($1:$2)) возвела в квадрат все значения из диапазона A19:C19 и объединила их в один диапазон (это видно по символу : , находящемуся между значениями 290:108900. Я верно понимаю данную операцию? И после выполнения данной операции функция ЛИНЕЙН() приобрела следующий вид =ЛИНЕЙН({1500;1250;1000};{330;310;290:108900;96100;84100};ИСТИНА;ЛОЖЬ).

  • Как дальше производится расчет коэффициентов? Понимаю, что, возможно, этот вопрос уже выходит за рамки вопросов по табличному редактору Excel, но, может, кто-нибудь поможет найти ответ?

    В результате расчетов получились значения трех коэффициентов:
    4,21319E-17 12,5 -2625

    Так как функция линейная, то она определяется угловым коэффициентом k и точкой пересечения с осью Y.
    И если использовать функции НАКЛОН() и ОТРЕЗОК(), то как раз получатся следующие значения:
  • Функция =НАКЛОН(A18:C18;A19:C19)возвращает значение 12,5;
  • Функция =ОТРЕЗОК(A18:C18;A19:C19) возвращает значение -2625;

    Осталось понять как функция ЛИНЕЙН() рассчитала вот этот ужас: 4,21319E-17

  • Автор - ffpereverzev
    Дата добавления - 01.04.2021 в 08:50
    Pelena Дата: Четверг, 01.04.2021, 09:19 | Сообщение № 5
    Группа: Админы
    Ранг: Местный житель
    Сообщений: 19162
    Репутация: 4412 ±
    Замечаний: ±

    Excel 365 & Mac Excel
    Вы всё совершенно верно поняли respect
    Насчёт самого алгоритма расчёта коэффициентов не подскажу, не разбиралась.

    Функция ЛИНЕЙН() может рассчитать коэффициенты полинома любой степени. Вот здесь есть пример расчёта полинома 6-ой степени

    число 4,21319E-17 - это 4,21319*10^(-17), то есть 10 в степени -17 - совсем маленькое число, близкое к нулю.


    "Черт возьми, Холмс! Но как??!!"
    Ю-money 41001765434816
     
    Ответить
    СообщениеВы всё совершенно верно поняли respect
    Насчёт самого алгоритма расчёта коэффициентов не подскажу, не разбиралась.

    Функция ЛИНЕЙН() может рассчитать коэффициенты полинома любой степени. Вот здесь есть пример расчёта полинома 6-ой степени

    число 4,21319E-17 - это 4,21319*10^(-17), то есть 10 в степени -17 - совсем маленькое число, близкое к нулю.

    Автор - Pelena
    Дата добавления - 01.04.2021 в 09:19
    ffpereverzev Дата: Четверг, 01.04.2021, 09:32 | Сообщение № 6
    Группа: Пользователи
    Ранг: Прохожий
    Сообщений: 6
    Репутация: 0 ±
    Замечаний: 0% ±

    Pelena, Спасибо за ответ!
    число 4,21319E-17 - это 4,21319*10^(-17), то есть 10 в степени -17 - совсем маленькое число, близкое к нулю.

    "Ужасом" я назвал это число не из-за того, что не смог его прочитать, а потому что не понял как оно рассчитывается. Но ведь согласитесь, ужас же :D
    Это ведь 0,0000000000000000421319 !!!

    Спасибо еще раз! Как разберусь, дополню свой ответ полным разбором. Для будущих поколений. :)
     
    Ответить
    СообщениеPelena, Спасибо за ответ!
    число 4,21319E-17 - это 4,21319*10^(-17), то есть 10 в степени -17 - совсем маленькое число, близкое к нулю.

    "Ужасом" я назвал это число не из-за того, что не смог его прочитать, а потому что не понял как оно рассчитывается. Но ведь согласитесь, ужас же :D
    Это ведь 0,0000000000000000421319 !!!

    Спасибо еще раз! Как разберусь, дополню свой ответ полным разбором. Для будущих поколений. :)

    Автор - ffpereverzev
    Дата добавления - 01.04.2021 в 09:32
    Pelena Дата: Четверг, 01.04.2021, 09:49 | Сообщение № 7
    Группа: Админы
    Ранг: Местный житель
    Сообщений: 19162
    Репутация: 4412 ±
    Замечаний: ±

    Excel 365 & Mac Excel
    ужас же

    Это да) Но если этот ужас стоит перед большим числом, возведённым в какую-нибудь степень, то получается довольно значимая поправка. То есть проигнорировать нельзя


    "Черт возьми, Холмс! Но как??!!"
    Ю-money 41001765434816
     
    Ответить
    Сообщение
    ужас же

    Это да) Но если этот ужас стоит перед большим числом, возведённым в какую-нибудь степень, то получается довольно значимая поправка. То есть проигнорировать нельзя

    Автор - Pelena
    Дата добавления - 01.04.2021 в 09:49
    прохожий2019 Дата: Четверг, 01.04.2021, 20:09 | Сообщение № 8
    Группа: Проверенные
    Ранг: Старожил
    Сообщений: 1241
    Репутация: 317 ±
    Замечаний: 0% ±

    365 Beta Channel
    Как дальше производится расчет коэффициентов?
    ну если в общем виде, то обозначим за b вектор коэффициентов в полиноме; X - матрица значений x (несколько столбцов - в степенях от нулевой до старшей в полиноме), Y - матрица (вектор) значений y; XT обозначим транспонированную матрицу, тогда b = ((XT*X)^(-1))*(XT*Y) - ну а как это использовать - курите линейную алгебру
     
    Ответить
    Сообщение
    Как дальше производится расчет коэффициентов?
    ну если в общем виде, то обозначим за b вектор коэффициентов в полиноме; X - матрица значений x (несколько столбцов - в степенях от нулевой до старшей в полиноме), Y - матрица (вектор) значений y; XT обозначим транспонированную матрицу, тогда b = ((XT*X)^(-1))*(XT*Y) - ну а как это использовать - курите линейную алгебру

    Автор - прохожий2019
    Дата добавления - 01.04.2021 в 20:09
    ffpereverzev Дата: Суббота, 03.04.2021, 23:07 | Сообщение № 9
    Группа: Пользователи
    Ранг: Прохожий
    Сообщений: 6
    Репутация: 0 ±
    Замечаний: 0% ±

    Pelena, Здравствуйте!
    Как и обещал, раскладываю формулу:
    Код
    {=ТРАНСП(ЛИНЕЙН(A18:C18;СТЕПЕНЬ(A19:C19;СТРОКА($1:$2));ИСТИНА;ЛОЖЬ))}


    Итак, начнем:
    1. Функция ТРАНСП() - изменяет направление положения ячеек. Если ее не применять, то ячейки со значениями будут расположены горизонтально (в моем случае). Это сделано для удобства, чтобы сэкономить место;
    2. Функция ЛИНЕЙН() - ОЧЕНЬ интересная функция с весьма обширным функционалом. Как сказано в справке "Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую.". Казалось бы, что данная функция применима только к расчетам прямой линии (так, вроде бы сказано в справке, да и само слово ЛИНЕЙН, как бы намекает на прямую линию), но нет!
    Оказывается, та часть функции в которую должны вноситься известные значения Х в данном случае указывает на показатель степени Х при старшем коэффициенте в уравнении. В одном случае у меня это "2", а в другом - "3", а значит, что в случае с "2" - это квадратичная функция, графиком которой является парабола, а в случае с "3" - кубическая парабола.
    Функция ЛИНЕЙН() в моем случае возвращает коэффициенты определителей матрицы (я рассчитал методом Крамера, но можно и другими способами. Какой именно использует функция ЛИНЕЙН() для меня осталось загадкой, да и, в общем-то, уже не так важно).
    Вот такая вот интересная функция!
    Всем спасибо за помощь!
    respect
     
    Ответить
    СообщениеPelena, Здравствуйте!
    Как и обещал, раскладываю формулу:
    Код
    {=ТРАНСП(ЛИНЕЙН(A18:C18;СТЕПЕНЬ(A19:C19;СТРОКА($1:$2));ИСТИНА;ЛОЖЬ))}


    Итак, начнем:
    1. Функция ТРАНСП() - изменяет направление положения ячеек. Если ее не применять, то ячейки со значениями будут расположены горизонтально (в моем случае). Это сделано для удобства, чтобы сэкономить место;
    2. Функция ЛИНЕЙН() - ОЧЕНЬ интересная функция с весьма обширным функционалом. Как сказано в справке "Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую.". Казалось бы, что данная функция применима только к расчетам прямой линии (так, вроде бы сказано в справке, да и само слово ЛИНЕЙН, как бы намекает на прямую линию), но нет!
    Оказывается, та часть функции в которую должны вноситься известные значения Х в данном случае указывает на показатель степени Х при старшем коэффициенте в уравнении. В одном случае у меня это "2", а в другом - "3", а значит, что в случае с "2" - это квадратичная функция, графиком которой является парабола, а в случае с "3" - кубическая парабола.
    Функция ЛИНЕЙН() в моем случае возвращает коэффициенты определителей матрицы (я рассчитал методом Крамера, но можно и другими способами. Какой именно использует функция ЛИНЕЙН() для меня осталось загадкой, да и, в общем-то, уже не так важно).
    Вот такая вот интересная функция!
    Всем спасибо за помощь!
    respect

    Автор - ffpereverzev
    Дата добавления - 03.04.2021 в 23:07
    ffpereverzev Дата: Суббота, 03.04.2021, 23:08 | Сообщение № 10
    Группа: Пользователи
    Ранг: Прохожий
    Сообщений: 6
    Репутация: 0 ±
    Замечаний: 0% ±

    прохожий2019, Здравствуйте!
    Да, я уже разобрался. Спасибо большое за совет!
     
    Ответить
    Сообщениепрохожий2019, Здравствуйте!
    Да, я уже разобрался. Спасибо большое за совет!

    Автор - ffpereverzev
    Дата добавления - 03.04.2021 в 23:08
    Мир MS Excel » Вопросы и решения » Вопросы по Excel » Соединение функций МАССИВ+ТРАНСП+ЛИНЕЙН+СТЕПЕНЬ+СТРОКА (Формулы/Formulas)
    • Страница 1 из 1
    • 1
    Поиск:

    Яндекс.Метрика Яндекс цитирования
    © 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!