Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1
SUMIFS формула, в которой в 1 из критериев 2-ое условие, по типу или-или.
 
Здравствуйте, друзья!

Нужен Ваш совет. Искал на форуме, есть схожие темы, но ответа не нашел.

Задача простая. Нужно подтянуть данные формулой SUMIFS. Но, в одном из критериев формулы, должно быть 2-ое условие, то есть как бы 2 критерия в 1 критерии.
Более детальное описание задачи во вложении-примере. Все расписано подробно. Надеюсь на Вашу помощь.

Заранее спасибо!
Выделить ячейку следующую после последней заполненной ячейки в определенной строке через VBA
 
Друзья, нужен Ваш добрый совет!

Вопрос примитивный. Есть таблица. У таблицы есть шапка. Шапка всегда во второй строке. Незаполненных ячеек в шапке нет. Количество же столбцов может варьироваться. Нужно выделить ячейку следующую после последней заполненной ячейки в шапке. То есть в файле-примере надо выделить ячейку O2.

Поиском по формуму тоже пользовался. Все вокруг, да около, а нужного примера, к сожалению не нашел.

Пробовал некие варианты, не подошли.
Код
With ActiveCell.EntireRow
  .Cells(.Cells.Count).End(xlUp).Activate
End With

Cells(2, Columns.Count).End(xlToRight).Select

Range(Cells(2, Columns.Count + 1)).Select

Range("A2" & Cells(Columns.Count, 1).End(xlRight).Row + 1).Select
Заранее спасибо.
Подтянуть данные формулой SUMIFS из другой книги через ВБА
 
Здравствуйте, друзья!

Подскажите, пожалуйста!

Я через ВБА макросом вношу простую формулу в ячейку. Но, формула ссылается на другую закрытую книгу. Открывать ее желания нет совсем.
В одном файле когда через ВБА вношу формулу ВПР в ячейку, также ссылаясь на закрытую книгу - без всяких проблем получаю нужный результат.
Однако, в другом файле использую SUMIFS по той же логике, и вместо данные получаю #VALUE!. Только если открыть книгу, тогда появляются данные.
Не могу почему ВПР не требует открытия книги для отражения данных, а SUMIFS требует. Что делаю не так?

Код
Range("E2").Formula = "=IFERROR(VLOOKUP(D2,'X:\[EXAMPLE1.xlsx]Sheet1'!$A$1:$B$1000,2,0),""xxx"")"   -   работает
 Range("J3").Formula = "=SUMIFS('X:\[EXAMPLE2.xlsx]MAIN'!$D:$D,'X:\[EXAMPLE2.xlsx]MAIN'!$A:$A,F3,'X:\[EXAMPLE2.xlsx]MAIN'!$C:$C,"">=""&C3,'X:\[EXAMPLE2.xlsx]MAIN'!$B:$B,""<=""&C3)"    -    не работает

Заранее всем спасибо!

Поиск ближайщей среды от текущей даты
 
Здравствуйте друзья!

Прощу помочь советом.

Задача проста как 3 копейки. Каждый день будет открываться определенный файл. Необходимо, чтобы в этом файле в ячеку А1 при открытии автоматически вписывалась дата. Эта дата должна быть ближайщей средой. Если на день открытия файла так и так среда - то соответсвенно просто текущую дату внести.
Мне нужна простая формула, которая поможет это решить, что-то типа if, today, +/-. Как формулу при открытия файла в лист впилить - разберусь, мне бы саму формулу. Если есть вариант Макросом, тоже неплохо.

Спасибо всем заранее.
Удаление всех закрашенных строк по столбцу А через ВБА, Выделить целиком все строки по критерию закрашенности ячеки в столбце А
 
Здравствуйте, друзья!

Снова нужна Ваша помощь. Искал по темам, не сумел найти того, что мне нужно.

Задача такова:

Есть таблица. Ее нужно очистить от ненужных строк. Все ненужные строки по колонке А окрашены в какой-либо цвет (не УФ). Все неокрашенные ячейки в столбце А нужно оставить. Макрос, должен, соответственно, быть таким: сканируем Интервал А1:A1000 например, ищем все что равно какому-либо цвету, кроме без цвета, затем выделяет целую строку и удаляем. В общем-то вполне тривиальная задача. Есть аналогичный код, которые удаляет пустые строки, надо его чуть подправить, чтоб удалял по цветным ячейкам.
Код
Sub DeleteEmptyRows()

