Не находит значения за Z столбцом в формуле.

Старая форма входа

Страница 1 из 1 1
Модератор форума: _Boroda_, китин

Не находит значения за Z столбцом в формуле. (Формулы/Formulas)

timurhabibulin1993

Дата: Суббота, 25.03.2023, 14:38 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

2010 / Google excel

Добрый день. Помогите разобраться в Google Excel.
Это калькулятор для снайперских винтовок (уложенная реалистичная игра) где я вбиваю известные табличные значения поправок с шагом в 0,50,100,150 метров или в 0,100,200,300 метров и калькулятор вычисляет точные значения исходя из вводимых мною значений.
Я не силен в ексель, и эта формула написана не мною, остальное мое. Пытался разобраться в ее сути работы но так и не смог.

Ошибка находится в ячейках B18 и B19
Формула работает отлично до Z столбца.
Далее правее любые вновь созданные столбцы не распознает и выдает ошибку.

Пример как воспроизвести ошибку:
Выбираю в B2 значение "Правее Z столбца"
в ячейке (B4) дальность указано "1033".

"Ошибка
В диапазоне функции VLOOKUP значение "1033" не найдено."
С другими значениями находящимися левее столбца Z все работает как надо.

Опишу как работает формула на ячейке B18 (приблизительно но может быть немного по другому):
Находит по значению B2 в 1 строке ячейку, и находит столбец ниже ее, и исходя из дальности (B4) находит ячейки которые ближе всего по значению (к примеру дальность стоит 1033м она найдет ячейку 1000 и 1100), далее она находит ячейки в тех же строках но правее ( т.е. в той же строке 1000 и 1100 она правее берет значения к примеру это будет 7 и 8 (это поправки щелчки милдоты для винтовки)), далее она выясняет среднею цифру между этими двумя ячейками исходя из дальности из ячейки B2 ( в ячейке B2 вбито дальность 1033 , формула вытаскивает из него 33 и выводим ее в проценты по отнашению между 7 и 8, и прибавляем к меньшему это 7, и получаем 7.33 это главный ответ формулы)
Обращался к нейросетки за советом он сказал что надо изменить ВПР на Индекс "ВПР($B$25,ДВССЫЛ(СЦЕПИТЬ(REGEXEXTRACT(АДРЕС(2,ПОИСКПОЗ($B$2,$1:$1,0),2)"
ничего не помогало... моих знаний тут явно не хватит.

Далее опишу формулу B19:
Она делает все тоже самое что и B18 вначале но вместо того чтобы цеплять соседние правые ячейки она цепляет ячейки справо через одну ( там находятся поправки по ветру) к примеру дистанция 1033м в B4 и формула находит строки 1000 и 1100 и цепляет через одну вправо ячейки, к примеру это 11.3 и 12.125, и так же как и в B18 она выясняет среднее число исходи из 11.3 и 12.125 где из 1033 вытаскиваем 33 и выясняем сколько она процентов имеет между 1000-1100 (0-100 , бывает таблицы где с шагом в 50,100,150,200,250 .. и тут уже это пригодится) и получается ответ 11.57. Далее эти 11.57 делится на 4 (так надо) и умножается на силу ветра B7.

P.S. к объяснением выше.
На самом деле формула берет ветер и дальность из B25 и B26 но там уже другие формулы которые к теме не относятся.
Все табличные значения (которые правее находятся от формулы созданы для примера)

Эту можете скопировать себе на гугл таблицу и изменять:
Используйте для ввода данных только B2 , B4 ! Другие не трогайте а то данные будут другие
Ссылка на гугл таблицу (открыт для комментирования): Ссылка гугл ексель для скачивания

В файловый вариант ексель вывести формулу не удалось, версии разные.

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

Заранее Спасибо!
Человеку который исправит или поможет исправить обе ячейки скину на сникерс или пачку сиг ^_^
(объяснялки не обязательны, но если будут то доходчивые, я очень тугой в ексель если что =) )

Сообщение отредактировал timurhabibulin1993 - Суббота, 25.03.2023, 14:53

Ответить

СообщениеДобрый день. Помогите разобраться в Google Excel.
Это калькулятор для снайперских винтовок (уложенная реалистичная игра) где я вбиваю известные табличные значения поправок с шагом в 0,50,100,150 метров или в 0,100,200,300 метров и калькулятор вычисляет точные значения исходя из вводимых мною значений.
Я не силен в ексель, и эта формула написана не мною, остальное мое. Пытался разобраться в ее сути работы но так и не смог.

Ошибка находится в ячейках B18 и B19
Формула работает отлично до Z столбца.
Далее правее любые вновь созданные столбцы не распознает и выдает ошибку.

