Уважаемые форумчане, подскажите, пожалуйста по такой проблеме:

Что имеем: файл Excel в котором есть модель данных. Все данные (таблицы фактов, справочники) загружаются с SQL сервера и подключение было создано непосредственно через Power Pivot (не через Power Query).

Что необходимо: необходимо для запроса ввести параметр. Пользователь на листе меняет в ячейке год и, соответственно, данные обновляются.

Насколько я понимаю, для запросов, созданных через Power Pivot нельзя вводить параметр? Если я жму в Power Pivot свойства таблицы - дизайн - изменить как текст, то я вроде как вижу, что для фильтров WHERE можно поставить галочку Параметр, но как его назначить так и не понял.

Если все же сделать параметр нельзя, то есть ли какой-либо способ поменять источник на Power Query, чтобы не воссоздавать все с нуля, переделывая на Power Query, воссоздавая связи, меры и т.д.?

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

Как он работает сейчас:
1. Работает только на активном листе.
2. Пробегает по строкам из диапазона ActiveSheet.UsedRange.

Как необходимо, чтобы он работал?
1. Собираем перечень листов файла, в которых в ячейке B1 стоит "X". "X" в ячейке B1 это индикатор того, что на этом листе необходимо будет подбирать высоту объединенных ячеек.
Перечень листов сформирован и теперь необходимо осуществить подбор высоту по очереди на каждом листе из пункта 1 по алгоритму.
2. Меняем ActiveSheet.UsedRange на LastRow по столбцу B каждого листа.
3. От LastRow до строки 1 (или наоборот, это не принципиально) осуществляем подбор высоты, при условии, что в ячеке B* стоит слово "comment".

Таким образом, в файле макрос должен отработать на листе "1" в ячейках C4 и C6 и на листе "3" в ячейке C5.

Добрый день, необходимо написать 2 макроса для 2 файлов.

Первый файл: "1. template..."
Необходимо:
1) перенести данные с листа Planning на лист 1C. Переносим только значения и форматы. На листе Planning формулы, но мне пришлось их убрать, чтобы пройти по размерам прикрепляемого файла.
2) Какие строки и столбцы необходимо оставить? Те, которые помечены слово "save" в столбце A / строке 1. Кол-во строк и столбцов меняется, поэтому необходимо переносить данные с учетом этого факта (последняя активная строка/столбец) на листе и т.п.
Для этого фала я уже сделал подобие макроса, но он нестабильно работает, когда открыты еще книги Excel

Второй файл: "2. template..."
Смысл такой, же, что и в первом файле, за исключением того, что данные должны быть перенесены на соответствующие листы по дням недели "пн", "вт", "ср"... и т.д. (строки для переноса обозначал именами листов и словом save).
И во втором файле добавляется еще условие, что наименование продукции, помеченной для переноса может быть пустым - такие строки либо не переносим сразу либо зачищаем после переноса.

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

Добрый день, есть следующие данные:

id	price	check	manual	final check
1	5	OK		OK
2	5	OK		OK
3	4	NO	OK	OK
4	5	OK		OK
5	5	OK		OK
6	5	OK		OK

При этом id (номер фактуры) и price (цена) это результат запроса с sql сервера.
Далее я добавляю столбец проверки check, в котором простая формула, что если цена 5 руб, то OK, в противном случае NO.
Далее я смотрю все NO и если у меня есть согласование на применение цены, отличной от 5 рублей, то вручную ставлю OK.
И final check тоже становится OK, так как в manual OK.

Проблема в следующем: если я удалю из базы фактуру #2, то ручной OK "переезжает" из фактуры №3 в фактуру №4.

id	price	check	manual	final check
1	5	OK		OK
3	4	NO		NO
4	5	OK	OK	OK
5	5	OK		OK
6	5	OK		OK

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

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

Доброе утро. Обнаружил, что в сводной таблице пропала возможность выделить столбцы, строки или все промежуточные итоги по столбцу.
То есть перестала появляться черная стрелка на краях ячеек сводной таблицы. Сталкивался ли кто-нибудь с такой проблемой?