Dim ws As Excel.Worksheet
Dim LastRow As Long

Set ws = ActiveSheet
LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
With ws.Range("A7:A" & LastRow)
    If WorksheetFunction.CountBlank(.Cells) > 0 Then        ' вот в эти строках полагаю надо что-то изменить типа .Interior.Color <> 0 
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete    ' или Blank (какой код для незакрашенных ячеек?
    End If
End With

End Sub

Пример по вложении. Заранее всем спасибо за советы.

P.S. в данном примере после работы Макроса должны сохраниться строки А7:A8 и А22:A25  + A30:A31 и A52:A57. Сканировать столбец А можно с ячейки А7 и до последней заполненной. Ну в коде это есть в общем.
Изменено: Paul Zealand - 1 дек 2018 08:06:32
Возврат результата формулой SUMIFS внутри интервала дат по нескольким критериям, Один из критериев SUMIFS вход даты на главном листе в интервал дат с другого листа, откуда возвращается результат
 
Здравствуйте, друзья!

Нужен Ваш добрый совет!

SUMIFS формула не выводит верный результат. Уже голову сломал - не могу понять почему. Пример во вложение.
Суть проста:
Есть SHEET1, на нем в столбец N надо вывести с помощь SUMIFS формулы данные с листа SHEET2.
Критерии следующие: имя - тут проблем нет; Дата с SHEET1 должна находиться в интервале между двумя датами со страницы SHEET2 - соответственно, тут использую как бы 2 области с одним критерием, и что-то мне подсказывает, что именно тут какой-то подвох выскакивает. Хотя использовал подобную формулу, просто идентичную, с такой же морфологией, прям один в один, тоже с датой в интервале дат, и все работало. Много раз. А вот сейчас почему-то не работает. Никак не пойму почему. Подскжите, люди добрые, пожалуйста!  
[ Закрыто] SUMIFS, Не возвращает нужный результат
 
Здравствуйте, друзья!

Нужен Ваш добрый совет!

SUMIFS формула не выводит верный результат. Уже голову сломал - не могу понять почему. Пример во вложение.
Суть проста:
Есть SHEET1, на нем в столбец N надо вывести с помощь SUMIFS формулы данные с листа SHEET2.
Критерии следующие: имя - тут проблем нет; Дата с SHEET1 должна находиться в интервале между двумя датами со страницы SHEET2 - соответственно, тут использую как бы 2 области с одним критерием, и что-то мне подсказывает, что именно тут какой-то подвох выскакивает. Хотя использовал подобную формулу, просто идентичную, с такой же морфологией, прям один в один, тоже с датой в интервале дат, и все работало. Много раз. А вот сейчас почему-то не работает. Никак не пойму почему. Подскжите, люди добрые, пожалуйста!  
Изменено: Paul Zealand - 27 ноя 2018 08:39:49
VBA: Выделить следующую видимую ячейку при автофильтре
 
Здравствуйте, друзья! Нужен Ваш совет.

Задача банальная. Есть таблица, есть автофильтр. Сортирую скажем столбец D по определенному критерию. А далее мне нужно просто выделить ячейку следующую после D2, то есть D3, нотак как использован фильтр она может быть и D150, то есть Offset должен происходить именно видимой области и именно выделить 1 ячейку, а не всю таблицу или область. Пытался всяко, не получается, то всю таблицу выделяет, то весь лист. Пробовал следующее:
Код
    Set rn = [D2]
    rn.Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
    
    Set HJ = Cells(3, 4).Offset(1, 0)
    HJ.SpecialCells(xlCellTypeVisible).Select
    ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
    Set tbl = Sheets("MAIN").AutoFilter
    Set HTL = tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count - 6).SpecialCells(xlCellTypeVisible)
    HTL.Select
Формула VLOOKUP через VBA с искомой переменной, вместо фииксированной ячейки
 
Здравствуйсте, друзья!

