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

Страницы: 1 2 След.
Поиск ячеек, которые (не)соответствуют определенным условиям. VBA Excel.
 
Цитата
vikttur написал:
Совершенно не меняет.
Ок. Спасибо.

Цитата
vikttur написал:
А в этом разделе "один вопрос - одна тема".
Понятно.
Поиск ячеек, которые (не)соответствуют определенным условиям. VBA Excel.
 
Цитата
Mershik написал:
пока для раздумий
и спасибо за пример.

PS Конечная цель, проанализировать, как я использую свое время. Сколько в % времени уходит на тот или иной вид работ, как эти %% зависят от дня недели и\или времени суток, и др. В планах сделать что-то типа Dashbord: сводные таблицы, графики. Возможно, в дальнейшем – визуализация в BI…
Изменено: avbook - 06.04.2021 22:29:21
Поиск ячеек, которые (не)соответствуют определенным условиям. VBA Excel.
 
спасибо за комплимент,

Цитата
Mershik написал: а в каком виде все это в результате должно быть?
В моих планах было сделать следующее:
1) Все строки с ошибками скопировать на отдельный лист, в каждой строке справа добавить ячейку с тестовым описанием ошибок. Пример: «Длительность равна 0:00, Лишние пробелы в Поле «Группа работ».
2) В исходном листе заливкой указать все ячейки(!) с ошибками.
3) В исходном листе выбрать Автофильтр по Столбцу «Строка», т.е показать все ошибки. Установить курсор в начало такой отфильтрованной таблицы. Примечание: Автофильтр можно установить по номерам строк. Номер строки планировал взять из соответствующего столбца, т.е. не адрес ячейки.
3.1) При каждом запуске макроса чистить вышеуказанную заливку. И устанавливать Автофильтр на исходную таблицу в режиме «Показать Все».
4) Если ошибок нет – лист ошибок не создавать. Установить курсор в последнюю строку исходной таблицы.

Здесь, Вы можете заметить, есть дубляж: и отдельно ошибки на листе, и еще Автофильтр строк с ошибками в исходной таблице.
Поиск ячеек, которые (не)соответствуют определенным условиям. VBA Excel.
 
vikttur, спасибо за совет.

Одно уточнение, пожалуйста. Мне следовало бы сразу сказать обо всех деталях. Извините, не хотел заранее «грузить».
Проверка ячеек идет не только по числам, но и по тексту: ищем ячейки с пробелами в начале или в конце, и еще ищем пустые ячейки. А также для двух числовых столбцов выполняется проверка, что число из нее входит в массив допустимых значений. Я выложил файл, где есть выдержка из таблицы. В следующем сообщении.

Это как-то меняет подход к решению задачи?

Mershik, В моем случае – это можно назвать TimeSheet. Пример и пояснения в файле. Я веду его уже год с небольшим. Сейчас стал анализировать (графики, сводные таблицы) и обнаружил ошибки. Конечно, можно все поправить руками. Но мне стало интересно – как решить эту задачу макросом. Появилась мотивация начать изучать макросы. За год в реальном файле уже 7975 строк.
Поиск ячеек, которые (не)соответствуют определенным условиям. VBA Excel.
 
Добрый день!

Есть таблица в Эксель. 9 столбцов, примерно 8 тыс. строк, в потенциале 20 тыс. Каждый столбец должен заполняться по определенным правилам. Задача – проанализировать таблицу и скопировать на другой лист все строки, в которых есть ошибки с указанием ошибки или списка ошибок, если в одной строке их несколько. Из 9 столбцов – только 5 подлежат проверке, в остальных содержаться формулы.

Пока планирую методом перебора каждой строки в цикле. Примерно так. Это набросок, а не реальный код.

Код
If Cells(i, “a”) <> Cells(i-1,”b”) Then CodeErr = "Значение А не равно предыдущему значению В"
ErrNar = СodeErr

If Cells(i, “с”) <> 1330 Then CodeErr = "Неправильное значение С"
    If ErrNar <> 0 Then
    ErrNar = ErrNar + ", " + СodeErr
    Else
    ErrNar = CodeErr
    End If

If Cells(i, “d”) = 0 Then CodeErr = "Значение D равно нулю"
    If ErrNar <> 0 Then
    ErrNar = ErrNar + ", " + CodeErr
    Else
    ErrNar = CodeErr
    End If