Пример как воспроизвести ошибку:
Выбираю в B2 значение "Правее Z столбца"
в ячейке (B4) дальность указано "1033".

"Ошибка
В диапазоне функции VLOOKUP значение "1033" не найдено."
С другими значениями находящимися левее столбца Z все работает как надо.

Опишу как работает формула на ячейке B18 (приблизительно но может быть немного по другому):
Находит по значению B2 в 1 строке ячейку, и находит столбец ниже ее, и исходя из дальности (B4) находит ячейки которые ближе всего по значению (к примеру дальность стоит 1033м она найдет ячейку 1000 и 1100), далее она находит ячейки в тех же строках но правее ( т.е. в той же строке 1000 и 1100 она правее берет значения к примеру это будет 7 и 8 (это поправки щелчки милдоты для винтовки)), далее она выясняет среднею цифру между этими двумя ячейками исходя из дальности из ячейки B2 ( в ячейке B2 вбито дальность 1033 , формула вытаскивает из него 33 и выводим ее в проценты по отнашению между 7 и 8, и прибавляем к меньшему это 7, и получаем 7.33 это главный ответ формулы)
Обращался к нейросетки за советом он сказал что надо изменить ВПР на Индекс "ВПР($B$25,ДВССЫЛ(СЦЕПИТЬ(REGEXEXTRACT(АДРЕС(2,ПОИСКПОЗ($B$2,$1:$1,0),2)"
ничего не помогало... моих знаний тут явно не хватит.

Далее опишу формулу B19:
Она делает все тоже самое что и B18 вначале но вместо того чтобы цеплять соседние правые ячейки она цепляет ячейки справо через одну ( там находятся поправки по ветру) к примеру дистанция 1033м в B4 и формула находит строки 1000 и 1100 и цепляет через одну вправо ячейки, к примеру это 11.3 и 12.125, и так же как и в B18 она выясняет среднее число исходи из 11.3 и 12.125 где из 1033 вытаскиваем 33 и выясняем сколько она процентов имеет между 1000-1100 (0-100 , бывает таблицы где с шагом в 50,100,150,200,250 .. и тут уже это пригодится) и получается ответ 11.57. Далее эти 11.57 делится на 4 (так надо) и умножается на силу ветра B7.

P.S. к объяснением выше.
На самом деле формула берет ветер и дальность из B25 и B26 но там уже другие формулы которые к теме не относятся.
Все табличные значения (которые правее находятся от формулы созданы для примера)

Эту можете скопировать себе на гугл таблицу и изменять:
Используйте для ввода данных только B2 , B4 ! Другие не трогайте а то данные будут другие
Ссылка на гугл таблицу (открыт для комментирования): Ссылка гугл ексель для скачивания

В файловый вариант ексель вывести формулу не удалось, версии разные.

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

Заранее Спасибо!
Человеку который исправит или поможет исправить обе ячейки скину на сникерс или пачку сиг ^_^
(объяснялки не обязательны, но если будут то доходчивые, я очень тугой в ексель если что =) )

Автор - timurhabibulin1993
Дата добавления - 25.03.2023 в 14:38

Pelena

Дата: Суббота, 25.03.2023, 16:28 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 19403

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Здравствуйте.
Файл по ссылке недоступен для редактирования, поэтому самостоятельно попробуйте в формулах в ячейках В18 и В19 везде заменить "\w" на "\w+"

Цитата timurhabibulin1993, 25.03.2023 в 14:38, в сообщении № 1 (

)

немного описать что именно в этой формуле не так

из адреса ячейки в качестве столбца берется первая буква, а после столбца Z нужно уже две буквы АА, АВ и т.д.

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

timurhabibulin1993

Дата: Суббота, 25.03.2023, 17:10 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

2010 / Google excel

Цитата Pelena, 25.03.2023 в 16:28, в сообщении № 2 (

)

Здравствуйте.
Файл по ссылке недоступен для редактирования, поэтому самостоятельно попробуйте в формулах в ячейках В18 и В19 везде заменить "\w" на "\w+"

Спасибо помогло!

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

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

Сообщение отредактировал timurhabibulin1993 - Суббота, 25.03.2023, 17:18

Ответить

Gustav

Дата: Суббота, 25.03.2023, 17:28 | Сообщение № 4

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

В первую очередь согласимся, что формулу на почти 1500 символов в ячейке B18 в её исходном виде сможет конструктивно проанализировать разве что персонаж Дастина Хоффмана в фильме "Человек дождя" (он там играет гения-аутиста):
[vba]

Код

