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

Страницы: 1 2 След.
Задать диапазон в формуле другой формулой
 
#
05.01.2021 12:19:07
Цитата
БМВ написал:
INDEX(A5:F94;;3)  выдаст диапазон C5:C94
Спасибо. итоговая формула получилась.
Код
=MAXIFS(ИНДЕКС('ЛИСТ'!$G$5:$Z$94;0;ПОИСКПОЗ($C$2;'ЛИСТ'!$G$4:$Z$4;0));'ЛИСТ'!$F$5:$F$94;"желтый")
Как мне кажется делает то, что необходимо
 
Перейти    
Задать диапазон в формуле другой формулой
 
#
05.01.2021 11:58:16
Всем добрый день. Не могли бы подсказать как "Задать диапазон в формуле другой формулой"
Есть формула
Код
=MAXIFS('ЛИСТ'!U5:U94;'ЛИСТ'!F5:F94;"желтый")
В этой формуле необходимо задать диапазон неявно, т.к. он будет меняться (столбец поиска) в зависимости от другой ячейки.
Пробовал как-то объединить с формулой
Код
=АДРЕС(5;ПОИСКПОЗ(C2;'ЛИСТ'!G4:Z4;0)+6)
Вторая формула получает значение U5, в зависимости от ячейки C2.

Соответственно интересует, можно ли как то задать в формуле поиска максимума по условию, не конкретный диапазон, а зависящий от другой ячейки (зависит только столбец поиска, количество строк всегда одинаковое)
 
Перейти    
Несколько условий в одном диапазоне_условий функции СРЗНАЧЕСЛИМН
 
#
16.06.2020 13:23:07
Добрый день.
ПОдскажите, можно ли в формулу СРЗНАЧЕСЛИМН добавить несколько значений на одно условие? т.е. что-то типо
Код
=СРЗНАЧЕСЛИМН(A1:A10, B1:B10,"текст1" или "текст2")

таким образом диапазон условия один и тот же, но ищется в нем не одно, а два условия.

Пробовал
Код
=СРЗНАЧЕСЛИМН(A1:A10, B1:B10, "текст1", B1:B10, "текст2",) 
=СРЗНАЧЕСЛИМН(A1:A10, B1:B10, {"текст1", "текст2"})

не помогает. в первом случае ошибка, во втором, проверяет только первое значение массива и игнорирует второе
 
Перейти    
Заливка ячейки по условию
 
#
06.06.2019 21:28:00
всё вроде разобрался, это константа получается означает, с какого номера в массиве начать раскрашивать. сделал её зависимой от даты и всё заработало.
Спасибо за помощь. теперь попробую на телефоне открыть. посмотрим, работает ли там условное форматирование.

P.s. куда всё-таки формула из поста #5 вставляется? попробую в ней разобраться  
 
Перейти    
Заливка ячейки по условию
 
#
06.06.2019 20:49:10
а в ячейке I5 надо формулу восстановить?  без неё при смене даты также ничего не происходит
а если вернуть, то красит не так
 
Перейти    
Заливка ячейки по условию
 
#
06.06.2019 19:37:30
наверно я что-то не понимаю. куда необходимо поставить эту строку?
файл во вложении уже должно перекрашивать правильно? у меня при смене даты ничего не происходит

P.s. почему ничего не происходит вроде разобрался. в ячейке с остатком (I5) была удалена формула. после того, как её вернул. стало перекрашиваться.

Но перекрашивается точно не верно, т.к. появляются 2 или 3 ячейки, идущие подряд одного цвета, а такого быть не может
Изменено: Крекер - 06.06.2019 19:46:51
 
Перейти    
Заливка ячейки по условию
 
#
06.06.2019 17:09:45
закраска идет начиная с 26/04/2019 по следующей последовательности :
К,Ж,С,К,С,Ж,С,К,Ж
где соответственно К-коричневый, Ж-желтый, С-серый. дальше всё циклично повторяется.
дальше уже просто идет пересчетом от этой даты.
В цикле дат - 48 значений, в цикле цветов 9. 48 mod 9 =3. высчитываем количество полных циклов от начальный даты N, и количество остатков (3*т mod 9). получается с какого цвета начать первую дату. ну а дальше уже по массиву по порядку.
Надеюсь не слишком запутанно объяснил

P.s. спасибо за упрощенный код
Изменено: Крекер - 06.06.2019 17:11:44
 