If ErrNar <> 0 Then
MsgBox "Список ошибок в строке " & i & “: “& ErrNar
Else
MsgBox "Нет ошибок в строке "& i
End If

CodeErr=0
ErrNar = 0


Прошу дать совет, как лучше организовать алгоритм. Возможно, есть более быстрый и оптимальный, чем тот, который использую, т.е. чем перебор в цикле каждой строки. Я новичок в VBA, поэтому прошу строго не судить и ответить подробнее, по возможности

Заранее большое спасибо.
Копирование диапазона с переменным адресом. Только значения. VBA.
 
Цитата
МатросНаЗебре написал:
Вот ещё вариант с меньшим количеством указаний Sheets(SourceSheetName).
Спасибо.
Ваш первый вариант кажется проще, т.к. я еще не освоил функцию Resize. Остановлюсь пока на нем. Думаю, что он мне подходит,   т.к.  структура (количество и название столбцов)  анализируемой таблицы и таблицы результатов одинаковая и не меняется.
Изменено: avbook - 29.03.2021 16:53:53
Копирование диапазона с переменным адресом. Только значения. VBA.
 
Решение оказалось проще, чем ожидал.
Спасибо всем!
Копирование диапазона с переменным адресом. Только значения. VBA.
 
МатросНаЗебре,  спасибо и Вам!
Это работает.
И работает, если не указывать Sheets(SourceSheetName) дважды.
Код
Sheets(ErrorSheetName).Range(Cells(ErRow, "a"), Cells(ErRow, "i")) = Range(Sheets(SourceSheetName).Cells(SrRow, "a"), Sheets(SourceSheetName).Cells(SrRow, "i")).Value
Изменено: avbook - 29.03.2021 16:44:18
Копирование диапазона с переменным адресом. Только значения. VBA.
 
vikttur,  спасибо за пример кода.
При использовании этого кода, имеет ли значение, какой лист активен в данный момент?
Копирование диапазона с переменным адресом. Только значения. VBA.
 
Цитата
vikttur написал:
Все Cells указаны для активного листа
В таком случае, если правильно понимаю, мой метод вообще не подходит?
Можно ли это сделать, чтобы не учитывать, какой лист активен в данный момент?
Копирование диапазона с переменным адресом. Только значения. VBA.
 
Добрый день!
Надо скопировать диапазон ячеек на другой лист. Только значения.
Вот такой код работает.
Код
ErRow = 2
Sheets(ErrorSheetName).Range(Cells(ErRow, "a"), Cells(ErRow, "i")) = Sheets(SourceSheetName).Range("a5770: i5770").Value

А такой нет...
Код
ErRow = 2
SrRow = 5770
Sheets(ErrorSheetName).Range(Cells(ErRow, "a"), Cells(ErRow, "i")) = Sheets(SourceSheetName).Range(Cells(SrRow, "a"), Cells(SrRow, "i")).Value

Подскажите, как исправить.
Планирую изменять переменные ErRow и SrRow. Поэтому первый вариант, где явно указан адрес диапазона – не устраивает.
Я новичок в VBA, поэтому прошу строго не судить и ответить подробнее, по возможности.
Заранее спасибо.
Макрос который заменяет сводную таблицу на простую таблицу с сохранением формата ячеек
 
Цитата
Jack Famous написал:
Вот обработанный вариант (вставляет выделенный диапазон на новый лист):
Большое спасибо. Этот вариант работает, если выделить именно сводную. А если выделить весь лист - то форматирование слетает.
Трудность в том, что мне надо "заменить" - в том же файле, на том же месте -  сводную на "простую" таблицу. И еще в том, что я заранее не знаю, сколько будет сводных, на каких листах и сколько сводных на листе...
Ваш макрос очень удобный, но "по факту" не избавляет от ручного труда.
Макрос который заменяет сводную таблицу на простую таблицу с сохранением формата ячеек
 
Цитата
Mershik написал:
вы же это считаете сводной ? -
Вы правы - про такую сводную я говорю.
Макрос который заменяет сводную таблицу на простую таблицу с сохранением формата ячеек
 
Цитата
Mershik написал:
avbook ,а где хоть одна сводная таблица? и вы не обновляйте ее и все
Если нужно приложить файл со сводной, то я, конечно, это сделаю. Но я заранее не знаю, какая именно будет сводная, и сколько их будет. И еще может быть более одной сводной на одном листе.
По поводу "не обновляйте" - надо сделать так, чтобы пользователь конечного файла (директор) "не заморачивался"...
Создание макроса автоматического сохранения в формате без формул
 
