Профессиональные приемы работы в Microsoft Excel

Обмен опытом => Microsoft Excel => Тема начата: mixail64 от 12.09.2018, 22:46

Название: расчет мощности в заданые часы
Отправлено: mixail64 от 12.09.2018, 22:46
Уважаемые знатоки!
Прошу оказать помощь в составлении формулы для выбора максимальной мощности в заданные часы
Извиняюсь, выложил испорченный файл, исправил.
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 13.09.2018, 14:40
Добрый день,
Такие вещи проще делать сводной таблицей
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 13.09.2018, 15:30
Большое спасибо за помощь, я этого никогда не делал, буду учится.
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 14.09.2018, 11:38
Сделал как показали сводную таблицу, но вставить автовыбор по условию не получается. Подскажите что я делаю не так. Условия на странице "часы мощности"
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 14.09.2018, 13:19
Добрый день,
Цитата: mixail64 от 14.09.2018, 11:38
что я делаю не так.
в моей сводной соединены 2 таблицы.
Вам нужна сводная по нескольким диапазонам данных. Здесь (https://www.planetaexcel.ru/techniques/8/133/) можно детально почитать. А в Гугле (https://www.google.de/search?q=%D0%BF%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%B5%D0%BD%D0%B8%D0%B5+%D1%81%D0%B2%D0%BE%D0%B4%D0%BD%D0%BE%D0%B9+%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D1%8B+%D0%BF%D0%BE+%D0%BD%D0%B5%D1%81%D0%BA%D0%BE%D0%BB%D1%8C%D0%BA%D0%B8%D0%BC+%D0%B4%D0%B8%D0%B0%D0%BF%D0%B0%D0%B7%D0%BE%D0%BD%D0%B0%D0%BC+%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85&oq=%D0%BF%D1%80%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%B5%D0%BD%D0%B8%D0%B5+%D1%81%D0%B2%D0%BE%D0%B4%D0%BD%D0%BE&aqs=chrome.2.69i57j0l5.8799j0j7&sourceid=chrome&ie=UTF-8) даже видео найдете.
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 14.09.2018, 15:39
Спасибо, буду изучать дальше.
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 17.09.2018, 22:48
Уважаемый, boa! Если можно объясните более подробно как делается первая сводная таблица, в которой ссылка на таблицу в которой задаются часы мощности. У меня 13 листов одинаковых расчетов по разным точкам учета, я их сделал, но в каждой часы и даты нужно выбирать вручную, а хотелось бы задать их в одной таблице, а в остальных выставилось автоматически. По указанным, Вами, ссылкам разобраться не получилось. Заранее спасибо.
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 18.09.2018, 08:18
Здравствуйте, Михаил
а какая у Вас версия офиса?
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 18.09.2018, 10:17
2010
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 18.09.2018, 11:08
не имею под рукой 2010-го, но нашел для вас инструкцию от сапорта (https://support.office.com/ru-ru/article/%D0%A1%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D0%B5-%D0%BC%D0%BE%D0%B4%D0%B5%D0%BB%D0%B8-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85-%D0%B2-excel-87e7a54c-87dc-488e-9410-5c75dbcb0f7b)
попробуйте, все же разобраться, т.к. описывать достаточно много :( Нет сейчас свободного времени.
Обращаю внимание, что в инструкции под "Таблицы" имеются ввиду "умные таблицы". Что бы быстро преобразовать любую вашу таблицу в умную таблицу, сделайте следующие шаги:
1. выделите любую ячейку в Вашей таблице,
2. нажмите последовательно комбинации клавиш "Ctl+A"(А - английская), "Ctrl+L", "Enter"

Запомните или переименуйте в запоминаемое имя созданной таблицы. Оно вам понадобится для создания модели данных.
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 18.09.2018, 11:22
Спасибо, буду пробовать.
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 18.09.2018, 15:26
Если я правильно понял делаем с помощью Power Query модель данных, а потом в Power Pivot делаем сводную таблицу.
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 18.09.2018, 17:11
Это один из путей.
Если вы его поняли, то отлично.
Получилось?
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 18.09.2018, 23:17
Смысл кажется понял, но пока ничего не получается.
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 18.09.2018, 23:31
Это то что получилось, с ручным выбором часов.
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 19.09.2018, 10:33
Добрый день,
попробую в картинках объяснить на вашем примере.
После преобразования ваших таблиц в умные(ниже я писал как это делается) формируем сводную таблицу (Clip1.jpg)
В качестве источника данных выбираем коннекты (Clip2.jpg)
В открывшемся окне окне выбираем вкладку Таблицы (Clip3.jpg)
В ней вы увидите имеющиеся в данной книге "Умные таблицы"
Выбираем любую, это сейчас не столь важно.
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 19.09.2018, 10:47
нажимаем "ОК" и расставляем поля.
В свойствах таблицы выбираем "Все" (Clip4.jpg) и у Вас появятся все умные таблицы.
После добавления поля из второй таблицы, появится окно установки связей.
Не доверяйте автоматической установке и нажмите "Создать"
Выберите связи как на рисунке Clip5.jpg
Дальше смотрите в файле.
Формулы, которые я переписал, выделил зеленым.
На странице "отчет" показал как использовать ссылки на умные таблицы и пользоваться именованными диапазонами, которые предварительно создал на странице "СН2"
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 19.09.2018, 11:43
Огромное спасибо за такое подробное объяснение!!!!!
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 19.09.2018, 15:43
Уважаемый boa. Я думаю причина моего непонимания в Excel 2010. Я делаю умные таблицы, перехожу в формирование сводных таблиц, выбираю внешний источник данных, выбираю подключение, а там пусто. И на этом останавливаюсь. Создание модели с помощью Power Query тоже не дало результата. В каком Excel делаете вы?
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 19.09.2018, 17:10
я работаю в 2013 и 365-м офисах
завтра попробую сделать для вас другим способом. Но там надо будет немного знать SQL и VBA
ну нет у меня 2010-го :) не могу проверить
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 20.09.2018, 09:51
Цитата: mixail64 от 19.09.2018, 15:43
выбираю внешний источник данных, выбираю подключение, а там пусто.
А вкладка Таблицы у вас есть(Clip3.jpg)? она тоже пустая?
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 20.09.2018, 11:36
Нет даже вкладки "Таблица" только "Существующие подключения"
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 21.09.2018, 09:58
это фото экранов
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 21.09.2018, 15:13
Добавил в файл макрос и форму
Для создания нового коннекта запустите макрос AddCoonectToTables

после этого создайте на основе созданного коннекта сводную таблицу
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 21.09.2018, 15:17
А вот и сам файл
    Следующая строка должна создавать сводную таблицу на основе созданного коннекта, но не работает
    выдает ошибку "Потеряна связь с Microsoft Excel для просмотра присоединенных листов". Причину нагуглить не смог :(
    Может кто-то знает причину и поможет отредактировать.
    ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=NewConn).CreatePivotTable TableDestination:=Selection(1)

P.S.
Цитата: boa от 13.09.2018, 14:40
Такие вещи проще делать сводной таблицей
Да уж, не получилось "проще" :)
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 21.09.2018, 16:35
Да все совсем не просто. Сделал как написано, все получилось и пришел к той же ошибке
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 21.09.2018, 22:54
Цитата: mixail64 от 21.09.2018, 16:35
...все получилось и пришел к той же ошибке
что опять не так?
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 22.09.2018, 17:39
Нет все получилось, то компьютер глюкнул. А так при смене часов все пересчитывает, т.е. то что мне хотелось. Благодаря Вам я узнал много нового. Огромное Вам спасибо!!!
Название: Re: расчет мощности в заданые часы
Отправлено: mixail64 от 22.09.2018, 17:41
А вообще это для сравнения стоимости электроэнергии по ценовым категориям, может еще кому то понадобится.
Название: Re: расчет мощности в заданые часы
Отправлено: boa от 22.09.2018, 23:17
А вообще, суть решения была в построение сводной таблицы из двух диапазонов
тема избитая...
может, как-то я этот макрос доработаю..., выложу в готовые решения. Идей много.
сейчас у меня есть макрос который создает OLEDB-коннекты для экселевских, текстовых и аксесовских файлов. sql'ку я потом дописываю ручками. а вот вариант с программным созданием sql-запроса, думаю, имеет путевку в жизнь...
если тема интересна - пишите комменты или в личку