Перейти    
Заливка ячейки по условию
 
#
06.06.2019 16:49:52
Добрый день. Есть готовый файл с расписанием.
Можно ли как-то в нем избавиться от макроса, для того что бы файл выполнял свои функции на телефоне (Android)?
Суть в следующем: при вводе в ячейку I3 (дата для пересчета), автоматически ищется промежуток (в полном цикле 17 дней) в который входит эта дата, и по нему заменяется первый столбец. эта часть сделана формулами и на телефоне работает нормально.
Также всё расписание должно быть перекрашено по порядку (цикл раскрашивания 9 цветов). эта часть делается макросом, который запускается при изменении ячейки I3. ну и естественно эта часть не работает на Android.

Нашел вот такую тему
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=117717&...
По названию и просьбе автора, вроде тоже самое. Но результата в ней нет? в итоговом файле вроде никаких формул или макросов
 
Перейти    
Вставить лист между скрытыми листами (макрос)
 
#
14.05.2019 15:11:18
Цитата
Nordheim написал:
ключевое слово "наверно", но по сути совсем не то же самое.
делает то что мне надо

Цитата
Мотя написал:
Однако, странно - Вы о каком-то коде автоматизации "печётесь"...
по-моему это уже оффтоп
 
Перейти    
Вставить лист между скрытыми листами (макрос)
 
#
14.05.2019 14:52:30
Цитата
Мотя написал:
Это - о ЧЁМ?
о листе, который в дальнейшем будет добавлен в свод
Изменено: Крекер - 14.05.2019 14:52:43
 
Перейти    
Вставить лист между скрытыми листами (макрос)
 
#
14.05.2019 14:43:25
Цитата
Мотя написал:
Вы каждый Месяц добавляете в файл - Лист-Начало и Лист-Конец?!    
А раньше ничего и не было. Я пока только формирую автоматизированный свод и делаю по аналогии с тем, что делал для других целей ранее
 
Перейти    
Вставить лист между скрытыми листами (макрос)
 
#
14.05.2019 14:15:18
Цитата
Юрий М написал:
Есть. Только не нужно через 2 часа поднимать тему.
Спасибо. Не очень знаю для чего используется With, но сделал по аналогии тоже самое наверно
Код
Sheets("Лист2").Visible = True
Sheets(1).Select
Sheets(1).Copy Before:=Sheets("Лист2")
Sheets("Лист2").Visible = False
Цитата
Мотя написал:
Мне всегда - интересно, кому нужен такой: "заумный" Алгоритм?!   В придачу - с Макросом!!!У Вас тривиальнейшие формулы Консолидации однотипных таблиц на листах!Листы - Скрывать / Отображать: Сколько Геморроя!  Короче, "Без бутылки" - не разобраться!    
Не очень понял суть вопроса.
Можно как то проще посчитать сумму ячейки по всем листам? Таже справка https://www.planetaexcel.ru/techniques/2/138/ советует тот же самый способ.
Просто т.к. нет начальных листов, приходится создать пусть Лист1 вначале, и Лист2 в конце для подсчета суммы между этими листами. Эти листы раздражают и поэтому решил их скрыть, как оказалось формула со скрытым листом так же работает.
А по поводу макроса: это ведь только один этап. Вначале добавляется лист в свод, затем обрабатывается на ошибки, затем добавляется в нужную часть свода и т.д. Теоретически это можно делать и руками (как раньше и было) каждый месяц, но если есть возможность автоматизировать процесс, то почему бы и нет  
 
Перейти    
Вставить лист между скрытыми листами (макрос)
 
#
14.05.2019 12:15:45
нет никаких вариантов?
 
Перейти    
Вставить лист между скрытыми листами (макрос)
 
#
14.05.2019 10:18:21
Доброго времени суток. Возник следующий вопрос:
Есть сводный файл, по умолчанию в нем 5 лист (лист обработки, свод, лист1, лист2, ошибки)
на листе свод формулы вида:
=СУММ(Лист1:Лист2!D15)
Соответсвенно после обработки лист копируется между лист1 и лист2, и в своде считается сумма по всем листам. Хочется скрыть скрыть ненужные лист (лист1 и лист2). Формулы в своде считают при этом нормально.
Но вот в макросе строка
Код
Sheets(1).Copy Before:=Sheets("Лист2")

копирует лист  в случайное место. СОбственно сабж:
Как скопировать лист между скрытыми листами?

