Спасибо!
Как все легко и просто, и даже через сумму можно найти вычисляемую ячейку

Добрый день!
Есть классическая таблица, где столбцы - это календарные месяцы, строки - это параметры. Нужно найти, к примеру, сумму значений за последние 12 месяцев по заданному параметру и заданной дате. Недавно для себя открыл функции ПРОСМОТРХ, где с помощью 2 вложений данной функции можно легко и изящно вычислять значение на пересечении строки и столбца:
ПРОСМОТРX(значение_вертикальное;диапазон_поиска_столбец;ПРОСМОТРX(значение_горизонтальное;диапазон_поиска_строка;массив_данных))

Собственно, чтобы вычислить диапазон по условию, объединил вычисленные начальную и конечные ячейки диапазона через ":", пример во вложении.

Подобное вычисление диапазона на листе будет использоваться порядком в 24 столбцах * 200 строк = 4 800 раз (переписываю бизнес-модель и ищу пути оптимизации автоматизации расчета)
Вопросы: можно ли упростить данную формулу или применить другие формулы и будет ли это сказываться на быстродействии? (фактически исходные данные для расчета суммы из примера обновляются раз в месяц)

Ранее пробовал вычислять значения на основе пересечения именованных диапазонов, очень много мороки с именнованными диапазонами.
Вычисления ячейки на пересечении через функцию СУММПРОИЗВ работает до тех пор, пока в диапазоне указаны только числа.

Либо же тут пока не попробуешь, не узнаешь...  

Добрый день!
Необходимо на периодической основе заполнять таблицу из csv, где в csv каждый период это строка, а в excel это должен быть новый столбец, при этом добавляя новые строки / столбцы при их отсутствии. Более подробно в ТЗ. Для себя вижу как обработку VBA
В архиве ТЗ в doc, выгрузка в csv и рабочая книга xls для загрузки. В xls и csv приведены упрощенная выгрузка, в рабочей выгрузке будет около 250-300 записей для каждого периода (месяца).

Прошу кратко описать подход к реализации, назвать срок и стоимость

Добрый день!
Excel 2021, сборка 2108, W11
По ПКМ нет меню Вставить заметку/примечание, но ее можно вставить через shift+F2. Если в ячейке уже есть заметка, то по ПКМ доступно редактировать/удалить заметку.
Можно ли добавить на ПКМ вставить заметку? либо же  на ленту добавить кнопку создания заметки? поковырялся в настройках других команд ленты, но так и не нашел ничего связанного с заметками

а почему вы пишите, что лучшей обычной таблицей xlsx?

Добрый день!
Есть таблица со столбцами (дата, параметр, значение), которая выгружается из 1С (в примере диапазон "исходные данные").
Нужно перевернуть таблицу, где каждая уникальная дата будет отдельным столбцом (в примере диапазон "конечные данные"). При перевороте нужно добавлять новые строки и столбцы (при их появлении в исходных данных и отсутствии в конечных), сортировать диапазон с конечными данными. Это планируется сделать посредством VBA.
Данные из 1С можно копировать на лист в excel и потом обрабатывать их макросом, но можно сразу сразу выгружать из 1С в определенном формате (json, xml, cvs), и этот файл скармливать на обработку макросу. На текущий момент данные из 1С представляют собой одну таблицу, но потом в дальнейшем планируется добавить выгрузку дополнительных данных (таблицы будут иметь разную структуру).
Вопрос в следующем, в каком формате лучше выгружать данные для обработки? так как нужно будет написать ТЗ на выгрузку из 1С, и отдельно ТЗ на обработку макросом (скоординировать работу 2 специалистов).

Доброго времени суток!
Потребовалось написать бизнес план с нуля. Для анализа имеем диапазон данных 400 строк параметров х 36 столбцов (месяцы) - упрощенно вкладка "ввод данных" в примере. Количество строк с различными вычислениями составило 1000 строк. Все расчеты выполнялись на одной вкладке с данными, причем фактически основным вычислением являлось нахождение среднеарифметическое значения за 12 месяцев определенного параметра (к примеру на вкладке "расчеты"), а дальше над среднеарифметическими значениями производились вычисления.
Файл оказался не юзабельным для последующего ввода новых данных в столбцы в связи с хаотически разбросанными данными для ввода и строк с расчетами. В данном случае можно либо скомпоновать блоки для ввода данных, блоки расчетов , блоки вывода информации на одном листе , либо же вносить данные на одной вкладке, производить вычисления на другой вкладке (в примере вкладка "расчеты"), а результат компоновать на третьей вкладке (к примеру вкладка "вывод данных"), при этом названия столбцов будет идентичными для всех трех вкладок..
Самым простым при обращении к диапазону на другой вкладке указать диапазон с ячейками формата А2:F2 (к примеру), а какие еще могут быть изящные способы получения данных для вкладки "расчеты"?

Не могу разобраться, что обозначает диапазон 3:3 в формуле ИНДЕКС и диапазон 2:2 в формуле ПОИСКПОЗ. Разбирал через вычислить формулу, также смущает, что в функции ИНДЕКС второй аргумент должен указывать на номер строки, но фактически возвращает номер столбца.

Подскажите, а есть ли вариант решения, если диапазоны A2:AA7 (дни) и AB2:AB7 (цены) находятся на разных листах, диапазон цен транспонирован (столбцы - даты, строки - услуги), и не имеют одинаковую высоту строк для функции СУММПРОИЗВ?

Есть прайс на услугу (№, дата начала, дата окончания, цена), внутри месяца может быть установлено более одной цены (в примере - март 2022).