Помогите разобраться почему код выдает ошибку?
Код
Sub EXAMPLE ()
Dim rn As Range

Application.FindFormat.MergeCells = True
Set rn = Cells.Find("", After:=ActiveCell, SearchFormat:=True)
rn.Select

Sheets("MAIN").Cells(5, 5).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(" & rn.Value & ",Sheet1!C[1]:C[2],2,0)"
End Sub

Ошибку выдает на последней строчке, не хочет вводить такую формулу в ячейку, ругается. (1004 ошибка). Не могу понять почему.

Суть проста: есть переменная, хочу чтобы VLookup делал поиск не по фиксированной ячейке, а по переменной объявленной в коде. Переменная объявлена как Range, как видно из кода выше это объединенная ячейка на листе. Хочу чтобы VlookUp мне искал содержимое объединенной ячейки в фиксированном месте.
Сразу несколько оговорок. Объединенная ячейка всегда будет в разных местах по ходу кода, поэтому и надо поиск делать с помощью переменной, а не фиксированно. Пробовал также ActiveCell = Selection.Application.WorksheetFunction.VLookup(rn, Sheet1.Range("B100:C100"), 2, False), тоже не работает. Почему через формулу не работает тоже не пойму, вроде Амперсанды выставил, вроде указал чтобы искал значение, а не саму переменную, так как она объявленная не как String, а как Range. Видел примеры подобного кода, все работало.

Заранее всем спасибо
Сумма по товару в зависимости от календарного месяца
 
Уважаемые гуру всея Эксель!

Снова нужен Ваш мудрый совет.

Есть таблица с данными. По вертикали в строках позиции товаров, в столбцах по горизонтали даты.
Нижу таблицы пара товаров из той же самой таблицы. Напротив каждого товара надо посчитать сумму каждой ячейки по этому товару в зависимости от календарного месяца из этой таблицы. То есть, в ячейке B17 кол-во товара за Май месяца. а в С17 количество товара за Июнь месяц. То же самое B18 и С18.
Выполнить задачу надо с помощью макроса, так как в будущем таблиц таких на листе будет много и месяцев может быть больше.
Реализовать бы с помощь SUMIF, но не знаю как суммировать по горизонтали. Желательно, чтобы это был какой-то цикл. Сам вижу алгоритм примерно таким:
- Для удобства ввел формулу месяц, с порядком месяца вверху таблицы. Надо чтобы Макрос находил под таблицей нужный товар (это могу offset-ами сделать), то есть встать в ячейки нужны не проблема. А вот далее надо чтобы суммировать только строку из таблицы совпадающую с именем товара (первый критерий) и только те столбцы где номер месяца одинаков (второй критерий). Грубо говоря надо встать в ячейку B17 и просуммировать из таблицы строку с именем нужного товара и по колонкам в рамках календарного месяца.

Мудрено написал. Не знаю как проще объяснить, извините. Буду признателен за любой совет. Заранее спасибо
Вставить время в текстовую строку в виде числа
 
Здравствуйте, друзья!

Задача очень простая. Прошу Вашего мудрого совета.

Есть колонка со временем, формат соответствующий. Есть текстовая колонка. Надо объединить обе ячейки обычной &. Проблема в том, что когда объединяешь временной формат с помощью формулы, он переводит время в число, как обычно эксель отсчитывает от 1900 года. В итоге получается белиберда. А надо чтобы в ячейке с формулой резьтат склеивания был идеентичен цифрам, которые отображает числовой формат. То есть 13:50 должен превратиться в 1350, 10:30 в 1030, 02:00 в 0200. Как поменять формат в обычный, и добавить вперед нули я знаю, этот вариант не подходит. Не подходит также ни одна из формул ЦЕЛОЕ, ДЕНЬ, ГОД, ЧАС, всевозможные деления на 24, 60 и т.д. По теме искал, ответа не нашел. Пробовал Макросом скрепить ячейки через value все равно тот же результат. В общем во вложении пример, там все понятно, что надо и как есть. Буду весьма признателен за помощь. Заранее спасибо.
Изменено: Paul Zealand - 4 июн 2018 18:59:51
MACROS вставки формулы в активную ячейку
 
