Совмещение формулы СУММЕСЛИ и IMPORTRANGE

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

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

Совмещение формулы СУММЕСЛИ и IMPORTRANGE (Формулы/Formulas)

n-ton

Дата: Вторник, 07.11.2023, 15:36 | Сообщение № 1

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

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

Сообщений: 5

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

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

Google Docs

Здравствуйте, есть такая формула для расчета ЗП:
=СУММЕСЛИМН('Сентябрь!J4:J87;'Сентябрь'!K4:K87;"=Анастасия")

Пытаюсь ее вставить в другой документ через IMPORTRANGE:
=СУММЕСЛИМН(IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXX/edit";"Сентябрь'!J4:J87");
IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXX/edit";"Сентябрь'!K4:K87");"=Анастасия")

Выдается ошибка "В качестве аргумента необходимо указать диапазон ячеек.". Подскажите, что я делаю не так?

Сообщение отредактировал n-ton - Вторник, 07.11.2023, 15:39

Ответить

Gustav

Дата: Вторник, 07.11.2023, 16:41 | Сообщение № 2

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

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

Сообщений: 2808

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

Замечаний: ±

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

Да, эти функции плохо дружат между собой, точнее, совсем не дружат, уже сталкивались с этим:
http://www.excelworld.ru/forum/23-46058-1

Выйти из положения можно, обернув IMPORTRANGE в функции QUERY или SUMPRODUCT (как по ссылке было рассмотрено), либо используя функцию FILTER и обычную SUM:
[vba]

Код

=SUM(FILTER(
IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXX/edit";"Сентябрь'!J4:J87");
IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXX/edit";"Сентябрь'!K4:K87")="Анастасия"
))

[/vba]

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

Ответить

n-ton

Дата: Среда, 08.11.2023, 08:23 | Сообщение № 3

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

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

Сообщений: 5

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

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

Google Docs

Gustav, спасибо! Работает!

Еще 1 вопрос: как сделать, чтобы выводился "0", если в столбце нет имени "Анастасия", сейчас выводится "#Н/Д" и из-за этого формула итог не считает.

Ответить

Gustav

Дата: Среда, 08.11.2023, 12:00 | Сообщение № 4

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

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

Сообщений: 2808

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

Замечаний: ±

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

Цитата n-ton, 08.11.2023 в 08:23, в сообщении № 3 (

)

как сделать, чтобы выводился "0", если в столбце нет имени "Анастасия", сейчас выводится "#Н/Д"

Надо обернуть FILTER в функцию IFNA, которая является частным случаем функции IFERROR для ошибок типа #N/A (#Н/Д):
[vba]

Код

=SUM(IFNA(FILTER(
IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXX/edit";"Сентябрь'!J4:J87");
IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXX/edit";"Сентябрь'!K4:K87")="Анастасия"
);0))

[/vba]

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

Ответить

n-ton

Дата: Среда, 08.11.2023, 20:40 | Сообщение № 5

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

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

Сообщений: 5

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

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

Google Docs

Gustav, вы маг и волшебник! Спасибо большое!

Ответить

n-ton

Дата: Вторник, 28.11.2023, 09:50 | Сообщение № 6

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

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

Сообщений: 5

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

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

Google Docs

Gustav, подскажите, а как эту формулу адаптировать:
=СЧЁТЕСЛИ('Октябрь'!M4:M66;"Андрей")

Чтобы вставить в другой документ через IMPORTRANGE?

Делаю так, не выходит:
=СЧЁТЕСЛИ(IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXX/edit";"Октябрь!M4:M66")="Андрей")

Ответить

Gustav

Дата: Вторник, 28.11.2023, 10:41 | Сообщение № 7

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

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

Сообщений: 2808

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

Замечаний: ±

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

А доступ к документу, к которому IMPORTRANGE, авторизован? Если отдельно ввести в ячейку IMPORTRANGE (без СЧЁТЕСЛИ), то данные приходят? Если нет, то что пишет про ошибку? Это раз.

И два. Заметил при пристальном разглядывании второй формулы:

Цитата n-ton, 28.11.2023 в 09:50, в сообщении № 6 (

)

;"Октябрь!M4:M66")="Андрей")

А что если просто поменять знак равенства перед "Андреем" на точку с запятой (как в первой формуле)?