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

Страницы: 1
Выбор строк между двумя метками, Выбрать строки, которые находится между 1-м и 2-м, 3-м и 4-м, 5-м и 6-м и т.д. появлениями ключевого значения в столбце
 
#
27.07.2024 20:05:59
Добрый день, коллеги!

Есть список событий – таблица: Дата, Начало, Окончание, Длительность, Группа. Необходимо выбрать дни, которые находятся между группой «Дорога» включительно.

Имеется в виду, что сначала едем «туда», потом «обратно». И надо проанализировать дни, когда мы были «там», включая дни приезда \ отъезда. При этом надо взять все события за выбранные дни.

Реализовал это на Power Query. Мой запрос выбирает из списка именно такие дни для дальнейшего анализа. См. приложенный файл. Там четыре вкладки: исходные данные, схема решения,  решение и вопрос. В реальной таблице около 35,000 строк.

В принципе, все работает. Но недавно столкнулся с проблемой. Если в полночь ты находишься "в дороге", то алгоритм неправильно определяет дни «туда \ там \ обратно». Прошу помощи, что можно с этим сделать? Без макросов.

Спасибо.
 
Перейти    
Запрос Power Query к файлу на Яндекс Диске, Совместная работа с файлом с запросом Power Query
 
#
15.12.2022 14:49:58
Добрый день!

Знаю, что не рекомендуется повторять вопрос в разных форумах. Приношу извинения. Но не получил ответа на дружественном форуме.

Файл источник и файл с запросом PQ находятся на Яндекс Диске на аккаунте «Владельца». Владелец синхронизировал Яндекс Диск с личным ПК. Владелец предоставил полный доступ «Программисту» к папкам с этими файлами, который также синхронизировал эти папки со своим личным ПК.

Владелец сам вносит изменения в файл источник. Он не знает PQ, ему нужна только «кнопка Обновить», чтобы можно было пользоваться результатом. Запрос должен написать Программист, который работает на удаленке.

Вопрос.
Можно ли и если «можно», то как написать запрос, чтобы он работал и у Владельца, и у Программиста?

Спасибо.

Ссылка http://www.excelworld.ru/forum/2-50998-1
 
Перейти    
Вычисляемое поле в Сводной с условием ЕСЛИ по тексту, Настройка вычисляемых полей в сводной таблице
 
#
22.06.2022 12:29:28
Добрый день!
Подскажите, пожалуйста, можно ли в вычисляемом поле в сводной настроить формулу с условием ЕСЛИ по тексту.
Формулу вида: = если( Документ = "Банковская выписка"; Сумма * -1; Сумма)
Спасибо.
 
Перейти    
Power Query. Заменить значения в некоторых ячейках согласно таблице замен
 
#
30.09.2021 11:33:17
Добрый день!

В видео от Николая Павлова был описан простой способ замены текста
https://www.planetaexcel.ru/techniques/7/13923/
Однако он имеет недостаток: меняется не вся ячейка целиком, а текст «внутри» ячейки. В результате могут появиться ненужные дублирования.

К примеру, имеем пары для замены:
«Амортизация» – новое значение «Амортизация ОС и НМА»
«Амортизация НМА» – новое значение «Амортизация ОС и НМА»

Результат выглядит так:
Амортизация ОС и НМА
Амортизация ОС и НМА НМА - здесь есть дублирование "НМА"

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

Спасибо.
Изменено: vikttur - 30.09.2021 11:39:52
 
Перейти    
Поиск ячеек, которые (не)соответствуют определенным условиям. VBA Excel.
 
#
06.04.2021 19:09:11
Добрый день!

Есть таблица в Эксель. 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.
 
#
29.03.2021 16:25:31
Добрый день!
Надо скопировать диапазон ячеек на другой лист. Только значения.
Вот такой код работает.
Код
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, поэтому прошу строго не судить и ответить подробнее, по возможности.
Заранее спасибо.
 
Перейти    
Макрос который заменяет сводную таблицу на простую таблицу с сохранением формата ячеек
 
#
15.01.2021 17:10:27
Добрый день!

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

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

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

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

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

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

Заранее спасибо.
 
Перейти    
Визуализация данных – какой продукт выбрать?, Прошу дать совет общего характера, в каком направлении решать задачу
 
#
31.01.2020 21:54:33

Добрый день!

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

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