Windows 10, Excel 2016 Pro. Полагаю, что это может быть связано с установкой последний обновлений. К сожалению, не удалось выполнить восстановление системы, так как просто пропали точки восстановления.
 

Добрый вечер, ищу более оптимальную/эффективную и т.д. формулу. Задача такая: представим что мы продаем кефир и молоко и своим покупателям мы будем платить бонусы. Правила начисления бонусов:
1. За кефир платим 0% в 2017 году. За молоко платим 5% в 2017 году.
2. При этом покупателю Иванову за покупку молока платим не 5%, а 7%.
3. При этом если это молоко бренда "Веселая молочница" то Иванову платим 0% в первом полугодии и 15% во втором полугодии.

Таким образом, получаем систему условий

Тип   продукта	Клиент	Бренд	Период с	Период по	Бонус
Молоко	Иванов	Веселая молочница	01.01.2017	30.06.2017	0,0%
Молоко	Иванов	Веселая молочница	01.07.2017	31.12.2017	15,0%
Молоко	Иванов		01.01.2017	31.12.2017	7,0%
Молоко			01.01.2017	31.12.2017	5,0%

Выгрузка из системы выглядит так:

Тип   продукта	Клиент	Бренд	Дата отгрузки
Кефир	Сидоров	Муму	01.02.2017
Молоко	Сидоров	Муму	01.02.2017
Кефир	Иванов	Муму	01.05.2017
Молоко	Иванов	Муму	01.05.2017
Молоко	Иванов	Веселая молочница	01.05.2017
Молоко	Иванов	Веселая молочница	01.10.2017

Необходимо прописать формулу, которая бы к каждой строчке выгрузки добавляла бы % бонуса, который необходимо заплатить клиенту. Мне удалось прописать такую формулу, но она довольно громоздкая и с использованием доп.столбцов. Ищу варианты ее оптимизации.

Добрый день. Прошу помощи с решением задачи или идей по ее решению.

Есть таблица на листе "условия", в которой прописаны критерии, даты действия критериев и значения, которые надо будет вернуть в выгрузку.

уровень	критерий 1	критерий 2	дата с	дата по	значение
1	а	1	01.08.2017	15.08.2017	0%
1	а	1	16.08.2017	31.08.2017	10%
2	а		01.08.2017	31.08.2017	15%

Важный момент - если в выгрузке критерии сходятся с критериями по уровню 1, то возращается соответствующее значение, а уровень 2 игнорируется. Если критерии не сходятся по уровню 1, то идет проверка по уровню 2. В столбце значения могут быть 0% для принудительного обнуления и остановки дальнейшей проверки. Если бы 0% отсутствовали, то проблемы бы не было. Пустое значение по критерию 2 означает, что там может быть любое значение и проверка будет только по критерию 1.

Выгрузка выглядит так. К ней необходимо добавить "значение" с листа "условия"

критерий 1	критерий 2	дата
а	1	10.08.2017
а	1	25.08.2017
а	2	10.08.2017

Финальный корректный результат такой:

критерий 1	критерий 2	дата	значение
а	1	10.08.2017	0%
а	1	25.08.2017	10%
а	2	10.08.2017	15%

Формулой ВПР задачу не решил, так как период действия может быть с 01.08.2017 по 15.08.2017, а в выгрузке дата 10.08.2017. Игнорировать дату нельзя.
СУММЕСЛИМН также не подошел. Если бы не было значений 0%, то можно было бы написать, что если суммеслимн по двум критериям не равно 0, то возращать суммеслимн по обоим критериям, если равно 0, то возращать значение только по одному критерию. Но 0% есть и СУММЕСЛИМН по двум критериям <> 0 уже не пропишешь. Если бы СУММЕСЛИМН возращал ошибку при условии хотя бы одного несоответствия в диапазонах проверки, то проблема была бы решена добавлением ЕСЛИОШИБКА.

Буду благодарен за любую помощь в решении задачи. Спасибо.

Добрый день, помогите пожалуйста, разобраться со следующей проблемой.