=ROUND((INDEX(INDIRECT(CONCATENATE(REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"),"2:",REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0)+2,2),"\w"))),MATCH(VLOOKUP($B$25,INDIRECT(CONCATENATE(REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"),"2:",REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"))),1),INDIRECT(CONCATENATE(REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"),"2:",REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"))),0)+1,2)-VLOOKUP($B$25,INDIRECT(CONCATENATE(REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"),"2:",REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0)+1,2),"\w"))),2))*(($B$25-VLOOKUP($B$25,INDIRECT(CONCATENATE(REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"),"2:",REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"))),1))/(INDEX(INDIRECT(CONCATENATE(REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"),"2:",REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"))),MATCH(VLOOKUP($B$25,INDIRECT(CONCATENATE(REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"),"2:",REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"))),1),INDIRECT(CONCATENATE(REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"),"2:",REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"))),0)+1,1)-VLOOKUP($B$25,INDIRECT(CONCATENATE(REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"),"2:",REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"))),1)))+VLOOKUP($B$25,INDIRECT(CONCATENATE(REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0),2),"\w"),"2:",REGEXEXTRACT(ADDRESS(2,MATCH($B$2,$1:$1,0)+1,2),"\w"))),2),2)

[/vba]
Однако, слава Богу, у нас в распоряжении теперь есть новые мощные табличные функции и, в частности, функция LET, с помощью которой попробуем записать исходное "безобразие" в более компактном виде, пригодном для эффективного анализа (пришлось несколько раз поработать в Блокноте заменой по Ctrl+H; "несколько" - это по числу созданных переменных внутри функции):
[vba]

Код

=LET(
адрес,    ADDRESS(2,MATCH($B$2,$1:$1,0),2),
адрес1,   ADDRESS(2,MATCH($B$2,$1:$1,0)+1,2),
адрес2,   ADDRESS(2,MATCH($B$2,$1:$1,0)+2,2),
регэкс,   REGEXEXTRACT(адрес,"\w"),
регэкс1,  REGEXEXTRACT(адрес1,"\w"),
регэкс2,  REGEXEXTRACT(адрес2,"\w"),
индир,    INDIRECT(CONCATENATE(регэкс,"2:",регэкс)),
индир1,   INDIRECT(CONCATENATE(регэкс,"2:",регэкс1)),
индир2,   INDIRECT(CONCATENATE(регэкс,"2:",регэкс2)),
впр25,    VLOOKUP($B$25,индир,1),
впр251,   VLOOKUP($B$25,индир1,2),
матч25,   MATCH(впр25,индир,0)+1,
финал,    ROUND((INDEX(индир2,матч25,2)-впр251)*(($B$25-впр25)/(INDEX(индир,матч25,1)-впр25))+впр251,2),

финал
)

[/vba]
А дальше, подставляя вместо последнего слова "финал" другие переменные, довольно быстро удалось понять, что шаблон регулярного выражения "\w" вытаскивает только первую букву из адреса, чем и объясняется "непроходимость" алгоритма правее колонки Z: из колонки, например, AB вытаскивалась только первая буква A.

ЛЕЧЕНИЕ: добавить в шаблон звездочку после буквы w, т.е. превратить его в "\w*".

Можете воспользоваться для ячейки B18 моей короткой подправленной формулой:
[vba]

Код

=LET(
адрес,    ADDRESS(2,MATCH($B$2,$1:$1,0),2),
адрес1,   ADDRESS(2,MATCH($B$2,$1:$1,0)+1,2),
адрес2,   ADDRESS(2,MATCH($B$2,$1:$1,0)+2,2),
регэкс,   REGEXEXTRACT(адрес,"\w*"),
регэкс1,  REGEXEXTRACT(адрес1,"\w*"),
регэкс2,  REGEXEXTRACT(адрес2,"\w*"),
индир,    INDIRECT(CONCATENATE(регэкс,"2:",регэкс)),
индир1,   INDIRECT(CONCATENATE(регэкс,"2:",регэкс1)),
индир2,   INDIRECT(CONCATENATE(регэкс,"2:",регэкс2)),
впр25,    VLOOKUP($B$25,индир,1),
впр251,   VLOOKUP($B$25,индир1,2),
матч25,   MATCH(впр25,индир,0)+1,
финал,    ROUND((INDEX(индир2,матч25,2)-впр251)*(($B$25-впр25)/(INDEX(индир,матч25,1)-впр25))+впр251,2),

финал
)

[/vba]
А можете продолжать использовать свои формулы-монстры, заменив в них все "\w" на "\w*". В частности, это касается и формулы в ячейке B19. Для нее я уж точно не буду делать разбор и сокращение. Если хотите, можете сами потренироваться.

По текущему состоянию таблицы результаты для ячеек B18 и B19 получились 7.33 и 11.57 соответственно.

[p.s.]Ну, вот, пока писал лекцию, Лена уже и решила вопрос

(на Сникерс не претендую!)[/p.s.]

Код