Добрый день!

В ранее обсуждаемой теме был опубликован макрос, который заменяет формулы на значения.
Прикладываю файл, в котором есть этот макрос. Он заменяет формулы на всех листах, но игнорирует сводные таблицы.
Макрос который заменяет сводную таблицу на простую таблицу с сохранением формата ячеек
 
Добрый день!

В ранее обсуждаемой теме (ссылка ниже) был опубликован макрос, который заменяет формулы на значения.

https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=60110&am...

Помогите, пожалуйста, расширить этот макрос – чтобы он сохранял «как значения» и сводные таблицы тоже. С сохранением форматов ячеек.

Задача: есть файл, в котором несколько листов, на которых могут быть таблицы с формулами, сводные таблицы и графики. Формулы могут содержать ссылки на другие книги. Надо передать этот файл другому пользователю, чтобы он мог его открыть (через Excel или Google Tabs) и чтобы формулы не «слетели».

В ручном режиме пользуюсь «Вставить, как значения». Для сводных – два раза «Вставить, как значения» и «Вставить форматирование».

Вышеприведенный макрос очень помогает, но игнорирует сводные таблицы. Выкладываю файл с этим макросом.

Заранее спасибо.
Визуализация данных – какой продукт выбрать?, Прошу дать совет общего характера, в каком направлении решать задачу
 
Sertg, Ваш скрин-шот впечатляет! Спасибо за ссылку на статью. Буду изучать.
Визуализация данных – какой продукт выбрать?, Прошу дать совет общего характера, в каком направлении решать задачу
 

Добрый день!

Есть задача анализа и визуализации данных.

Данные – сведения о бронировании автомобилей. Они выгружаются с сайта. На листе «Данные» это столбцы А-Р. Три правых синих столбца – дополнительные, вычисляются по формулам, которые «протягиваются» уже потом – после выгрузки данных.

Анализ данных – на листе «Анализ». Реализован с помощью функций СУММЕСЛИМН и СЧЁТЕСЛИМН с учетом Периода (ячейки В3-М3) и Статуса (ячейка В2).

Задача получить такой анализ для каждой пары столбцов «Страна» (столбец N) и «Место полученияСтрана» (столбец R).

В принципе, несложно сделать сводную таблицу, как показано на листе «Свод». Однако реальные данные содержать несколько тысяч строк и сводная получается очень большая.

Хотелось бы показывать не все пары, а только первые (максимальные) 10. Причем по каждому показателю (Комиссия, АрендоДни, Цена и др.) будет своя 10-ка максимальных. Стандартный фильтр "первые 10" собирает 10 максимальных броней – но не 10 максимальных сумм "по парам".

Сейчас все (кроме сводной таблицы) реализовано в ГуглТаблицах. Это связано с пожеланием шефа, что бы отчет можно было просматривать через Интернет. Шеф использует Mac и не очень приветствует MS Excel…

Собственно, вопрос:

Какой продукт вы можете посоветовать для этой задачи? Для создания чего-то типа «дашборд»…

Из-за недостатка опыта не знаю, куда направить усилия: формулы в Гугл Таблицах (инструменты Гугл предпочтительней – шефу удобнее пользоваться через web), Google Data Studio, Excel, Power BI или что-то другое?

Примечание:

Конечно, мне хотелось бы наиболее простой инструмент (на сегодняшний день знаком только с Excel и Google Tabs), чтобы уменьшить время на его освоение и показать шефу результат в минимальные сроки.

Заранее спасибо.

PS

Надеюсь, что не нарушаю Правила, разместив вопрос об этом и на другом форуме

http://www.excelworld.ru/forum/23-43984-1

Изменено: avbook - 31.01.2020 21:59:21 (Забыл приложить файл.)
Как очистить «пустые» ячейки?, Ячейки, в которых есть содержимое, которое не отображается
 
Цитата
Sceptic написал:
У меня такой вариант сработал
Спасибо !!! "Заработало !"
Как очистить «пустые» ячейки?, Ячейки, в которых есть содержимое, которое не отображается
 
Цитата
Sceptic написал:
пересчет стоит ручной?
Верно. Я копировал из рабочего файла. Там ручной пересчет. Сам пересчет занимает секунд 30-40, поэтому я поставил ручной.
Как очистить «пустые» ячейки?, Ячейки, в которых есть содержимое, которое не отображается
 
