Вместо результата формулы - пусто в ячейке, не считает

Дата: Четверг, 15.10.2020, 10:53 | Сообщение № 1

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

Ранг: Участник

Сообщений: 56

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

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

Excel 2019

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

Формула такая:
[vba]

Код

=ЕСЛИОШИБКА(ВПР(ПРОСМОТР(;-1/(СЖПРОБЕЛЫ(RIGHTB(ПОДСТАВИТЬ($A4;" ";ПОВТОР(" ";99));99))=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zUYGlp-7Y2SWQMwJZzrxtioHH48ZMipepaL8_wKYwQ8/edit#gid=990843988";"Ответы на форму (1)!$C:$C"))/(B$3=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zUYGlp-7Y2SWQMwJZzrxtioHH48ZMipepaL8_wKYwQ8/edit#gid=990843988";"Ответы на форму (1)!$B:$B"));IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zUYGlp-7Y2SWQMwJZzrxtioHH48ZMipepaL8_wKYwQ8/edit#gid=990843988";"Ответы на форму (1)!$H:$H"));{1;0,5:10000;1};2);)

[/vba]

Если сумма по конкретному человеку за день больше 10000 -то ставится коэффициент 1.
Если меньше 10000 - то коэф.0,5
Если продаж "0" - то коэф.0

Файл, где считаю данные https://u.to/gaHsGQ
Файл, откуда беру данные https://u.to/UWzrGQ

Дата: Четверг, 15.10.2020, 11:26 | Сообщение № 2

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

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

Сообщений: 2797

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

Замечаний: ±

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

IMPORTRANGE в каждой формуле, да по несколько раз? По-моему, Вы издеваетесь над Таблицами - вот они и отказываются считать

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

Дата: Четверг, 15.10.2020, 16:06 | Сообщение № 3

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

Ранг: Участник

Сообщений: 56

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

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

Excel 2019

Попробовала составить другую формулу, где IMPORTRANGE также несколько раз было - все работает. Полагаю что дело не в этом.

Попробовала разбить, написать покороче формулу:

[vba]

Код

=ЕСЛИОШИБКА(ПРОСМОТР(ВПР("*Регина*"&C3;IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dX9SiH5TY9ByWBXri9f-jXsRnecJyK_9ddSy7I9VpCQ/edit#gid=990843988";"Ответы на форму (1)'!$A:$I");9;0);{1;0,5:10000;1});0)

[/vba]

и опять, в икселе работает, в гугл таблицах нет. Полагаю, что не нравится вот это условие {1;0,5:10000;1}. Все цифры просто подсвечены цветом, а 0,5:10000. Может в написании формулы все же дело ? Только не понимаю как ее исправить

Сообщение отредактировал Pelena - Суббота, 17.10.2020, 20:37

Pelena

Дата: Четверг, 15.10.2020, 17:02 | Сообщение № 4

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

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

Сообщений: 19403

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

Замечаний: ±

Excel 365 & Mac Excel

Nikonka89, оформляйте формулы тегами с помощью кнопки #

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

Дата: Четверг, 15.10.2020, 23:51 | Сообщение № 5

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

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

Сообщений: 2797

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

Замечаний: ±

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

Цитата Nikonka89, 15.10.2020 в 16:06, в сообщении № 3 ()

формулу, где IMPORTRANGE также несколько раз было - все работает.

Работает, не спорю. Я имею в виду, что тон - дурной так делать. А некоторое время тому назад существовало и ограничение - не более 50 упоминаний IMPORTRANGE на файл. Так что с таким подходом у вас на десятке ячеек с подобными формулами квота бы и закончилась.

Дата: Пятница, 16.10.2020, 12:09 | Сообщение № 6

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

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

Сообщений: 2797

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

Замечаний: ±

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

Цитата Nikonka89, 15.10.2020 в 16:06, в сообщении № 3 (

)

Полагаю, что не нравится вот это условие {1;0,5:10000;1}

Вообще, в Таблицах Google внутри массива разделителем строк является точка с запятой (;), а разделителем столбцов - обратный слэш (\). Поэтому есть ощущение, что ваш массив должен быть записан как {1\0,5;10000\1}, что соответствует вот такой ориентации массива:
[vba]

Код

1 0,5
10000 1

[/vba]
Если же в ячейку ввести ваш массив как формулу:
[vba]

Код

={1;0,5:10000;1}

[/vba]
то массив предстанет в следующем виде (что явно не то, что надо):
[vba]

Код

1
#N/A
1

[/vba]

Цитата Nikonka89, 15.10.2020 в 16:06, в сообщении № 3 ()

и опять, в икселе работает, в гугл таблицах нет

А можете показать файл Excel, где это работает?

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

Сообщение отредактировал Gustav - Пятница, 16.10.2020, 12:17

Дата: Пятница, 16.10.2020, 12:55 | Сообщение № 7

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

Ранг: Участник

Сообщений: 56

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

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

Excel 2019

Цитата Gustav, 16.10.2020 в 12:09, в сообщении № 6 ()

ваш массив должен быть записан как {1\0,5;10000\1}

попробовала - все равно ничего. Не выдает результат.

Цитата Gustav, 16.10.2020 в 12:09, в сообщении № 6 ()

А можете показать файл Excel, где это работает?

прикладываю

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

Пока я пользуюсь просто дикой формулой, с тремя условиями если, с кучей IMPORTRANGE, поискпоз + сцепка в файле откуда беру инфо. Работает )), но она огромная и сцепка в другой файле тоже неудобна. Документы в общем доступе и все ими пользуются на работе(

К сообщению приложен файл: -___.xlsx (12.1 Kb)

Дата: Суббота, 17.10.2020, 18:27 | Сообщение № 8

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

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

Сообщений: 2797

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

Замечаний: ±

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

Цитата Nikonka89, 16.10.2020 в 12:55, в сообщении № 7 ()

Может можно составить формулу в гугл таблицах вообще другую, чтобы работала ?

Можно, и не одну. Я вот решил использовать функцию FILTER (для ячейки B4):
[vba]

Код

=IFERROR(VLOOKUP(FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zUYGlp-7Y2SWQMwJZzrxtioHH48ZMipepaL8_wKYwQ8/edit#gid=990843988";"Ответы на форму (1)!$D:$D");
(B$3=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zUYGlp-7Y2SWQMwJZzrxtioHH48ZMipepaL8_wKYwQ8/edit#gid=990843988";"Ответы на форму (1)!$B:$B"))*
(TRIM(RIGHT(SUBSTITUTE($A4;" ";REPT(" ";99));99))=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zUYGlp-7Y2SWQMwJZzrxtioHH48ZMipepaL8_wKYwQ8/edit#gid=990843988";"Ответы на форму (1)!$C:$C")));{1\0,5;10000\1};2);0)

[/vba]
Или чуть-чуть упрощая, оставив в IMPORTRANGE только Id таблицы, без избыточного символьного "мусора":
[vba]

Код

=IFERROR(VLOOKUP(FILTER(
IMPORTRANGE("1zUYGlp-7Y2SWQMwJZzrxtioHH48ZMipepaL8_wKYwQ8";"Ответы на форму (1)!$D:$D");
(B$3=
IMPORTRANGE("1zUYGlp-7Y2SWQMwJZzrxtioHH48ZMipepaL8_wKYwQ8";"Ответы на форму (1)!$B:$B"))
*
(TRIM(RIGHT(SUBSTITUTE($A4;" ";REPT(" ";99));99))=
IMPORTRANGE("1zUYGlp-7Y2SWQMwJZzrxtioHH48ZMipepaL8_wKYwQ8";"Ответы на форму (1)!$C:$C")));
{1\0,5;10000\1};2);0)

[/vba]

Цитата Nikonka89, 16.10.2020 в 12:55, в сообщении № 7 ()

Но все таки одну, без доп.листов и колонок?

Не знаю, что уж вы их так боитесь. Вывести по IMPORTRANGE нужные диапазоны из другого файла на отдельный вспомогательный лист (допустим, Лист1) в текущем файле - это нормально. Зато какие прозрачные формулы после этого получаются - приятно посмотреть!
[vba]

Код

=IFERROR(VLOOKUP(
FILTER('Лист1'!$D:$D;
(B$3='Лист1'!$B:$B)*(TRIM(RIGHT(SUBSTITUTE($A4;" ";REPT(" ";99));99))='Лист1'!$C:$C)
);
{1\0,5;10000\1};2);0)

[/vba]

Цитата Nikonka89, 16.10.2020 в 12:55, в сообщении № 7 ()

Может можно составить формулу в гугл таблицах вообще другую, чтобы работала ?

Можно, и не одну. Я вот решил использовать функцию FILTER (для ячейки B4):
[vba]

Код

[/vba]
Или чуть-чуть упрощая, оставив в IMPORTRANGE только Id таблицы, без избыточного символьного "мусора":
[vba]

Код

[/vba]

Цитата Nikonka89, 16.10.2020 в 12:55, в сообщении № 7 ()

Но все таки одну, без доп.листов и колонок?

Код

=IFERROR(VLOOKUP(
FILTER('Лист1'!$D:$D;
(B$3='Лист1'!$B:$B)*(TRIM(RIGHT(SUBSTITUTE($A4;" ";REPT(" ";99));99))='Лист1'!$C:$C)
);
{1\0,5;10000\1};2);0)

[/vba]Gustav

Сообщение

Цитата Nikonka89, 16.10.2020 в 12:55, в сообщении № 7 ()

Может можно составить формулу в гугл таблицах вообще другую, чтобы работала ?

Можно, и не одну. Я вот решил использовать функцию FILTER (для ячейки B4):
[vba]

Код

[/vba]
Или чуть-чуть упрощая, оставив в IMPORTRANGE только Id таблицы, без избыточного символьного "мусора":
[vba]

Код

[/vba]

Цитата Nikonka89, 16.10.2020 в 12:55, в сообщении № 7 ()

Но все таки одну, без доп.листов и колонок?

Код

=IFERROR(VLOOKUP(
FILTER('Лист1'!$D:$D;
(B$3='Лист1'!$B:$B)*(TRIM(RIGHT(SUBSTITUTE($A4;" ";REPT(" ";99));99))='Лист1'!$C:$C)
);
{1\0,5;10000\1};2);0)

[/vba]

Автор - Gustav
Дата добавления - 17.10.2020 в 18:27

Дата: Суббота, 17.10.2020, 18:39 | Сообщение № 9

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

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

Сообщений: 2797

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

Замечаний: ±

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

Цитата Gustav, 17.10.2020 в 18:27, в сообщении № 8 ()

Я вот решил использовать функцию FILTER

[p.s.]Да, и при использовании FILTER в общем случае может возвращаться не одна запись, а массив. В рассматриваемом примере, скорее всего, по условиям задачки будет возвращаться всегда одна запись, но на всякий случай можно сделать явное ограничение до одной записи при помощи функции INDEX(массив; 1)[/p.s.]:
[vba]

Код

=IFERROR(VLOOKUP(
INDEX(FILTER('Лист1'!$D:$D;
(B$3='Лист1'!$B:$B)*(TRIM(RIGHT(SUBSTITUTE($A4;" ";REPT(" ";99));99))='Лист1'!$C:$C)
);1);
{1\0,5;10000\1};2);0)

[/vba]

Дата: Воскресенье, 18.10.2020, 10:49 | Сообщение № 10

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

Ранг: Участник

Сообщений: 56

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

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

Excel 2019

Получилось, спасибо!!