На лист 'Data' подгружаются данные в умную таблицу. В этой умной таблице производится ряд дополнительных вычислений и преобразований (именно поэтому не выгружаю данные сразу в сводную таблицу). Из умной таблицы строится сводная таблица. Пользователь на листе со сводной таблицей может поменять один из параметров, который влияет на данные, выгружаемые с сервера (выбирает год и внешнее подключение обновляет данные в умной таблице).
Выбор года производится в ComboBox.

Я пробовал 2 варианта и в обоих следующая проблема: у меня сложилось такое впечатление, что запрос данных во внешнем источнике еще не завершен, а Excel уже пытается обновить сводную таблицу. В результате появляется такая ошибка: "Run time error 1104: Application-defined or object defind error" и ругается на строкуПри этом если ошибку пропустить, то продолжается запрос данных в умную таблицу из внешнего источника.

1. Данные в умной таблице автоматически обновляются при изменении значения в ComboBox и затем в ячейке ref!$F!1 (через параметры подключения) + строка макроса для обновления сводной таблицы

2. Данные в умной таблице и в сводной таблице меняются макросом

Pelena, спасибо огромное, открыл для себя новые возможности. В общем то разобрался, как работает функция ТЕКСТ с форматом "0;\0", но никак не мог понять, что означает --. Собственно, на форуме и нашел ответ. Всем спасибо большое, и Pelena и сайту и Kuzmich.

Доброго времени суток. Таблица с исходными данными, в которой есть две даты - дата начала и дата окончания (дата начала 25.05.2016, дата окончания 28.08.2016, например. Разница между этими датами 74 дня). Как с помощью формулы массива посчитать сумму дней (из всей таблицы), приходящихся отдельно на май, отдельно на июнб и т.д.? В файле более наглядно (надеюсь) представлена задача. Спасибо.

Всем спасибо, задача решена. Код от Максима идеально подошел под задачу.

Добрый день, просьба не воспринимать мою фразу в том смысле, что ваш код плохой. Прошу прощения, что не указал эти моменты изначально. Ваш код не подходит, так как результат необходим на листе stock в столбце "T", при этом строчки удалять нельзя, так как потеряем цифры по остаткам на складах.

Уважаемые участники форума, готов оплатить услуги того человека, который доведет код до ума.

Максим Зеленский, что-то не получилось применить к своему примеру, код выдает ошибку. Если честно, то не удалось разобраться в самом коде и не получается самому исправить ошибку. Я еще применил более оптимизированный код от k61 для формирования массивов, может в этом причина? Не могли вы бы посмотреть, что в коде не так?

[USER=40201]JayBhagavan[/USER, спасибо, интересное решение, если вместо $T2 поставить СУММЕСЛИМН, то задача будет решена без дополнительных столбцов. Если не получится дописать макрос, воспользуюсь данной формулой. А можно нечто подобное реализовать в vba? Есть массив, в котором в строке 4 элемента и необходимо неким образом перебрать массив таким образом, что если набор из первых 3 элекментов встречается в массиве ранее, то 4 элемент равен 0?

Спасибо вам за оптимизацию имеющего кода. Экономический смысл необходимого результата следующий: по данным листа stock строится сводная таблица и если оставить сумму резервов в каждой строчке, то в итоге в сводной таблице резервы удваиваются/утраиваются и т.д. Наборы "артикул - завод - склад" изначально не уникальны, так как сток разбивается по поставщику/партиям и т.д.

Вопрос остается открытым - как оставить сумму резервов только в одном из всех повторяющихся наборов (артикул - завод - склад).

Всем доброго времени суток. Прошу помочь в решении следующей задачи: необходимо просуммировать на листе stock значения с листов open и delivery по 3 критериям. Но при этом на листе stock набор из 3 критериев может встречаться более 1 раза, а сумма необходима только в одном из этих наборов (в принципе не важно, будет ли это первый набор или последний или набор из середины массива). Задачу хотелось бы решить через макрос, а не через формулы, так как важна скорость обработки (да и сделать это через формулы без использования дополнительных столбцов я не смог). Прикладываю два файла - решение задачи через формулы с использованием доп.столбцов (для наглядности необходимого результата) и файл с макросом (кто-то скажет, что макрос не оптимален и будет прав,но что смог, то смог), в котором мне удалось найти все необходимые суммы, но не удалось справиться с проверкой на наличие набора критериев в массиве выше текущей строки. Буду благодарен за любую помощь. Спасибо.

Уважаемые форумчане, просьба помочь. На листе "Stock" имеется выгрузка из системы, на втором листе "data" то, что хотелось бы получить в итоге. К сожалению, не обладаю умом программиста, совсем запутался в циклах и никак не могу получить желаемый результат.  
 
Выгрузка будет всегда однообразная (строка артикула - ниже штуки - ниже рубли - пустая строка - следующий артикул), но с разным кол-вом строк.  
 
p.s. ниже артикула всегда стоит "1", ее необходимо игнорировать.

Всем огромное спасибо, очень и очень помогли!!!

файл

Подскажите, пожалуйста, необходимо столбец "А" разделить по частям, разделитель ",". И для каждого вновь отделенного столбца необходимо поставить формат "text", иначе данные преобразуются в дату.  
 
Часть текста макроса:  
FieldInfo _  
:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), _  
Array(7, 2), Array(8, 2), Array(9, 2)), TrailingMinusNumbers:=True  
 