P.s.
Возможно есть другой способ считать сумму по ячейке со всех листов для свода, чтобы избежать пустых листов вначале и вконце?
 
Перейти    
Последовательная заливка ячеек в формуле
 
#
12.05.2019 20:55:08
Вопрос решен. сделал через RGB
 
Перейти    
Последовательная заливка ячеек в формуле
 
#
12.05.2019 20:49:54
жаль. тогда пытаюсь сделать через макрос и массив.
не могу разобраться с цветом. Использую 3 разных цвета. автозаписью макроса получил следующее:
Код
'желтый:
Range("D63").Interior.Color = vbYellow
'серый:
Range("D63").Interior.ThemeColor = xlThemeColorAccent3
'коричневый:
Range("D63").Interior.ThemeColor = xlThemeColorAccent2

Проблема в том что в первом случае используется похоже просто "цвет", а во втором и третьем "тема". Можно как то привести к одному виду? желтый сделать "темой" или наоборот как-то вытащить цвет в любом формате этой темы
 
Перейти    
Последовательная заливка ячеек в формуле
 
#
12.05.2019 19:40:24
Добрый вечер.
Вопрос в следующем.
Можно ли как то в формуле задать заливку ячейки? В ячейке уже есть формула (=C2+ВРЕМЯ(8;30;0)) для расчета времени.
Расписание составляется на 17 дней (полный цикл), а последовательность заливки повторяется каждые 9 раз.
Файл примера во вложении.
Последовательность заливки в ячейках B23:J23.
Сейчас хочется понять, можно ли залив первую ячейку со временем, например первым цветом, залить все оставшиеся? Заливаются ячейки B2:D19
И при этом избежать макросов, сделав это всё формулами
Изменено: Крекер - 12.05.2019 19:45:00
 
Перейти    
Промежуточные итоги (аналог) для скрытых листов
 
#
12.10.2018 09:56:36
Доброе время суток. Вопрос в названии темы.
Суть вопроса:
Есть сводный лист в файле с формулами в ячейках ввида
=СУММ(Лист1:Лист20!F8).
Все листы одного формата, и соответственно считается сумма определенной ячейки всех листов между 1 и 20. количество листов рандомно. Если скрыть один из листов, то сумма не меняется в своде. Можно ли как то адаптировать под скрытые листы ПромежуточныеИтоги ну или есть аналог этой функции для листов?
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;Лист1:Лист20!G8) или =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;Лист1:Лист20!G8) не работает
 
Перейти    
Использование VLookUp В макросе
 
#
28.06.2018 18:25:44
Вроде понял, спасибо. Буду за компом попробую
 
Перейти    
Использование VLookUp В макросе
 
#
28.06.2018 18:03:03
файл:
 
Перейти    
Использование VLookUp В макросе
 
#
28.06.2018 17:59:26
Аналогично, пробовал и с кавычками и без:
Код
Application.WorksheetFunction.VLookup(Cells(i, 1).Value, Worksheets("Лист2").Range("B1:D7"), 2)
Изменено: Крекер - 28.06.2018 17:59:42
 
Перейти    
Использование VLookUp В макросе
 
#
28.06.2018 17:54:34
а можно ли как то обращаться к базе(таблица2), которая находится на другом листе? Пробовал так, но ругается на неверное свойство объекта:
Код
Application.WorksheetFunction.VLookup(Cells(i, 1),Worksheets(2).Range("B1:D7"), 2)
Изменено: Крекер - 28.06.2018 17:54:57
 
Перейти    
Использование VLookUp В макросе
 
#
26.06.2018 17:47:57
Sanja, спасибо. макрос отработал, но скрыл все строки. поищу пока что лишнее в нем

Цитата
vikttur написал: Выделять строки не нужно
тоже спасибо, поправил
 
Перейти    
Использование VLookUp В макросе
 
#
26.06.2018 17:39:43
Код
If (chislo <> Application.WorksheetFunction.VLookup(Cells(i, 1), "M14:P20", 2) And chislo <> Application.WorksheetFunction.VLookup(Cells(i, 1), "M14:P20", 3) And chislo <> Application.WorksheetFunction.VLookup(Cells(i, 1), "M14:P20", 4)) Then
Изменено: Крекер - 26.06.2018 17:48:24
 
Перейти    
Использование VLookUp В макросе
 
