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

Вход

Регистрация

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

 

= Мир MS Excel/Относительная ссылка на диапазон ячеек на другом листе - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Относительная ссылка на диапазон ячеек на другом листе (Формулы/Formulas)
Относительная ссылка на диапазон ячеек на другом листе
Ilya62 Дата: Вторник, 18.12.2018, 11:59 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день.
Не смог самостоятельно победить сложившуюся проблему, поэтому я здесь)
Есть следующая задача. Необходимо найти значение в таблице (массиве), соответствующее заданным параметрам. При этом, в случае нахождения искомого значения между двумя существующими, нужно применить метод линейной интерполяции (можно обычной формулой пропорции или ТЕНДЕНЦИЯ или ПРЕДСКАЗ). Задача усложняется тем, что таблица (лист) в которой происходит поиск, определяется по исходным данным, и таких таблиц (листов) очень много. Все таблицы разных размеров, но значения в их столбцах, кроме первого, расположены по убыванию, а в первом столбце - по возрастанию. В каждой таблице есть фиксированные ячейки с адресами начала и конца таблицы.
На сегодняшний день есть файл, в котором всё работает, но в нем не учтена интерполяция. Искомое значение получается ближайшее наибольшее. Все попытки внедрить интерполяцию заканчиваются тем, что Excel ругается на громоздкие формулы.
Помогите пожалуйста оптимизировать и прикрутить интерполяцию.
Буду благодарен за любую подсказку в нужном направлении.
К сообщению приложен файл: 6277214.xlsx (17.3 Kb)


Сообщение отредактировал Ilya62 - Вторник, 18.12.2018, 13:26
 
Ответить
СообщениеДобрый день.
Не смог самостоятельно победить сложившуюся проблему, поэтому я здесь)
Есть следующая задача. Необходимо найти значение в таблице (массиве), соответствующее заданным параметрам. При этом, в случае нахождения искомого значения между двумя существующими, нужно применить метод линейной интерполяции (можно обычной формулой пропорции или ТЕНДЕНЦИЯ или ПРЕДСКАЗ). Задача усложняется тем, что таблица (лист) в которой происходит поиск, определяется по исходным данным, и таких таблиц (листов) очень много. Все таблицы разных размеров, но значения в их столбцах, кроме первого, расположены по убыванию, а в первом столбце - по возрастанию. В каждой таблице есть фиксированные ячейки с адресами начала и конца таблицы.
На сегодняшний день есть файл, в котором всё работает, но в нем не учтена интерполяция. Искомое значение получается ближайшее наибольшее. Все попытки внедрить интерполяцию заканчиваются тем, что Excel ругается на громоздкие формулы.
Помогите пожалуйста оптимизировать и прикрутить интерполяцию.
Буду благодарен за любую подсказку в нужном направлении.