Анализ данных – на листе «Анализ». Реализован с помощью функций СУММЕСЛИМН и СЧЁТЕСЛИМН с учетом Периода (ячейки В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 (Забыл приложить файл.)
 
Перейти    
Как очистить «пустые» ячейки?, Ячейки, в которых есть содержимое, которое не отображается
 
#
19.03.2019 18:11:46

Добрый день!

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

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

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

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

Заранее спасибо.
 
Перейти    
Быстрый поиск уникальных пар значений
 
#
12.07.2018 22:34:13

Добрый день!

Прошу уважаемое сообщество помочь. Необходимо находить уникальные пары значений. На одном листе пара Контрагент-СтатьяБюджета, на другом Контрагент-ГруппаСтатейБюджета.

Предложенное решение в рабочем файле с 5500 строк на листе «Реестр» требует 8 минут на обсчет. Как повысить быстродействие файла? Надо сохранить структуру файла, т.к. в реальной таблице на каждом из листов справа идут вычисления, которые здесь не показаны.

Заранее спасибо.
 
Перейти    
Поиск по двум таблицам по разным условиям, без макросов
 
#
09.07.2018 20:22:38
Добрый день!

Есть «Реестр» операций, в котором наряду с другими данными есть три текстовых столбца «Контрагент», «Статья Бюджета» и «Группа Статей Бюджета». Надо заполнить текстовый столбец «Группа Статей Бюджета». Есть две таблицы соответствия  этих параметров: «Исключения» и «Обычная зависимость». Сложность в том, что при разных комбинациях «Контрагент» - «Статья» надо искать значения «Группы» в разных таблицах соответствия. Задачу надо решить без макросов.

Коротко алгоритм поиска выглядит так: Если текущая пара "Контрагент-Статья Бюджета" есть в таблице "Исключения", то Группа выбирается из таблицы "Исключения"; иначе - Группа выбирается из таблицы "Обычная зависимость".

У меня есть решение задачи, но я не уверен, что оно хорошее. С точки зрения быстродействия. У меня в этом файле около 6000 строк в «Реестре» и еще много других формул массива и формул СУММЕСЛИМН и СУММПРОИЗВ. Обсчет файла занимает около минуты. (Excel 2016).

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

Заранее большое спасибо.
 
Перейти    
Какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?, сравнение Excel и Гугл Таблиц
 
#
06.07.2018 15:40:02
Уважаемые коллеги.
Работал ли кто-то из Вас с Гугл Таблицами? Все ли функции Excel там работают?

Делал работу для заказчика. Все настраивал в Excel, а потом, когда по требованию заказчика перенес на Гугл, обнаружил, что некоторые формулы считают неправильно или выдают синтаксическую ошибку. В частности, речь идет о формулах
Код
=СУММПРОИЗВ((A1:A2922=A6005)*(E1:E2922=B6005);B1:B2922)
=+СУММПРОИЗВ(СУММЕСЛИМН(Реестр!$I$1:$I$111;Реестр!$W$1:$W$111;"01-18";Реестр!$E$1:$E$111;"Валюта"))

Собственно, вопрос я бы поставил шире: какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?

Также буду благодарен за ссылки на материалы по Гугл Таблицам.
Спасибо.
 
Перейти    
Поиск уникальных пар значений по двум столбцам
 
#
06.07.2018 14:17:19

Добрый день!

Есть задача подсчета сальдо расчетов по каждой Статье Бюджета в разрезе Контрагентов для Баланса. При этом надо различать положительные и отрицательные значения, чтобы потом «отнести» их, соответственно, в Актив или в Пассив баланса. Есть лист с хозяйственными операциями – РеестрОпераций, на других листах идут вычисления.

Сложность в том, что пара Контрагент - СатьяБюджета заранее неизвестна. Поставщики могут меняться и \ или могут появиться новые виды расходов. Поэтому возникает необходимость каждый раз искать уникальные пары значений по двум тестовым столбцам.

Я нашел решение задачи здесь https://www.planetaexcel.ru/forum/?PAGE_NAME=read&FID=8&TID=24364. Большое спасибо пользователю МСН! Но обнаружил существенный недостаток: если удалить строку в исходном диапазоне, то формула выдает ошибку #ССЫЛКА!. Лист РеестрОпераций – это по сути рабочий лист или база данных, куда вручную заносятся данные из разных источников, поэтому совсем без ошибок и удалений обойтись сложно.

Помогите, пожалуйста, разобраться в чем тут дело или предложить другой способ решения без макросов (я оными не владею).

Файл прилагаю. Excel 2016.

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