vikttur Спасибо. Я видел макросы на других форумах. Но пока количество ячеек не так велико. Мне  проще через Del (это помогает), чем тратить время на освоение макросов (я пока с ними "на вы").
Как очистить «пустые» ячейки?, Ячейки, в которых есть содержимое, которое не отображается
 
Цитата
vikttur написал:
Выделить и нажать Delete
Т.е. вручную?
Дело в том, что в реальном файле такие ячейки "разбросаны" по таблице. И это занимает слишком много времени...
Как очистить «пустые» ячейки?, Ячейки, в которых есть содержимое, которое не отображается
 

Добрый день!

Ситуация следующая: есть Гугл Таблица, из нее при помощи запроса Query заполняется промежуточная таблица Excel, далее Copy-Paste данные переносятся в рабочий файл. Так делается ежемесячно – в рабочем файле хранятся данные за все периоды.

Я обнаружил, что в рабочем файле есть ячейки, которые внешне кажутся пустыми, но на самом деле содержат что-то. В моем примере я выделил такие ячейки желтой заливкой. Эти «пустые» ячейки влияют на работу сводных таблиц и еще, наверное, увеличивают объем файла. В реальном файле около 10,000 строк.

Вопрос: как очистить такие «пустые» ячейки? Есть ли относительно простой способ (без макросов), который можно было бы повторять каждый месяц, и который не изменял бы данные в рабочем файле.

Или может надо что-то докурить в запросе Query?

Заранее спасибо.

Быстрый поиск уникальных пар значений
 
Цитата
TheBestOfTheBest написал:
Может сводную формировать через Power Query?
Не знаком с Power Query, к сожалению... А можно увидеть пример?
Быстрый поиск уникальных пар значений
 
vikttur, все исправил.  Спасибо за замечание. Учту на будущее.
Быстрый поиск уникальных пар значений
 
Цитата
БМВ написал:
В таблице еще много расчетов (в столбцах)  и на SUMIF(S)....
Добрый день всем! Как автор таблицы могу сказать, что из 5500 строк собирается около 200 пар значений. Далее для каждой пары по формуле СУММЕСЛИМН идет расчет 2-х показателей за каждый месяц за 4 года. т.е.  около 20,000 ячеек. И еще на  других листах есть около 8000 ячеек с формулами СУММЕСЛИМН.
Цитата
БМВ написал:
при этом есть что считать или нет - было отдано на откуп многочисленным суммам.
"Пустых расчетов" немного - около 20 строк. Формула определения пар "протянута" вниз с запасом, чтобы пользователь, который вносит данные "не беспокоился" о появлении новых пар. Иначе пришлось бы каждый раз проверять, все ли пары вошли в расчет. Ну и конечно, Эксель не знает, для какой пары в каком месяце нет оборотов. Это еще одни источник "пустых" суммирований.
Изменено: avbook - 16.07.2018 14:08:28
Быстрый поиск уникальных пар значений
 
Цитата
gling написал:
Вариант формулами с доп столбцами которые можно скрыть.
БОЛЬШОЕ спасибо gling за решение в сообщении #8 ! Немного доработал под себя и все работает! Сокращение времени почти в 8 (восемь!) раз. Обсчет всего файла занимает примерно 40 сек. Вот готовое решение.
Быстрый поиск уникальных пар значений
 
Цитата
БМВ написал:
все верно, порой пара столбцов делает расчет в разы проще
БМВ Вы уже писали это где-то. Поэтому я и обратился к Форуму.  Когда сам пришел в тупик - нужен свежий взгляд и более опытный.
Скрытый лист - отличная идея!
Быстрый поиск уникальных пар значений
 
gling Вы используете ссылку на весь столбец типа А:А, можно наверное для сокращения времени сделать "закрытый" диапазон типа $A$1:$A$545 ?
PS Уже сегодня не смогу испытать на своем "живом" файле...За целый день уже "глаза" в кучку. Напишу Вам позже, что получилось в этой теме.
Спасибо!
Быстрый поиск уникальных пар значений
 
БМВ, еще раз Вам большое спасибо. Но я точно к ночи уже торможу немного... Вы поставили фильтр "неравно пустому значению" ? В сообщении #4
Страницы: 1 2 След.
Наверх