Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » ZakZak
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1 2 След.
Поиск наибольшего номера строки, удовлетворяющей условию
 
#
13.02.2022 14:25:09
Огромное спасибо! Именно то, что нужно, а то ну никак я эти массивы не освою до конца...
 
Перейти    
Поиск наибольшего номера строки, удовлетворяющей условию
 
#
13.02.2022 13:52:11
Выручайте товарищи, тупняк страшный напал, не знаю как сделать вот и всё...
В гугле что-то потыркался, но вот прям  нужное не смог найти...

Вообще моя формула  имеет следующий вид: (вникать в неё НЕ НУЖНО)
Код
=ЕСЛИОШИБКА(
ЕСЛИ(C484="";"";
ЕСЛИ(СЧЁТЕСЛИ(C$1:C$5000;C484)<2;
ЕСЛИ(O484="Not Confirmed";"";ЕСЛИ(И(L484*ЕСЛИ(M484="-";1;M484)>450;O484<>"Confirmed");"Требуется проверка";L484*ЕСЛИ(M484="-";1;M484)));
ЕСЛИ(СЧЁТЕСЛИМН(C$1:C$5000;C484;O$1:O$5000;"Confirmed")>0;
ЕСЛИ(ArrayFormula(НАИБОЛЬШИЙ((C484=C$1:C$5000)*(O$1:O$5000="Confirmed")*СТРОКА(C$1:C$5000);1))=СТРОКА(C484);
ЕСЛИ(O484="Not Confirmed";"";ЕСЛИ(И(L484*ЕСЛИ(M484="-";1;M484)>450;O484<>"Confirmed");"Требуется проверка";L484*ЕСЛИ(M484="-";1;M484)));
ЕСЛИ(ArrayFormula(НАИБОЛЬШИЙ((C484=C$1:C$5000)*(O$1:O$5000="Confirmed")*СТРОКА(C$1:C$5000);1))<СТРОКА(C484);"требует обновления";
ЕСЛИ(ArrayFormula(НАИБОЛЬШИЙ((C484=C$1:C$5000)*(O$1:O$5000="Confirmed")*СТРОКА(C$1:C$5000);1))>СТРОКА(C484);"устарело")));
ЕСЛИ(СЧЁТЕСЛИМН(C$1:C$5000;C484;O$1:O$5000;"Confirmed")=0;
ЕСЛИ(СТРОКА(C484)=ArrayFormula(НАИБОЛЬШИЙ((C484=C$1:C$5000)*СТРОКА(C$1:C$5000);1));
ЕСЛИ(O484="Not Confirmed";"";ЕСЛИ(И(L484*ЕСЛИ(M484="-";1;M484)>450;O484<>"Confirmed");"Требуется проверка";L484*ЕСЛИ(M484="-";1;M484)));
"устарело")))));"")
И вроде как она выполняет свою задачу, хоть и наверняка криво написана, опыта у меня не особо много, но мне нужно добавить ещё 1 условие.

Выдернул интересующий меня момент в отдельный файл:
Нужно найти максимальный номер строки и записать его в столбец D, чтобы позиция по столбцы B совпадала, а число в столбце C  у этой строки не превышало 15.
 
Перейти    
Поиск текста содержащего кавычки
 
#
30.04.2021 06:58:17
Да спасибо, работает, даже не буду пытаться понять, почему крайние кавычки надо полностью брать в кавычки, а среднюю нет.
 
Перейти    
Поиск текста содержащего кавычки
 
#
30.04.2021 06:27:39
Здравствуйте, измучился с этими кавычками сил нет уже.

