=МАКС('1:31'!M5) работает, а вот минимум нужен без учета "0" и отрицательных чисел С дополнительной ячейкой не подойдет, так как в каждом листе по 50 ячеек и 31 лист (1550 ячеек)
=МАКС('1:31'!M5) работает, а вот минимум нужен без учета "0" и отрицательных чисел С дополнительной ячейкой не подойдет, так как в каждом листе по 50 ячеек и 31 лист (1550 ячеек)EreminaNS
Function MIN_0(l, F As Range) Dim re Dim N, K, d, AD N = Val(Split(Replace(l, "'", ""), ":")(0)) K = Val(Split(Replace(l, "'", ""), ":")(1)) d = IIf(N > K, -1, 1) AD = F.Address MIN_0 = 1E+30 For i = N To K Step d re = Sheets("Лист" & i).Range(AD).Value If re > 0 Then If MIN_0 > re Then MIN_0 = re End If Next i End Function
[/vba] вызов: [vba]
Code
=MIN_0("9:2";L2)
[/vba] но нужно подумать как лучше обращаться к листам: по индексу или пи имени. У меня в примере по номеру в имени. по индексу наверное будет проще. Индексы придётся вводить в поле вручную через ":"
можно написать свою функцию. Например так: [vba]
Code
Function MIN_0(l, F As Range) Dim re Dim N, K, d, AD N = Val(Split(Replace(l, "'", ""), ":")(0)) K = Val(Split(Replace(l, "'", ""), ":")(1)) d = IIf(N > K, -1, 1) AD = F.Address MIN_0 = 1E+30 For i = N To K Step d re = Sheets("Лист" & i).Range(AD).Value If re > 0 Then If MIN_0 > re Then MIN_0 = re End If Next i End Function
[/vba] вызов: [vba]
Code
=MIN_0("9:2";L2)
[/vba] но нужно подумать как лучше обращаться к листам: по индексу или пи имени. У меня в примере по номеру в имени. по индексу наверное будет проще. Индексы придётся вводить в поле вручную через ":"alex77755
Могу помочь в VB6, VBA Alex77755@mail.ru
Сообщение отредактировал alex77755 - Понедельник, 05.11.2012, 13:40
Тут не ячейка, а дополнительный лист бы нужен, который, кстати, можно скрыть. На листе этом вывести в 31 ячейках минимумы с соответствующих листов, а в 32-й получить общий минимум.
В одной же ячейке, кроме тупого прописывания 31 фрагмента, вряд ли что еще получится: [vba]
[/vba] К сожалению, не работают ни конструкция вида =МИН(ЕСЛИ('1:31'!M5>0;'1:31'!M5;1E+99)) ни ее версия с использованием ДВССЫЛ и СТРОКА(1:31): =МИН(ЕСЛИ(ДВССЫЛ("'"&СТРОКА(1:31)&"'!M5")>0;ДВССЫЛ("'"&СТРОКА(1:31)&"'!M5");1E+99))
Хотя если фрагменты ДВССЫЛ в формуле превратить по F9 в константы, то формула начинает считать: =МИН(ЕСЛИ({-1:-15:5:...}>0;{-1:-15:5:...};1E+99))
Quote (EreminaNS)
С дополнительной ячейкой не подойдет
Тут не ячейка, а дополнительный лист бы нужен, который, кстати, можно скрыть. На листе этом вывести в 31 ячейках минимумы с соответствующих листов, а в 32-й получить общий минимум.
В одной же ячейке, кроме тупого прописывания 31 фрагмента, вряд ли что еще получится: [vba]
[/vba] К сожалению, не работают ни конструкция вида =МИН(ЕСЛИ('1:31'!M5>0;'1:31'!M5;1E+99)) ни ее версия с использованием ДВССЫЛ и СТРОКА(1:31): =МИН(ЕСЛИ(ДВССЫЛ("'"&СТРОКА(1:31)&"'!M5")>0;ДВССЫЛ("'"&СТРОКА(1:31)&"'!M5");1E+99))
Хотя если фрагменты ДВССЫЛ в формуле превратить по F9 в константы, то формула начинает считать: =МИН(ЕСЛИ({-1:-15:5:...}>0;{-1:-15:5:...};1E+99))Gustav
Не жалко, конечно, но "неположительность" подобной ситуации в том, что на одном форуме ответ может быть уже получен, а на другом люди в самых искренних побуждениях в неведении могут еще напрягать свои извилины - уже практически впустую. Редкий топикстартер "закрывает" свои многочисленные вопросы на других форумах. И то, как правило, уже будучи один раз пожуренным.
Мне совершенно не жалко места для Ваших сообщений, пишите на здоровье. Мне потенциально жалко НАПРАСНЫХ усилий своих коллег по этому Форуму. И сообщение про кросс - в основном для них, а не с целью как-то поругать Вас.
Quote (EreminaNS)
И ЧТО ЖАЛКО
Не жалко, конечно, но "неположительность" подобной ситуации в том, что на одном форуме ответ может быть уже получен, а на другом люди в самых искренних побуждениях в неведении могут еще напрягать свои извилины - уже практически впустую. Редкий топикстартер "закрывает" свои многочисленные вопросы на других форумах. И то, как правило, уже будучи один раз пожуренным.
Мне совершенно не жалко места для Ваших сообщений, пишите на здоровье. Мне потенциально жалко НАПРАСНЫХ усилий своих коллег по этому Форуму. И сообщение про кросс - в основном для них, а не с целью как-то поругать Вас.Gustav
EreminaNS, Делайте дополнительный лист и не заморачивайтесь. Это самый простой и наглядный способ. На этом листе и будет находиться ячейка с требуемым минимумом книги, на которую можно ссылаться из других мест. Ячейке можно (но необязательно) назначить имя (через Ctrl+F3), а сам лист скрыть. Из плюсов данного подхода - не трогаются структуры листов 1..31. Ну, а что до структуры книги - то там где 31 лист, там и 32
EreminaNS, Делайте дополнительный лист и не заморачивайтесь. Это самый простой и наглядный способ. На этом листе и будет находиться ячейка с требуемым минимумом книги, на которую можно ссылаться из других мест. Ячейке можно (но необязательно) назначить имя (через Ctrl+F3), а сам лист скрыть. Из плюсов данного подхода - не трогаются структуры листов 1..31. Ну, а что до структуры книги - то там где 31 лист, там и 32 Gustav
Цитата, Gustav писал(а): Делайте дополнительный лист
С дополнительной ячейкой не подойдет
Вы хоть файл мой открывали из сообщения №10 ? Смотрели решение? С дополнительным - отдельным ЛИСТОМ, который никому не мешает, а не с ЯЧЕЙКОЙ на каждом листе...
P.S. И хочу уточнить, поскольку засомневался - надо одно минимальное значение среди всех ячеек всех листов или 50 ?
P.P.S. Если 50, значит на дополнительном листе будет некая решающая матрица (см. лист МИН50 в прилагаемом файле). Надо 1550 ячеек - значит надо, и даже не один раз.
Quote (EreminaNS)
Цитата, Gustav писал(а): Делайте дополнительный лист
С дополнительной ячейкой не подойдет
Вы хоть файл мой открывали из сообщения №10 ? Смотрели решение? С дополнительным - отдельным ЛИСТОМ, который никому не мешает, а не с ЯЧЕЙКОЙ на каждом листе...
P.S. И хочу уточнить, поскольку засомневался - надо одно минимальное значение среди всех ячеек всех листов или 50 ?
P.P.S. Если 50, значит на дополнительном листе будет некая решающая матрица (см. лист МИН50 в прилагаемом файле). Надо 1550 ячеек - значит надо, и даже не один раз.Gustav
Gustav, ваш вариант конечно подойдет НО нужен "сквозной" минимум по листам из определенной ячейки '1'!M5;'2'!M5;'3'!M5;'4'!M .....все ячейка M5 (C '1 ПО 31'ЛИСТ ) МИНИМУМ СРЕДИ ЯЧЕЕК М5 '1'!M6;'2'!M6;'3'!M6;'4'!M .....все ячейка M6 (C '1 ПО 31'ЛИСТ) МИНИМУМ СРЕДИ ЯЧЕЕК М6 '1'!M7;'2'!M7;'3'!M7;'4'!M .....все ячейка M7 (C '1 ПО 31'ЛИСТ) МИНИМУМ СРЕДИ ЯЧЕЕК М7 ВОТ ТАК ПРИМЕРНО И ЭТИХ ЯЧЕЕК С М5 ПО М55
Gustav, ваш вариант конечно подойдет НО нужен "сквозной" минимум по листам из определенной ячейки '1'!M5;'2'!M5;'3'!M5;'4'!M .....все ячейка M5 (C '1 ПО 31'ЛИСТ ) МИНИМУМ СРЕДИ ЯЧЕЕК М5 '1'!M6;'2'!M6;'3'!M6;'4'!M .....все ячейка M6 (C '1 ПО 31'ЛИСТ) МИНИМУМ СРЕДИ ЯЧЕЕК М6 '1'!M7;'2'!M7;'3'!M7;'4'!M .....все ячейка M7 (C '1 ПО 31'ЛИСТ) МИНИМУМ СРЕДИ ЯЧЕЕК М7 ВОТ ТАК ПРИМЕРНО И ЭТИХ ЯЧЕЕК С М5 ПО М55EreminaNS
Сообщение отредактировал EreminaNS - Вторник, 06.11.2012, 08:34
Окончательно нарисовалась самонастраивающаяся формула, отображающая для одноименных ячеек листов 1..31 "минимум > 0" в такой же (одноименной) ячейке некоторого дополнительного ("32-го") листа.
В примере в прилагаемом файле формула настроена для листов 1..6 и помещена на дополнительный лист МИН.
При настройке на 31 лист следует исправить число 6 на число 31 в двух фрагментах ДВССЫЛ("1:6"). Дополнительной именованной формулы (Ctrl+F3) в этом файле не требуется.
P.S. Если для какой-либо ячейки на всех листах не существует хотя бы одного положительного значения (например, все ячейки пусты), то формула возвращает 0. Этот 0 можно превратить в любое заранее оговоренное число, например, 1000000 или очень большое 1E+99, просто дописав это число в окончание формулы:
Окончательно нарисовалась самонастраивающаяся формула, отображающая для одноименных ячеек листов 1..31 "минимум > 0" в такой же (одноименной) ячейке некоторого дополнительного ("32-го") листа.
В примере в прилагаемом файле формула настроена для листов 1..6 и помещена на дополнительный лист МИН.
При настройке на 31 лист следует исправить число 6 на число 31 в двух фрагментах ДВССЫЛ("1:6"). Дополнительной именованной формулы (Ctrl+F3) в этом файле не требуется.
P.S. Если для какой-либо ячейки на всех листах не существует хотя бы одного положительного значения (например, все ячейки пусты), то формула возвращает 0. Этот 0 можно превратить в любое заранее оговоренное число, например, 1000000 или очень большое 1E+99, просто дописав это число в окончание формулы:
Gustav, Спасибо большое все понятно и работает а главное вы доступно все объяснили
Мне так же помогли на форуме[url=http://www.programmersforum.ru/showthread.php?t=216609] ZORRO2005 формула(Немного переделал формулу KIMa.) =НАИБОЛЬШИЙ('1:4'!A2;СУММ(СЧЁТЕСЛИ(ДВССЫЛ(СТРОКА($1:$4)&"!A2");">0")))
Gustav, Спасибо большое все понятно и работает а главное вы доступно все объяснили
Мне так же помогли на форуме[url=http://www.programmersforum.ru/showthread.php?t=216609] ZORRO2005 формула(Немного переделал формулу KIMa.) =НАИБОЛЬШИЙ('1:4'!A2;СУММ(СЧЁТЕСЛИ(ДВССЫЛ(СТРОКА($1:$4)&"!A2");">0")))EreminaNS
Сообщение отредактировал EreminaNS - Вторник, 06.11.2012, 17:09
Ну, наверное, какой-нить формат в этих ячейках у Вас стоит "сокращающий". Сами формулы ничего не сокращают. Не видя файла - увы, беспредметно рассуждать.
Ну, наверное, какой-нить формат в этих ячейках у Вас стоит "сокращающий". Сами формулы ничего не сокращают. Не видя файла - увы, беспредметно рассуждать.Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
