Использую формулу для суммирования значений в столбцах по значению в другом.
=СУММ(ЕСЛИОШИБКА((A2:AW2=BE2)*$C2:$AY2;))
Как можно изменить ее, чтобы суммировались значения не в отдельных ячейках, по критерию, а в в диапазоне от одного критерия до другого.
Т.е есть критерий, который находится в ячейке BE2 и критерии в ячейках BG2:BG3
Необходимо суммировать значения в ячейках "Время" лежащих между данными критериями.
В моем примере это выглядит так:
Есть фамилия в ячейке A2, которая соответствует критерию в ячейке BE2. Начинаем суммировать значения в ячейках Время, до ячейки Время которая лежит за одним из критериев в ячейках BG2:BG3. Суммируем C2, G2, K2. O2 лежит за критерием BG2:BG3. Дальше снова ищем критерий в строке, который соответствует BE2 - ячейка AG2. Суммируем AI2, AM2, AQ2, AU2. AY2 лежит за критерием BG2:BG3 ее не считаем.
Еще раз спасибо, за формулу в столбце F. Значительно облегчила работу, но никак не могу разобрать формулу в столбце G и возникла необходимость в определении дубля и выносе значения из ячеек A по дополнительным критериям в столбце I.
Если есть возможность объясните какие правки внести в формулу. Алгоритм подсчета дублей по критерию руками под спойлером. Если задача громоздкая для бесплатной помощи, то подскажите куда обратиться за платной
Скрытый текст
Алгоритм поиска дубликатов во вложенном файле. 1. Берется номер маршрута 333 из ячейки "B2", 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках B:B 4. Ищется ближайшая дата, перед искомой, удовлетворяющая критерию <=30 дней. Такой нет. 5. Возвращает значение "Нет" в ячейку F2.
1. Берется номер маршрута 333 из ячейки "B3", 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках B:B 4. Ищется ближайшая дата, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С2. 5. Проверяется соответствие "D3" c "D2" - не соответствует. Идем дальше. 6. Ищется ближайшая дата, минуня дату которая не выполнили условия перед искомой, удовлетворяющая критерию <=30 дней. Такой нет. Идем дальше. 5. Возвращает значение "Нет" в ячейку F3.
1. Берется номер маршрута 333 из ячейки "B4". 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках B:B 4. Ищется ближайшая дата, перед искомой, удовлетворяющая критерию <=30 дней. В д
1. Берется номер маршрута 333 из ячейки "B3", 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках B:B 4. Ищется ближайшая дата, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С2. 5. Проверяется соответствие "D3" c "D2" - не соответствует. Идем дальше. 6. Ищется ближайшая дата, минуня дату которая не выполнили условия перед искомой, удовлетворяющая критерию <=30 дней. Такой нет. Идем дальше. 5. Возвращает значение "Нет" в ячейку F3.
1. Берется номер маршрута 333 из ячейки "B4". 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках B:B 4. Ищется ближайшая дата, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С3. 5. Проверяется соответствие "D4" c "D3" - соответствует. Идем дальше. 6. Проверяется ячейка E3 на соответсвие со списком критериев I:I. Cоотвествует. Возвращается значение "Да" в ячейку F4. И возвращаем значение A3 в ячейку G4.
1. Берется номер маршрута 333 из ячейки "B5". 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках B:B 4. Ищется ближайшая дата, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С4. 5. Проверяется соответствие "D5" c "D4" - соответствует. Идем дальше. 6. Проверяется ячейка E4 на соответсвие со списком критериев I:I. Не соответвует.Идем дальше. 7. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С3. 8. Проверяется соответствие "D5" c "D3" - соответствует. Идем дальше. 9. Проверяется ячейка E3 на соответсвие со списком критериев I:I. Cоотвествует. Возвращается значение "Да" в ячейку F2. И возвращаем значение A3 в ячейку G5.
1. Берется номер маршрута 333 из ячейки "B6". 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках B:B 4. Ищется ближайшая дата, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С5. 5. Проверяется соответствие "D6" c "D5" - не соответствует. Идем дальше. 6. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С4. 7. Проверяется соответствие "D6" c "D4" - не соответствует. Идем дальше. 8. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С3. 9. Проверяется соответствие "D6" c "D3" - не соответствует. Идем дальше. 10. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С2. 11. Проверяется соответствие "D6" c "D2" - Соответствует. Идем дальше. 12. Проверяется ячейка E2 на соответсвие со списком критериев I:I. Не соотвествует. 13. Возвращает значение "Нет" в ячейку F6.
1. Берется номер маршрута 333 из ячейки "B7". 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках B:B 4. Ищется ближайшая дата, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С6. 5. Проверяется соответствие "D7" c "D6" - не соответствует. Идем дальше. 6. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С5. 7. Проверяется соответствие "D7" c "D5" - Соответствует. Идем дальше. 8. Проверяется ячейка E5 на соответсвие со списком критериев I:I. Не соотвествует.Идем дальше. 9. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С4. 10. Проверяется соответствие "D7" c "D4" - Соответствует. Идем дальше. 11. Проверяется ячейка E4 на соответсвие со списком критериев I:I. Не соотвествует. 12. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С3. 13. Проверяется соответствие "D7" c "D3" - Соответствует. Идем дальше. 14. Проверяется ячейка E3 на соответсвие со списком критериев I:I. Соотвествует.Возвращается значение "Да" в ячейку F7. И возвращаем значение A3 в ячейку G7.
1. Берется номер маршрута 333 из ячейки "B8". 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках B:B 4. Ищется ближайшая дата, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С7. 5. Проверяется соответствие "D8" c "D7" - не соответствует. Идем дальше. 6. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С6. 7. Проверяется соответствие "D8" c "D6" - Соответствует. Идем дальше. 8. Соотвествует.Возвращается значение "Да" в ячейку F8. И возвращаем значение A6 в ячейку G8.
1. Берется номер маршрута 333 из ячейки "B9". 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках B:B 4. Ищется ближайшая дата, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С8. 5. Проверяется соответствие "D9" c "D8" - Cоответствует. Идем дальше. 6. Соотвествует.Возвращается значение "Да" в ячейку F9. И возвращаем значение A8 в ячейку G9.
1. Берется номер маршрута 333 из ячейки "B10". 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках B:B 4. Ищется ближайшая дата, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С9. 5. Проверяется соответствие "D10" c "D9" - не оответствует. Идем дальше. 6. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С8. 7. Проверяется соответствие "D10" c "D8" - не оответствует. Идем дальше. 8. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С7. 9. Проверяется соответствие "D10" c "D7" - Соответствует. Идем дальше. 10. Проверяется ячейка E7 на соответсвие со списком критериев I:I. Не соотвествует.Идем дальше. 11. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С6. 12. Проверяется соответствие "D10" c "D6" - не соответствует. Идем дальше. 13. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С5. 14. Проверяется соответствие "D10" c "D5" - Соответствует. Идем дальше. 15. Проверяется ячейка E5 на соответсвие со списком критериев I:I. Не соотвествует.Идем дальше. 16. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С4 17. Проверяется соответствие "D10" c "D4" - Соответствует. Идем дальше. 18. Проверяется ячейка E4 на соответсвие со списком критериев I:I. Не соотвествует.Идем дальше. 19. Ищется ближайшая дата, минуня дату которая не выполнила условия, перед искомой, удовлетворяющая критерию <=30 дней. В данном примере это ячейка С3 20. Проверяется соответствие "D10" c "D3" - Соответствует. Идем дальше. 21. Проверяется ячейка E3 на соответсвие со списком критериев I:I. Соотвествует.Возвращается значение "Да" в ячейку F10. И возвращаем значение A3 в ячейку G10.
Кроме поиска дубликатов условным форматированием никакой информации в интернете найти не смог. Необходимо определить наличие дубликата по ряду критериев.
Пример поиска одного дубликата ниже. 1. Берется номер маршрута 333 из ячейки "B2", 2. Ищутся совпадения в столбце "B". 3. Находятся совпадения в ячейках "B3", "B5", "B7", "B9".
Далее проверяется соответствие дата из ячейки "С2" ячейкам "С3", "С5", "С7", "С9" по условию: <=30 дней 1. Ищется ближайшая дата, перед искомой. Это будет ячейка "С9" 2. Проверяется соответствие "D2" c "D9" - не соответствует 4. Ищется ближайшая дата следующая за "С9". Это будет ячейка "С7" 5. Проверяется соответствие "D2" c "D7" - соответствует 6. Значение "Да" возвращается в ячейку "E2" 7. Значение "A7" возвращается в "F2"
Возможно ли посчитать сумму только тех столбцов "Время 1-10", которые соответствуют столбцам "Фамилия 1-10" по критериям в столбце BU?
Если в ячейках одной строки в столбцах Фамилия1, Фамилия 2, Фамилия 3 и т.д. значение совпадает с критериями в столбце BU, то тогда суммировать значения в ячейках одной строки столбцов Время1, Время2, Время3 и т.д. Пробовал использовать "суммеслимн", но не нашел решения для несвязанных диапазонов.
1. Есть исходные данные в которых формула (столбец BR) ищет крайнее левое значение в строке по критерию в столбце BV (спасибо форумчанам!)
2. Необходимо найти значение в строке, стоящее перед крайним левым значением,которое соответствует одному из критериев в столбце BV (пункт 1) и соответствующее одному из критериев в столбце BW.
Надеюсь вопрос поставлен корректно. Отдельный лист с необходимым результатом создал. Большое спасибо за помощь!
Добрый день! Недавно пользователь форума помог с решением проблемы по нахождению крайнего правого значения по критерию.
Возник еще один вопрос для решения которого необходимо: 1. Найти крайнее левое значение по критерию из списка с критериями (столбец BV); 2. Найти первое значение, соответствующее критерию из спиcка (столбец BW), слева от найденного значения из пункта один.
В приложении на листе "Результат с крайним левым знач" указан желаемый результат.
Буду рад любой помощи или информации по составлению такой формулы. Спасибо!
БМВ, Огромное спасибо за ответ! Мне не очень ясен синтаксис формулы.
Можете ли вы подсказать еще как сделать аналогичный результат,но только не для последнего наименования (крайнее правое значение по критерию), а для первого (крайнее левое значение по критерию).
1. Необходимо чтобы в конце каждой строки было указано последнее наименование в столбце "Фамилия 1-10" из перечня в столбце BW, с возможностью в будущем редактировать данный перечень в будущем. 2. Необходимо посчитать сумму только тех столбцов "Время 1-10", которые соответствуют столбцам "Фамилия 1-10" с перечнем в столбце BW.
Второй вопрос был решен с помощью формулы в столбце "BQ":
Код
=(СУММЕСЛИ(BI2;BW$2;BK2))
Но она получается слишком громоздкая, когда число столбцов переваливает за 1000. Есть ли другой способ?
Добрый день! Есть формула которая возвращает значение из текста БОРИСОГЛЕБСКИЙ Р-Н., Г.БОРИСОГЛЕБСК до окончания слова "Р-Н.
Но также имеется текст формата МОСКОВСКАЯ ОБЛ., Г.ПОДОЛЬСК УЛ.СВЕРДЛОВА. Как можно изменить формулу, чтобы при отсутвии текста "Р-Н" формула искала и возвращала текст между словами "Г" и "УЛ" ?
Добрый день! Есть таблица куда автоматически заносятся данные. Постоянно приходится в ручную править формулу для расчета процента значений "0". Возможно ли как-то автоматизировать данный процесс? В столбце "С" требуется процент значений "0" от общего числа заполненных ячеек в строке, начиная со следующего дня в столбце "B". Заранее огромное спасибо!
Добрый день! Подскажите какой функцией возможно проверить соответствие номера из ячейки "A" на втором листе, значениям в таблице на первом листе. И в случае соответствия вернуть значение "да", а отсутствия - "нет". При условии, что требуется вернуть значение в ячейку столбца с соотвестующей датой на листе.
Добрый день. Подскажите как задать на данной диаграмме верхнее значение в 96 часов с шагом строго в 4 часа. Игрался с различными параметрами, excel делает значения произвольными.
Mikhail24, есть 59 строк с различным содержанием. Необходимо разделить строки 50/50, чтобы можно было произвести печать на двух сторонах брошурки. Соответственно есть всего несколько строк, размер которых можно изменить.
Sanja, а как указать переключатель в качестве переменой? Или придется ставить напротив строки какой-либо символ в случае активного переключателя и ссылаться на него?
Добрый день, есть тест c выбором ответов с помощью переключателя и галочек, подсчет балов происходит с двух строках выделенных цветов, которые я скрыл:
Код
Sub HideByConditionalFormattingColor()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In ActiveSheet.UsedRange.Columns(1).Cells
If cell.DisplayFormat.Interior.Color = Range("O2").DisplayFormat.Interior.Color Then cell.EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
Sub ShowByConditionalFormattingColor()
Columns.Hidden = False
Rows.Hidden = False
End Sub
Скажите, как дописать макрос, чтобы показать строки возможно было после проставления всех галочек и переключателей. Спасибо!