Нужно проверить содержит ли в себе текст внутри ячейки, текст, который указан в скобках ("id": ")
там 3 символа кавычек из-за этого Excel ругается, даже если взять этот текст в кавычки.
Подскажите, как решается такая задача?
Изменено: ZakZak - 30.04.2021 06:33:12
 
Перейти    
Суммирование через формулу, ссылающуюся на сумму группы ячеек.
 
#
24.09.2020 08:42:36
Цитата
Дмитрий(The_Prist) Щербаков написал:
или отдельно каждую строку с Персиками надо просуммировать и сравнить?
Доброе утро!

Да, нужно чтобы было именно так  -  проверять условие по сумме из строки. И считать эту строку если сумма в ней меньше 100.

Это так легко делается через дополнительный стоблец, но я не могу додуматься как это сделать без него...
Изменено: ZakZak - 24.09.2020 08:43:15
 
Перейти    
Суммирование через формулу, ссылающуюся на сумму группы ячеек.
 
#
23.09.2020 16:55:36
Попытался я упростить задачу и похоже чересчур доупрощался, извиняюсь, что не смог сразу корректно сформулировать вопрос:

вот оригинал:
Код
=СУММ(ЕСЛИ(&#39;[Книга1.xlsx]Лист1&#39;!$FI$4:$FI$1000=B4;ЕСЛИ(&#39;[Книга1.xlsx]Лист1&#39;!$GP4:$GP1000<100;1;0);0))

Формула массива считает количество строк удовлетворяющих требованиям:
- значение из столбца FI  = значению из столбца B  в новой книге
- значение из столбца GP меньше 100

При этом в «Книга1» ячейка в столбце GP4  =СУММ(FX4:GH4) и так далее.
Вопрос: Как обойтись без столбца GP, знаю что его можно просто скрыть в основной таблице, но использование вспомогательного столбца почему-то меня раздражает....
 
Перейти    
Суммирование через формулу, ссылающуюся на сумму группы ячеек.
 
#
23.09.2020 16:05:27
Цитата
Сергей написал: берете СУММ в ней указываете диапазон суммирования а так же диапазон условий и необходимый критерийвсе просто
Вы имеете ввиду СУММЕСЛИМН ? эту функция я не могу использовать из-за некорректной работы в книгах с включенным общим доступом.
У меня ступор как раз с указанием диапазона, формула не спускается на такой же диапазон рядом ниже.

Цитата
Дмитрий(The_Prist) Щербаков написал: ...нам тоже лень создавать за Вас файл примера..
Ну извините, если вы восприняли это, как оскорбления, я наоборот хотел не загружать людей лишним открытием файлика, вот пример:
 
Перейти    
Суммирование через формулу, ссылающуюся на сумму группы ячеек.
 
#
23.09.2020 15:49:59
Здравствуйте, Уважаемы знатоки!

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

Яблоки       1  2  3
Персики     4  5  6
Груши        7  8  9
Персики  10 11 12
Груши     13 14 15

В крайнем левом столбце указана категория (в данном примере яблоки, персики или груши)
В ячейках правее показатели для этой категории.

Задача простая: через формулу массива в другой книге просуммировать по каждой категории все показатели.
Чтобы получилось так:
Яблоки     6
Персики  48
Груши      66

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

Без вот такого:

Яблоки       1  2  3     6
Персики     4  5  6    15
Груши         7  8  9    24
Персики  10 11 12   33
Груши     13 14 15    42

Заранее спасибо за ответы!
 
Перейти    
Управление диапазоном ссылки при помощи формулы ПОИСКПОЗ
 
#
31.07.2020 13:41:14
Всем спасибо, прикрутил формулу в рабочий файл, если будет время, то позже прикручу ее также в пример, получился у меня 4-ёх этажный ужастик конечно, но вроде работает...

Я не знал, что функция индекс может возвращать не только значение ячейки но и выступать внутри формулы в  роли ссылки на ячейку. Полезно.
 
Перейти    
Управление диапазоном ссылки при помощи формулы ПОИСКПОЗ
 
#
31.07.2020 11:33:50
Благодарю за разъяснение,  попозже попробую разобраться со своей задачей по данной методике, о результатах отпишусь.
 
Перейти    
Управление диапазоном ссылки при помощи формулы ПОИСКПОЗ
 
#
31.07.2020 11:20:53
Здравствуйте, в целом не принципиально, я смогу это исправить, но давайте будем считать что "нет3" не тоже самое что "нет"
 
Перейти    
Управление диапазоном ссылки при помощи формулы ПОИСКПОЗ
 
#
31.07.2020 10:03:08
эхххх, хочу просто написать формулу сам, поэтому и спросил сначала, так сказать именно непонятный мне кусочек, но походу без примера тут не обойтись...

На листе 1 красным выделено то, что должна возвращать функция, исходя из данных с листа 2.

Нужно, чтобы на листе 2 в столбце B производился поиск по коду с листа 1, и производилась проверка по слову "нет" и только в случае всех "нет" ( до строчки со следующим кодом) возвращалось "нет", а во всех остальных случаях "?".
 
Перейти    
Управление диапазоном ссылки при помощи формулы ПОИСКПОЗ
 
#
31.07.2020 09:47:18
Цитата
Максим В. написал:
=ИНДЕКС(B$6:B$2000;1000):B$2000
Что-то я не могу понять синтаксиса, индекс() это отдельная функция, которая возвращается результат ячейки, каким образом она сочитается через Двоеточие с другой ссылкой? диапазона же так не получится.
 
Перейти    
Управление диапазоном ссылки при помощи формулы ПОИСКПОЗ
 
#
31.07.2020 09:23:39
Доброе утро!

Столкнулся на первый взгляд с простой задачей, но впал в ступор, памааааагите!

Вот есть к примеру диапазон  !B$6:B$2000 в котором надо произвести выборочный поиск.
Дальше я при помощи формулы ПОИСКПОЗ определяю, что поиск надо начинать с 1000 строки.

Как мне это записать формульно, чтобы новый диапазон поиска был привязан к результату функции ПОИСКПОЗ
И в данном примере  был бы !B$1000:B$2000  
Изменено: ZakZak - 31.07.2020 09:23:47
 
Перейти    
Поиск номера первой строки в столбце, содержащей число
 
#
26.06.2020 13:00:00
Код
=ЕСЛИ(СУММ(AP22:BU22)<>0;ИНДЕКС($A$1:AE314;СТРОКА(AG22);ИНДЕКС(AG$1:AG21;
          АГРЕГАТ(15;6;СТРОКА(AG$1:AG21)/(AG$1:AG21^0);1)))-AK22;
          ЕСЛИ(AF22=1000;1000*ИНДЕКС($A$1:AE314;СТРОКА(AG22);ИНДЕКС(AG$1:AG21; 
          АГРЕГАТ(15;6;СТРОКА(AG$1:AG21)/(AG$1:AG21^0);1)));ЕСЛИ(AF22<>"";ИНДЕКС($A$1:AE314;СТРОКА(AG22);
          ИНДЕКС(AG$1:AG21;АГРЕГАТ(15;6;СТРОКА(AG$1:AG21)/(AG$1:AG21^0);1)));"")))

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

Поиск первого числа в строке, это как раз поиск ячейки в которой указан нужный номер столбца , ручками заносящийся через =Столбец(         )

P.S. наверняка всё это как-то красивее можно было сделать, но сделал как умел.
 
Перейти    
Поиск номера первой строки в столбце, содержащей число
 
#
26.06.2020 12:10:54
БМВ, спасибо, то что нужно!
 
Перейти    
Поиск номера первой строки в столбце, содержащей число
 
#
26.06.2020 10:13:56
Вариант интересный, но это также формула массива.
Просто есть тут у меня на работе человечек, который любит просматривать мои формулы, чтоб прям текстом что-то из строки копирнуть, и естественно формула массива потом слетает.
 
Перейти    
Поиск номера первой строки в столбце, содержащей число
 
#
26.06.2020 09:41:06
Доброе утро!

Туплю что-то по-страшному, подскажите, как найти номер первой строки в столбце, содержащей число. Это же как-то просто можно сделать,но я придумал только вот такой вариант, через формулу массива:
Код
=МИН(ЕСЛИ(ЕЧИСЛО(C1:C100);СТРОКА(C1:C100);"Не найдено"))

Но должен же быть вариант проще?
 
Перейти    
Ломается формула массива после закрытия книги
 
#
19.06.2020 15:19:33
Да, работает, почему это так просто и откуда вы все это знаете?
 
Перейти    
Ломается формула массива после закрытия книги
 
#
19.06.2020 14:17:01
Цитата
Сергей написал:
в чем проблема проверить
Я уже понял в чем причина, но отключать общий доступ пока в файле сидит примерно 20 человек нельзя

Ну а вдруг кто-нибудь предложит вариант подсчета без использования формулы массива.
Изменено: ZakZak - 19.06.2020 14:17:25
 
Перейти    
Ломается формула массива после закрытия книги
 
#
19.06.2020 09:46:33
Доброе утро!

Задача следующая: Посчитать кол-во символов "№" внутри диапазона ячеек, включая случаи когда внутри одной ячейки больше одного символа "№" .

Написал следующую формулу, вроде все нормально считает, и в итоге выводит в ячейку: Количество позиций N
Код
=СЦЕПИТЬ("Количество позиций";СИМВОЛ(10);СУММ(ДЛСТР(FP$4:FP$1000)-ДЛСТР(ПОДСТАВИТЬ(FP$4:FP$1000;"№";""))))
Но стоит закрыть книгу и открыть её по новой, как формула массива слетает и перестает реагировать на добавление новых символов "№"  внутри данного диапазона ячеек.

Подскажите, как можно это решить?

P.S. Чтобы заставить формулу считать, нужно снова задать её как формулу массива через Ctrl+Shift + Enter
P.P.S.  Возможно проблема связана с тем, что книга находится в общем доступе и фигурные скобки, обозначающие формулу массива почему-то не ставятся, хотя формула до закрытия книги работает.
P.P.P.S.  Да проблема в общем доступе, возможно если его отключить, ввести формулу массива и включить, то все будет нормально.
 
Перейти    
Поиск текста с использованием знаков подстановки (наличие нужного слова в тексте)
 
#
18.06.2020 12:07:19
Цитата
vikttur написал: ... два минуса. Маленькие,  да удаленькие )
Что это за магия? Каааак?
типо 2 минуса дают плус, но Истина при этом конвертируется в единичку?
 
Перейти    
Поиск текста с использованием знаков подстановки (наличие нужного слова в тексте)
 
#
18.06.2020 11:24:41
Цитата
sokol92 написал: У меня работает с закрытой книгой (формула массива):
У меня по-другому.

Вот это НЕ считает (возвращает 0):
Код
=СУММ(ЕЧИСЛО(ПОИСК("выполнил";&#39;[Лист Microsoft Excel (2).xlsx]Sheet1&#39;!$C$4:$C$1000)))

Вот это считает:
Код
=СУММ(ЕСЛИ(ЕЧИСЛО(ПОИСК("выполнил";&#39;[Лист Microsoft Excel (2).xlsx]Sheet1&#39;!$C$4:$C$1000));1;0))

формула массива и там и там.
Microsoft Office 365 бизнес, версия 1908 (сборка 11929.20838)
 
Перейти    
Поиск текста с использованием знаков подстановки (наличие нужного слова в тексте)
 
#
17.06.2020 16:57:35
Стоит убрать обертку из Если и ЕЧИСЛО сразу перестает считать, не знаю в чем дело, возможно во взаимодействии с формулой массива.
 
Перейти    
Поиск текста с использованием знаков подстановки (наличие нужного слова в тексте)
 
#
17.06.2020 16:07:00
Логику формулы понял сразу, если поиск находит позицию, значит искомый кусок текста внутри, и мы записываем записываем результат как ИСТИНА
Но  долго тупил, т.к. ИСТИНЫ у меня не суммировались потому что это не единички.
Но решилось довольно просто - обёртованием в еще одно если: ЕСЛИ(ЕЧИСЛО(ПОИСК("выполнил";ссылка));1;0)

Спасибо!
 
Перейти    
Поиск текста с использованием знаков подстановки (наличие нужного слова в тексте)
 
#
17.06.2020 15:31:18
Добрый день!

Уже 3 часа ломаю голову, но ничего не выходит.
СчетЕслиМн - отличная функция, но в некоторых случаях она перестает работать когда ссылка идет на закрытую книгу. ( у меня как раз такой случай)
Таким образом я записываю её через формулу массива и она работает как надо:
Код
=СУММ(ЕСЛИ([КнигаЗакрытая.xlsx]Лист1!$A$4:$A$1000="Иванов";ЕСЛИ([КнигаЗакрытая.xlsx]Лист1!$B$4:$B$1000="выполнил";1;0);0))

Но если я пишу не "выполнил" а "*выполнил*" , то находить слово "выполнил" функция перестает.

Подскажите как это можно исправить?

P.S. Да, я знаю что функция   =ЕСЛИ(B2="*выполнил*";1;0)  если в ячейки B2 записано "выполнил"  так же не посчитает из-за звездочек, но в этом случае можно использовать =СЧЁТЕСЛИ(B2;"*выполнил*") но как быть в случае с формулой массива?
 
Перейти    
[ Закрыто] СчетЕслиМн
 
#
28.05.2020 16:41:40
Здравствуйте!  Столкнулся с проблемой, что функция СчетЕслиМн  не работает при подсчете из закрытых книг.
Подскажите как можно прописать эту функцию через формулу массива, используя функции "Счет" "Если".
Для одного условия через массив прописал сам, но вот для 2 и более я что-то делаю не так...
Я уже функцию "И" только в какие места не засовывал не считает и все...
Изменено: ZakZak - 28.05.2020 16:47:49
 
Перейти    
Подстановочные знаки при поиски текста
 
#
04.03.2020 14:01:07
Здравствуйте!

Всегда меня мучил один вопрос, почему при поиске совпадения в тексте не работает подстановочные знаки?
например:
формула: =E22="нет" работает корректно,
но
та же формула с подстановочным символом:
Код
=E22="*нет*"
всегда вернет ЛОЖЬ
Вопрос: Обязательно ли в там случае делать так ? :
Код
=СЧЁТЕСЛИ(E22;"*нет*")
 
Перейти    
защита условного форматирования.
 
#
25.02.2020 17:09:43
Цитата
Mershik написал:
ну думаю конкретно так не получиться
А казалось бы всего-то нужна еще одна галочка в окошке защиты листа...

Неужели из-за этого могло бы что-то сломаться, ведь условное форматирование все равно приоритетнее обычного.
 
Перейти    
защита условного форматирования.
 
#
25.02.2020 16:53:17
Сам макросы писать не  умею.

Но у меня задачи состоит в том, чтобы запретить пользователю доступ к условному форматированию, но чтобы оно при этом работало.
А если я правильно понимаю принципы работы макросов, то вместе с отключением макросов отключится и УФ, сделанное через них.
 
Перейти    
Страницы: 1 2 След.
Наверх