Автор - Ilya62
Дата добавления - 18.12.2018 в 11:59
sboy Дата: Вторник, 18.12.2018, 12:43 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Без проверок на ошибки
Код
=ПРЕДСКАЗ(E3;ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C3")&":"&ДВССЫЛ(C3&"!C4"));;1);ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C3")&":"&ДВССЫЛ(C3&"!C4"));;ПОИСКПОЗ(D3;ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C1")&":"&ДВССЫЛ(C3&"!C2"));))+1))
К сообщению приложен файл: 4998171.xlsx (17.3 Kb)


Яндекс: 410016850021169

Сообщение отредактировал sboy - Вторник, 18.12.2018, 12:46
 
Ответить
СообщениеДобрый день.
Без проверок на ошибки
Код
=ПРЕДСКАЗ(E3;ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C3")&":"&ДВССЫЛ(C3&"!C4"));;1);ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C3")&":"&ДВССЫЛ(C3&"!C4"));;ПОИСКПОЗ(D3;ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C1")&":"&ДВССЫЛ(C3&"!C2"));))+1))

Автор - sboy
Дата добавления - 18.12.2018 в 12:43
Ilya62 Дата: Вторник, 18.12.2018, 12:54 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
sboy, спасибо за вариант.
Кажется где-то ошибка с выбором нужного столбца. Сейчас попробую разобраться.
 
Ответить
Сообщениеsboy, спасибо за вариант.
Кажется где-то ошибка с выбором нужного столбца. Сейчас попробую разобраться.

Автор - Ilya62
Дата добавления - 18.12.2018 в 12:54
sboy Дата: Вторник, 18.12.2018, 13:02 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
где-то ошибка с выбором

я уже поправил, файл перевложил


Яндекс: 410016850021169
 
Ответить
Сообщение
где-то ошибка с выбором

я уже поправил, файл перевложил

Автор - sboy
Дата добавления - 18.12.2018 в 13:02
Ilya62 Дата: Вторник, 18.12.2018, 13:18 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
sboy, спасибо, работает.
Но теперь появились очень большие отклонения при изменении значения в ячейке "Группа".
К сообщению приложен файл: 8261038.xlsx (17.3 Kb)


Сообщение отредактировал Ilya62 - Вторник, 18.12.2018, 13:21
 
Ответить
Сообщениеsboy, спасибо, работает.
Но теперь появились очень большие отклонения при изменении значения в ячейке "Группа".

Автор - Ilya62
Дата добавления - 18.12.2018 в 13:18
Светлый Дата: Вторник, 18.12.2018, 13:41 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
У меня получилась массивная формула:
Код
=СУММ((ТРАНСП(МУМНОЖ(ТРАНСП(Ч(+СМЕЩ(ДВССЫЛ(C3&"!b6");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));;2;20)));{-1:1}))*(E3-ПРОСМОТР(E3;ДВССЫЛ(C3&"!A7:A99")))/(СМЕЩ(ДВССЫЛ(C3&"!A7");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));)-ПРОСМОТР(E3;ДВССЫЛ(C3&"!A7:A99")))+СМЕЩ(ДВССЫЛ(C3&"!b6");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));;1;20))*(ДВССЫЛ(C3&"!b6:u6")=D3))
Пока не оптимизировал.
*Оптимизировал:
Код
=ПРЕДСКАЗ(E3;СМЕЩ(ДВССЫЛ(C3&"!A7:A99");;КОДСИМВ(D3)-64);ДВССЫЛ(C3&"!A7:A99"))
**Или так:
Код
=ПРЕДСКАЗ(E3;СМЕЩ(ДВССЫЛ(C3&"!A7");;КОДСИМВ(D3)-64;93);ДВССЫЛ(C3&"!A7:A99"))


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 18.12.2018, 14:02
 
Ответить
СообщениеУ меня получилась массивная формула:
Код
=СУММ((ТРАНСП(МУМНОЖ(ТРАНСП(Ч(+СМЕЩ(ДВССЫЛ(C3&"!b6");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));;2;20)));{-1:1}))*(E3-ПРОСМОТР(E3;ДВССЫЛ(C3&"!A7:A99")))/(СМЕЩ(ДВССЫЛ(C3&"!A7");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));)-ПРОСМОТР(E3;ДВССЫЛ(C3&"!A7:A99")))+СМЕЩ(ДВССЫЛ(C3&"!b6");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));;1;20))*(ДВССЫЛ(C3&"!b6:u6")=D3))
Пока не оптимизировал.
*Оптимизировал:
Код
=ПРЕДСКАЗ(E3;СМЕЩ(ДВССЫЛ(C3&"!A7:A99");;КОДСИМВ(D3)-64);ДВССЫЛ(C3&"!A7:A99"))
**Или так:
Код
=ПРЕДСКАЗ(E3;СМЕЩ(ДВССЫЛ(C3&"!A7");;КОДСИМВ(D3)-64;93);ДВССЫЛ(C3&"!A7:A99"))

Автор - Светлый
Дата добавления - 18.12.2018 в 13:41
sboy Дата: Вторник, 18.12.2018, 13:42 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
очень большие отклонения