На первом этапе подсчитываем вхождение календарного месяца в период из прайса, то есть кол-во календарных дней срока действия цены (спасибо за помощь от Ігор Гончаренко)
На втором этапе умножаем кол-во календарных дней действия цены из 1-го этапа на стоимость услуги за день в периоде, но так как в марте 2022 15 дней была одна цена, а следующие 16 - другая, то в формуле повторяем умножение для каждого из периодов (от 1 до 5). как можно оптимизировать, чтобы в формуле не суммировать все периоды, и при добавлении нового периода не добавлять новую строку в формулу?.

Пример - это краткая выдержка, по факту будет считаться выручка для более чем 100 услуг, на отдельной вкладке массив с услугами / ценами за периоды, нужная цена подтягивается через СУММЕСЛИМН.

очень круто, спасибо!

Добрый день!
Есть массив значений со столбцами дата начала и дата конца. Есть матрица со столбцами - календарные месяцы. Нужно посчитать кол-во дней вхождения временного интервала из массива дат в каждый из 12 календарных месяцев. Более подробно - в примере.

Вроде задача простая, но не смог написать универсальную формулу без привязки к годам.

Добрый день!
Имеется статистика продаж по дням с 01.01.2017 по 31.08.2022 (вкладка данные). Попробовал спрогнозировать данные с 01.09.2022 по 31.12.2023 с помощью ПРЕДСКАЗ.ETS, получился ровный боковичок, вообще ни о чем, хотя четко прослеживается сезонность зимних против летних периодов, вручную поставил сезонность на 365 (автоматом определял как 28), все же зимние периоды (январь 2023) слишком завышен.
Далее решил попробовать использовать статистику помесячно, опять таки январь 2023 слишком завышен, ручная правка сезонности на 12 (определил автоматом как 11) не сильно повлияла..
Я понимаю, что данная функция не панацея, а всего лишь некий инструмент, но может можно добиться более чувствительного расчета? кто давно в теме...

Можно присвоить каждому из столбцов A, B, C, D, E соответствующее имя диапазона, и подставлять диапазон через функцию =ДВССЫЛ(E4), к примеру =СУММ(ДВССЫЛ(E4)).

Добрый день!
Имеется 2 массива: Спецификация (с разбивкой на сам товар и его комплектующие для производства товара) и Производство (производство товара за периоды). В 3-м массиве нужно посчитать количество использованных комплектующих за период. Длина каждого из массивов может достигать до 10 тысяч строк. Расчет выполнен с использованием функции СУММЕСЛИМН в 3 шага - более подробно во вложении.
Основная проблема в том, что функция СУММЕСЛИМН не умеет отрабатывать условие ИЛИ(1;2), поэтому приходится дублировать функцию СУММЕСЛИМН для условия 2 и так далее., что сказывается на скорости расчета, так как входные и выходные массивы состоят/участвуют в других расчетах (пересчет формул до 2-3 секунд 4-х ядерном процессоре 2010г., на Xeon E5-2660 расчет выполняет за 0,2 секунды, но и массивы далее будут увеличиваться в размерах). Возможно есть способ оптимизации расчёта либо применив формулу массива (что возможно не даст выигрыша в скорости), либо же используя другую логику?

Спасибо! все ок!

Добрый день!
К примеру есть диапазон ячеек 1 строка - 30 столбцов, в каждом столбце стройки в ячейках находятся формулы с именами диапазонов (которые отличаются только именами диапазонов, функции одинаковые).Все было хорошо, пока не пришло время редактировать формулы: проще конечно отредактировать одну формулы в столбце, скопировать ее на соседние столбцы и потом вручную в каждом столбце менять имя диапазона, но долго и нудно.
Возможно ли в ячейке столбца указать имя диапазона как текст, и подставить этот текст в формулы? Что бы потом при протягивании формулы подхватывалось нужное имя диапазона? Пробовал разные варианты склейки/подстановки, не получается

Спасибо за ответы, пока к сожалению ни с VBA, ни с MSQuery не знаком, Малой кровью судя по всему не отделаешься(

Добрый день!
Вопрос по теории по мотивам данной темы https://www.planetaexcel.ru/techniques/2/224/

Имеется исходный массив значений порядка 10 тысяч строк. Из данного массива нужно возвращать числовые значения по 2,3 условиям в другой массив порядка 1000 строк и 5 столбцов. На текущий момент использую функцию СУММЕСЛИМН, в принципе как самую удобную и самую быструю для расчета тяжелых таблиц. Уникальные совпадения между двумя / тремя критериями отсутствуют, поэтому возвращаемые числа не задваиваются.
Кто давно в теме, подскажите, может есть более быстрые способы получения значений?

Спасибо за ответы, но по факту приведенный пример - это выжимка из 2-х диапазонов под 10.000 строк, диапазоны не подлежат переформатированию (столбец E). вчера пошел другим путем (добавление новых столбцов)

Добрый времени суток! Просьба подсказать.
Есть 2 диапазона данных - Таблица 1 и таблица 2. В таблице 1 присутствуют позиции - алгоритм с уникальным индексом А1, А2. В таблице 2 приводится комплектация / состав каждого алгоритма с указанием типа затрат (медицина, вода). В таблице 1 нужно просуммировать затраты в разрезе каждого типа исходя из наличия того или иного алгоритма (суммируемые ячейки выделены желтым маркером). Сначала хотел использовать функцию СУММЕСЛИМЕ, по фиксированному критерию - "тип" вопросов нет, а как быть с динамическим критерием "алгоритм"?