#
26.06.2018 17:25:34
Подскажите как правильно использовать ВПР (VlookUp) в макросе? Ругается на неизвестную функцию
Или есть вариант как то обойтись без ВПР?
Суть вопроса:
Есть некая база (таблица 2): первый столбец индивидуальный индекс, еще 3 столбца: некие числа от 1 до 12.
Есть основная таблица, в которой идет работа. Необходимо по поступающему от пользователю числу (от 1 до 12), скрыть те строки, у которых отсутствует поступившее число в столбцах с данными таблицы 2. при совпадении индекса.

возникает ошибка:
Цитата
run-time error '1004':
Невозможно получить свойство VLookup класса WorksheetFunction

P.s. пробовал использовать Application.WorksheetFunction.VlookUp, ругается на невозможность использовать свойство VlookUp
Изменено: Крекер - 26.06.2018 17:47:48
 
Перейти    
Посчитать сумму в ячейке из определенных листов (VBA)
 
#
02.02.2018 12:48:46
при нажатии на Cancel в окне ввода номеров листа (я ведь правильно понимаю, прекращение ввода листов только через cancel?), вылетает ошибка

Run-time error '9':
Subscript out of range

эта ошибка на 19 строке, если верить Debug

P.s. понял, там вместо m должно быть n
Изменено: Крекер - 02.02.2018 12:51:08
 
Перейти    
Посчитать сумму в ячейке из определенных листов (VBA)
 
#
02.02.2018 11:49:12
Добрый день.
Есть файл со большим количеством листов.
Сложить ячейку по всем листам просто:
Код
=Сумм(Лист1:Лист100!A1)

Кроме того необходимо посчитать сумму в ячейке A1 например из 5 и 7 листа (листы должны выбираться пользователем)

Как я понимаю надо выводить пользователю список всех листов на экран, и галками пользователь должен выбирать нужные, чтобы в итоге сам  макрос создал формулу вида:

Код
=Сумм(Лист5!A1;Лист7!A1)

Или может есть встроенные возможности Excel позволяющие сложить ячейку с произвольных листов по выбору?
 
Перейти    
Совмещение информации из разных файлов., Как совместить? Автоматизация
 
#
26.01.2018 15:47:48
на правах дилетанта.
Скопировать данные из одного файла в другой, предварительно добавив отсутствующий столбец (например собственность- в одном файле он есть, в другом нет).
Отсортировать по уникальному столбцу (например код плательщика).
И далее макросом в цикле через IF сравнить код плательщика у текущей строки и следующей, и в случае повторения удалять вторую строку, предварительно скопировав/суммировав задолженность или еще какие-либо необходимые строки.
Возможно такой макрос можно записать формулой
 
Перейти    
Изменить связи с внешней книгой через макрос
 
#
26.01.2018 15:01:01
ошибка Run-time error '13':
Type mismatch
на строке 28 ( aL = importWB.LinkSources(xlExcelLinks))

и еще этим макросом он открывает файл, на который меняются ссылки
Изменено: Крекер - 26.01.2018 15:02:46
 
Перейти    
Изменить связи с внешней книгой через макрос
 
#
26.01.2018 14:14:58
Добрый день. Пытаюсь сделать "Изменение связи с внешней книгой через макрос". Поиском была найдена тема http://www.planetaexcel.ru/forum/?FID=8&PAGE_NAME=read&TID=36243.
Макрос был немного изменен. Но есть некоторые моменты от которых хотелось бы избавиться.
Код
Sub Мак2()
   Range("Q3") = Replace(Range("G8").Formula, "=", "")
   exlink = "=SUBSTITUTE(MID(Q3,1,FIND(""Лист"",Q3,1)-2),""["","""")"
   Range("Q4") = exlink
   exlink = Range("Q4")

   With Application.FileDialog(msoFileDialogFilePicker)
.Show
 strwPath = .SelectedItems(1)
End With

ActiveWorkbook.ChangeLink Name:= _
        exlink _
       , NewName:= _
       strwPath, _
       Type:=xlExcelLinks
   
      
       End Sub

В указанной теме приходилось в ручную прописывать путь к файлу, с которым связана текущая книга.
Немного изменив оригинал получилось, то что в примере, но при этом все равно задействуются 2(Q3 и Q4 ) ячейки, а объединить формулы в первых 4 строках не получается.
Можно ли как то от этого избавиться, или есть более простой вариант менять связи?
Изменено: Крекер - 26.01.2018 14:15:39
 
Перейти    
Страницы: 1 2 След.
Наверх