Спешка :) х и у местами перепутал
Код
=ПРЕДСКАЗ(E3;ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C3")&":"&ДВССЫЛ(C3&"!C4"));;ПОИСКПОЗ(D3;ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C1")&":"&ДВССЫЛ(C3&"!C2"));))+1);ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C3")&":"&ДВССЫЛ(C3&"!C4"));;1))
К сообщению приложен файл: 6198192.xlsx (17.5 Kb)


Яндекс: 410016850021169
 
Ответить
Сообщение
очень большие отклонения

Спешка :) х и у местами перепутал
Код
=ПРЕДСКАЗ(E3;ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C3")&":"&ДВССЫЛ(C3&"!C4"));;ПОИСКПОЗ(D3;ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C1")&":"&ДВССЫЛ(C3&"!C2"));))+1);ИНДЕКС(ДВССЫЛ(C3&"!"&ДВССЫЛ(C3&"!C3")&":"&ДВССЫЛ(C3&"!C4"));;1))

Автор - sboy
Дата добавления - 18.12.2018 в 13:42
Ilya62 Дата: Вторник, 18.12.2018, 14:00 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Светлый, спасибо. Сначала не понял, что это массив.
Сейчас протестирую оба предложенных варианта.
 
Ответить
СообщениеСветлый, спасибо. Сначала не понял, что это массив.
Сейчас протестирую оба предложенных варианта.

Автор - Ilya62
Дата добавления - 18.12.2018 в 14:00
Ilya62 Дата: Вторник, 18.12.2018, 14:01 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
sboy, я тоже до этого уже дошел) Отличный вариант получился. Проверю в реальных условиях.
 
Ответить
Сообщениеsboy, я тоже до этого уже дошел) Отличный вариант получился. Проверю в реальных условиях.

Автор - Ilya62
Дата добавления - 18.12.2018 в 14:01
Светлый Дата: Вторник, 18.12.2018, 14:34 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
С Нью-йорком работать не хочет. Пришлось исправить:
Код
=ПРЕДСКАЗ(E3;СМЕЩ(ДВССЫЛ("'"&C3&"'!A7");;КОДСИМВ(D3)-64;93);ДВССЫЛ("'"&C3&"'!A7:A99"))


Программировать проще, чем писать стихи.
 
Ответить
СообщениеС Нью-йорком работать не хочет. Пришлось исправить:
Код
=ПРЕДСКАЗ(E3;СМЕЩ(ДВССЫЛ("'"&C3&"'!A7");;КОДСИМВ(D3)-64;93);ДВССЫЛ("'"&C3&"'!A7:A99"))

Автор - Светлый
Дата добавления - 18.12.2018 в 14:34
Ilya62 Дата: Вторник, 18.12.2018, 14:37 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
sboy, Ваш вариант очень хорош.
Но если в таблицах значения стоят не в линейной последовательности, то формула считает криво, приводя все данные к одной линейной зависимости. И как результат искомое значение может даже выйти из интервала соседних значений. Скорее всего так работает функция ПРЕДСКАЗ. Думаю, что для этой функции нужно конкретно определять интервал поиска по соседним значениям, а не по всей таблице. Но как это сделать пока не понял. Или считать обычной формулой с пропорцией по соседним точкам. Но при этом опять же сильно раздувается конечная формула.
Изменил исходные данные на вкладке "Москва" для наглядности.
К сообщению приложен файл: 1665426.xlsx (25.3 Kb)
 
Ответить
Сообщениеsboy, Ваш вариант очень хорош.
Но если в таблицах значения стоят не в линейной последовательности, то формула считает криво, приводя все данные к одной линейной зависимости. И как результат искомое значение может даже выйти из интервала соседних значений. Скорее всего так работает функция ПРЕДСКАЗ. Думаю, что для этой функции нужно конкретно определять интервал поиска по соседним значениям, а не по всей таблице. Но как это сделать пока не понял. Или считать обычной формулой с пропорцией по соседним точкам. Но при этом опять же сильно раздувается конечная формула.
Изменил исходные данные на вкладке "Москва" для наглядности.

