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

Страницы: 1
Запрос 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
Наверх