Всем привет!
Друзья стоит простая задача, сделать хоткей-макрос, который будет вносить простую формулу в активную ячейку.
Формула простая =subtotal(9,G3:G100000).
Проблема в том, что активная ячейка каждый раз будет из разного столбца. То есть, если активная ячейка А1, то формула должна быть =subtotal(9,А3:А100000),
если активная B1, то формула должна быть =subtotal(9,B3:B100000), если активна С1, то и формула должна быть с колонкой С, то есть =subtotal(9,С3:С100000).
Диапозон всегда можно фиксированный с 3 по 100000 ячейку, а вот столбец всегда меняется в зависимости от активной ячейки.

Когда в ВБА пытаюсь объявить переменную как ActiveCell, он естественно выдает ошибку. Идея была примерно такая:
Код
Dim x as Range
x = ActiveCell
ActiveCell.Formula ("=subtotal(9,"x3:x100000")

Ну примерно в таком стиле. Возможно, есть альтернативный путь решения поставленной задачи. Пожалуйста, подскажите. Спасибо всем заранее.
З.Ы. гуглил, форум листал, ответа не нашел. :(
Поиск макс. и мин. значений и вывод информации в отдельную ячейку
 
Здравия всем обитателям форума!

Задача:
В столбце А наименования различных, пусть будет товаров.
В ячейках B1, C1, D1, E1 находятся названия 4 разных фирм, которые, собственно, и продают эти товары. И дальше вниз по этим столбцам идут цены напротив каждого наименования из столюца A.
В следующий столбец F необходимо вывести текст, который содержит наименованием фирм с наибольшей ценой и с наименьшей ценой.
Для этого я использовал слишком уж громоздкие и уродливые формулы, и результат все равно получился не наилучшим. См. вложение
Суть в том, чтобы в одной формуле, или, например, макросом, если так будет удобнее заполнить столбец F с учетом всех возможных вариантов, а именно:

- когда максимальная цена есть только у 1 фирмы
- когда максимальная цена есть у 2-х фирм (одинаковая)
- когда максимальная цена есть у трех фирм (это не обязаетльно, но не если и это условие удастся выполнить, то вообще идеально).
- когда минимальная цена есть только у 1 фирмы
- когда минимальная цена есть у 2-х фирм (одинаковая)
- когда минимальная цена есть у трех фирм (это не обязаетльно, но не если и это условие удастся выполнить, то вообще идеально).
- когда нет цены, пустая ячейка, вместо слов - MIN/MAX PRICE вывести - NO PRICE.
- И САМОЕ ГЛАВНОЕ, чтобы все эти условия были скомбинированы в этой 1 формуле или Макросе. То есть, если, например, есть 2 фирмы с максимальной ценой, 1 фирма с минимальной цено и 1 фирма без цены вообще, вывести название 2 фирм и MAX price, название 1 фирмы и MIN price, и название Фирмы и NO Price.

То, что я попытался сделать в примере, это посчитать формулой в одном столбце все варианты при минимальных ценах, в другом все варианты при максимальных ценах, в третьем столбце тупо скомбинировал результаты предыдущих 2 столюцов. И все равно, не выводит фирмы без цены, не считает 3 одинаковых макс. и мин. цены.

В общем, надеюсь, есть пусть проще, чем тот, что я избрал и кто-то подскажет его.
Как с помощью VBA скопировать данные из списка поочередно в определенную ячейку
 
Здравствуйте, добрые люди!

Задача такова:

Есть простой список с текстом в столбце А. Сколько в нем строк неизвестно, то есть количество строк будет постоянно меняться. Нужно сделать так, чтобы макрос копировал поочередно значения всех ячеек в другую ячейку, ну скажем в С1. Но, только всегда в одну и ту же ячейку С1. До тех пор пока в столбце А не закончатся данные, ну и на выходне нужно оставить в ячейке С1 значение последней ячейки в столбце А.
Во вложении, пример, как должно все выглядеть, но только надо сделать, наверное, циклом это, или как-то еще, не так как я копировал вручную каждую ячейку.

Заранее признателен за любой совет.
Страницы: 1
Наверх