Автор - Ilya62
Дата добавления - 18.12.2018 в 14:37
Ilya62 Дата: Вторник, 18.12.2018, 14:39 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Цитата Светлый, 18.12.2018 в 14:34, в сообщении № 10 ()
С Нью-йорком работать не хочет

Этот косяк исправил. Заменил "-" на "_".
 
Ответить
Сообщение
Цитата Светлый, 18.12.2018 в 14:34, в сообщении № 10 ()
С Нью-йорком работать не хочет

Этот косяк исправил. Заменил "-" на "_".

Автор - Ilya62
Дата добавления - 18.12.2018 в 14:39
Ilya62 Дата: Вторник, 18.12.2018, 14:47 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Светлый, Ваш вариант ещё изящнее, но, к сожалению, работает только в таблицах (массивах) с линейным распределением значений.
К сообщению приложен файл: 9423537.xlsx (25.4 Kb)


Сообщение отредактировал Ilya62 - Вторник, 18.12.2018, 14:50
 
Ответить
СообщениеСветлый, Ваш вариант ещё изящнее, но, к сожалению, работает только в таблицах (массивах) с линейным распределением значений.

Автор - Ilya62
Дата добавления - 18.12.2018 в 14:47
Светлый Дата: Вторник, 18.12.2018, 17:02 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
линейным распределением значений
Думаю, что получилось устранить:
Код
=ПРЕДСКАЗ(E3;СМЕЩ(ДВССЫЛ(C3&"!A6");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));КОДСИМВ(D3)-64;2);СМЕЩ(ДВССЫЛ(C3&"!A6:A7");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));))
*
Код
=ПРЕДСКАЗ(E3;СМЕЩ(ДВССЫЛ(C3&"!A6");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));КОДСИМВ(D3)-64;2);СМЕЩ(ДВССЫЛ(C3&"!A6");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));;2))


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 18.12.2018, 17:08
 
Ответить
Сообщение
линейным распределением значений
Думаю, что получилось устранить:
Код
=ПРЕДСКАЗ(E3;СМЕЩ(ДВССЫЛ(C3&"!A6");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));КОДСИМВ(D3)-64;2);СМЕЩ(ДВССЫЛ(C3&"!A6:A7");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));))
*
Код
=ПРЕДСКАЗ(E3;СМЕЩ(ДВССЫЛ(C3&"!A6");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));КОДСИМВ(D3)-64;2);СМЕЩ(ДВССЫЛ(C3&"!A6");ПОИСКПОЗ(E3;ДВССЫЛ(C3&"!A7:A99"));;2))

Автор - Светлый
Дата добавления - 18.12.2018 в 17:02
Ilya62 Дата: Вторник, 18.12.2018, 17:56 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Всем огромное спасибо!
Проблема решена общими усилиями.
Скомпоновал из предложенных вариантов то, что мне больше подходит.
В формулах есть немного прямых ссылок, которые в моем случае не желательны, но с этим я уже справлюсь сам.
Ещё раз благодарю всех неравнодушных)
К сообщению приложен файл: 7843597.xlsx (26.8 Kb)


Сообщение отредактировал Ilya62 - Вторник, 18.12.2018, 18:30
 
Ответить
СообщениеВсем огромное спасибо!
Проблема решена общими усилиями.
Скомпоновал из предложенных вариантов то, что мне больше подходит.
В формулах есть немного прямых ссылок, которые в моем случае не желательны, но с этим я уже справлюсь сам.
Ещё раз благодарю всех неравнодушных)

Автор - Ilya62
Дата добавления - 18.12.2018 в 17:56
Ilya62 Дата: Среда, 19.12.2018, 09:31 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Заменил все ссылки на относительные.
Ещё раз всем спасибо!
К сообщению приложен файл: 7734659.xlsx (27.1 Kb)
 
Ответить
СообщениеЗаменил все ссылки на относительные.
Ещё раз всем спасибо!

Автор - Ilya62
Дата добавления - 19.12.2018 в 09:31
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Относительная ссылка на диапазон ячеек на другом листе (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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