=LET(
адрес,    ADDRESS(2,MATCH($B$2,$1:$1,0),2),
адрес1,   ADDRESS(2,MATCH($B$2,$1:$1,0)+1,2),
адрес2,   ADDRESS(2,MATCH($B$2,$1:$1,0)+2,2),
регэкс,   REGEXEXTRACT(адрес,"\w*"),
регэкс1,  REGEXEXTRACT(адрес1,"\w*"),
регэкс2,  REGEXEXTRACT(адрес2,"\w*"),
индир,    INDIRECT(CONCATENATE(регэкс,"2:",регэкс)),
индир1,   INDIRECT(CONCATENATE(регэкс,"2:",регэкс1)),
индир2,   INDIRECT(CONCATENATE(регэкс,"2:",регэкс2)),
впр25,    VLOOKUP($B$25,индир,1),
впр251,   VLOOKUP($B$25,индир1,2),
матч25,   MATCH(впр25,индир,0)+1,
финал,    ROUND((INDEX(индир2,матч25,2)-впр251)*(($B$25-впр25)/(INDEX(индир,матч25,1)-впр25))+впр251,2),

финал
)

(на Сникерс не претендую!)[/p.s.]Gustav

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Суббота, 25.03.2023, 17:46

Ответить

СообщениеВ первую очередь согласимся, что формулу на почти 1500 символов в ячейке B18 в её исходном виде сможет конструктивно проанализировать разве что персонаж Дастина Хоффмана в фильме "Человек дождя" (он там играет гения-аутиста):
[vba]

Код

=LET(
адрес,    ADDRESS(2,MATCH($B$2,$1:$1,0),2),
адрес1,   ADDRESS(2,MATCH($B$2,$1:$1,0)+1,2),
адрес2,   ADDRESS(2,MATCH($B$2,$1:$1,0)+2,2),
регэкс,   REGEXEXTRACT(адрес,"\w*"),
регэкс1,  REGEXEXTRACT(адрес1,"\w*"),
регэкс2,  REGEXEXTRACT(адрес2,"\w*"),
индир,    INDIRECT(CONCATENATE(регэкс,"2:",регэкс)),
индир1,   INDIRECT(CONCATENATE(регэкс,"2:",регэкс1)),
индир2,   INDIRECT(CONCATENATE(регэкс,"2:",регэкс2)),
впр25,    VLOOKUP($B$25,индир,1),
впр251,   VLOOKUP($B$25,индир1,2),
матч25,   MATCH(впр25,индир,0)+1,
финал,    ROUND((INDEX(индир2,матч25,2)-впр251)*(($B$25-впр25)/(INDEX(индир,матч25,1)-впр25))+впр251,2),

финал
)

(на Сникерс не претендую!)[/p.s.]

Автор - Gustav
Дата добавления - 25.03.2023 в 17:28

Pelena

Дата: Суббота, 25.03.2023, 17:31 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 19403

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Цитата timurhabibulin1993, 25.03.2023 в 17:10, в сообщении № 3 (

)

кошель электронный

Ю-money в подписи

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Gustav

Дата: Суббота, 25.03.2023, 17:38 | Сообщение № 6

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

timurhabibulin1993, "Google excel" выглядит примерно как "Бензиновый керосин". Хорошо бы исправить на "Google Sheets", а то позорите и то, и другое...

МОИ: Ник, Tip box: 41001663842605

Ответить

Gustav

Дата: Суббота, 25.03.2023, 23:43 | Сообщение № 7

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Как часто бывает в подобных случаях, ТС, получив ответ, удаляется, совсем закрывая доступ к обсуждаемому файлу. Таким образом, все последующие читатели топика с удручением констатируют этот факт. Чтобы не попадать в подобный "просак" я обычно выкладываю свою копию файла, первоначально сделанную для подготовки ответа на вопрос. Вот эта ссылка:

https://docs.google.com/spreads....sharing

Поскольку Форум иногда со временем "съедает" ссылки, делая их нерабочими, я также (уже по традиции) дополнительно выкладываю 44-символьный идентификатор таблицы. Этот id не является гиперссылкой, а является обычной строкой, правда, очень длинной. Вот этот id:

1jwE08ROzmTcOZDXXwWzNmxuOFS6hMR-4VrS9Y_J4L4o

Наконец, все примеры таблиц Гугл, которые я выкладываю, находятся у меня в специальной папке общего доступа. Вот ссылка на эту папку:

https://drive.google.com/drive....sharing

На всякий случай также отдельно id папки (28 символов):

0B6658xtcNsuzQ05ZYXZ4QXhOR28

МОИ: Ник, Tip box: 41001663842605

Ответить

Не находит значения за Z столбцом в формуле. (Формулы/Formulas)

Страница 1 из 1
1