Загвоздка в том, что вновь получаемых стольцов может быть 2, а может быть и 102.  
Вариант FieldInfo _:=Array(Array(1, 2),...........Array(102, 2) видится очень уж громоздким.  
Можно ли как то прописать в макросе, что у всех столбцов, сколько бы их не было, стоял формат "text", т.е. Array(...,2)  
<EM><STRONG>Файл удален</STRONG> - велик размер - [<STRONG>МОДЕРАТОРЫ</STRONG>]</EM>

{quote}{login=slav}{date=19.01.2011 11:27}{thema=Re: Re: }{post}{quote}{login=interspb78}{date=19.01.2011 10:58}{thema=Re: }{post}{quote}{login=slav}{date=18.01.2011 11:09}{thema=}{post}может виртуалка поможет?  
 
95000{/post}{/quote}  
 
Может я чего-то не понимаю, виртуалка в плане Alcohol? Но у меня нет проблем с установкой офиса...{/post}{/quote}  
 
Virtual PC  
VMWare  
сие есть виртуальная машина со своей операционкой  
я такую делаю для всяких тестов.  
Основная рабочаю система Win XP SP3, а на виртуалке Win Server 2003 со своим набором софта.  
Вам для каких целей нужно иметь две языковые версии одного продукта? если не секрет, конечно...{/post}{/quote}  
 
совсем не секрет, учебники и разную литературу читаю на русском языке и для русских офисов, а на работе офис весь английский, вот и пытаюсь преодолеть этот языковой барьер )))  
как вариант можно установить multilanguage pack, но для 2003 офиса его нереально найти. А может есть офисы уже с поддержкой нескольких языков?

{quote}{login=slav}{date=18.01.2011 11:09}{thema=}{post}может виртуалка поможет?  
 
95000{/post}{/quote}  
 
Может я чего-то не понимаю, виртуалка в плане Alcohol? Но у меня нет проблем с установкой офиса...

{quote}{login=}{date=18.01.2011 11:18}{thema=}{post}А какой должен запускаться по умолчанию? Может тот и стоит ставить на диск С, причем в последнюю очередь?{/post}{/quote}  
 
мне все равно, какой будет запускаться по умолчанию, просто сейчас какой бы я не запускал, запускается русский, думаю, что если поставлю английский на C, а русский на D, то по умолчанию будет запускаться английский, а русский не будет запускаться :(

Подскажите пожалуйста, как можно установить 2 офиса на одном компьютере, причем оба офиса 2003, только один русифицирован, а другой на английском языке? Я установил их один на диск C, другой на диск D, но при запуске английского офиса (диск D) все равно запускается русский (который ставил на диск C).