Здравствуйте!
Меня интересуют правила использования умных таблиц в MS Excel.
Вдохновившись этой статьёй я попробовал реализовать свои задачи в Умных таблицах, но так и не разобрался до конца с их синтаксисом.
Поэтому прошу подсказать как правильно преобразовать представленную в примере таблицу в Умную - а главное как требуется изменить/исправить формулы, чтобы сохранить результаты их работы, но задействовать синтаксис Умных таблиц.

Мне более-менее понятно как нужно ссылаться на нужный столбец целиком, а также на отдельную ячейку в текущей строке и выбранном столбце.
Не понимаю как можно обращаться к предыдущей строке (СМЕЩ???), а также к части столбца от заголовка до текущей строки (ИНДЕКС:ИНДЕКС???)

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

Буду признателен за любые рекомендации и идеи.
Заранее благодарен

Здравствуйте!

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

Я решаю задачу по округлению чисел, записанных одно под другим: вначале я округляю сумму, затем все значения кроме первого и, наконец, для первого значения записываю разницу между округлённой суммой и суммой прочих округлённых строк.
Количество чисел может меняться. В оригинальном столбце могут встречаться нечисловые (или пустые) значения - всё это относительно просто обрабатывается функцией ЕСЛИОШИБКА() и добавлением новых строк с последующим копированием формул прямо в них.

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

Нет ли у Вас идей как можно сослаться на вертикальный массив значений произвольной длины для определения его суммы?

С уважением,
Илья

Здравствуйте!
интересует возможность организовать выпадающий список из значений, хранящихся в "виртуальном" (неопубликованном на листе) массиве. Сложность заключается в том, что функция СМЕЩ() отказывается с ним работать вовсе, а ИНДЕКС() обращается либо к отдельному значению, либо к целому столбцу/строке.
В крайнем случае я вынесу этот массив на лист или создам отдельный виртуальный источник данных для выпадающего списка... Но хочется найти  решение поэлегантнее. Нет ли у Вас каких идей?

Приветствую всех!

Некий документ MS Excel последовательно правится разными пользователями (у всех MS Office 2021 pro), в системных настройках которых используются разные разделители целой и дробной части числа. Для корректной обработки данных документа в одной из его ячеек хранится сам разделитель: точка или запятая, вычисляемый как результат функции =ПСТР(1/2;2;1)
Чаще всего результат удовлетворительный (в крайнем случае достаточно пересчитать все данные на листе >> F9), но изредка приходится наступать на конкретную ячейку с разделителем и руками добиваться пересчёта конкретной формулы.

Нет ли у Вас идей как иначе реализовать проверку разделителя целой и дробной части (удобнее/быстрее/надёжнее)?

Заранее благодарен, Илья.

Уважаемые коллеги!

На двух разных компьютерах были установлены разные версии MS Office: 2021Pro и 2016Pro. На обоих компах были проведены необходимые действия по установке внешней надстройки к Excel'ю (REFPROP.XLA), а именно: папка с надстройкой добавлена в перечень "надёжных расположений" в "центре управления безопасностью", там же включены макросы (отключенные по умолчанию), а также прописан путь и включена сам надстройка в Параметрах. После чего на обоих компах стали доступны функции надстройки (появляются пользовательские функции), однако, на 2021м офисе они работают корректно, а на 2016м возвращают ошибку #ЗНАЧ... Опыт личного использования данной надстройки на младших версиях офиса имеется, но, к сожалению, непосредственно запустить надстройку там сейчас нет возможности.

Нет ли у Вас идей что можно сравнить в настройках двух офисов, чтобы найти причину неисправности. Или, возможно, вы сами сталкивались с подобными проблемами и подскажите что можно поправить.

Заранее благодарен!

Всех приветствую!
Столкнулся с ситуацией (видимо из-за недостатка количества исходных данных, а также кривизны рук), в которой у меня не получается найти приемлемого качества полиномиальную зависимость, проходящую через известные точки функции z=F(x,y).
В приведённом примере указаны исходные данные (столбцы A, B и C - прочее можно игнорировать), для которых мне нужно найти зависимость вида
F(x,y) = Amn*x^m*y^n+A(m-1)n*x^(m-1)*y^n+...+A00*x^0*y^0 и определить такие коэффициенты Aij (где i=0...m и j=0...n), чтобы расхождение между значениями z и F(x,y) для известных точек {x,y,z} оказалось минимальным
Известный мне способ определения таких коэффициентов не привёл к сколь-нибудь приемлемому результату. Могли бы Вы предложить какие-нибудь альтернативы ему?

Уважаемые коллеги,
Сталкивался ли кто-нибудь с проблемой зацикленных проверок (и фактического зависания процесса открытия) в документах, для которых MS Excel просит отдельного подтверждения для редактирования (обычно скачанных из интернета / почты)? И если сталкивался, то как решал?
К сожалению, я не могу приложить сам проблемный документ по причинам конфиденциальности.

Тем не менее я могу перечислить симптомы:
- MS Excel рассматривает документ как потенциально опасный и просит подтверждения для его редактирования
- После получения разрешения открываются две версии одноименного документа, что приводит к ошибке в логике работы макросов (чужих, с авторами связи нет )
- Снятие задачи в диспетчере (часто не с первой попытки) позволяет открыть документ или его сохраненную копию без доп. разрешения
- Далее работа с документом проходит в нормальном режиме, включая повторное его открытие из сохранённой версии (без пересылки по почте)
- Проблема с открытием таких документов (созданных из одного шаблона, содержащего данные конкретные макросы) происходит на нескольких машинах, но не на всех
- Отказаться и не использовать этот тип документов нет возможности

Если требуется уточнить что-то ещё, то прошу задавать вопросы без стеснения

Уважаемые коллеги!

В зависимости от типа введенных данных (число, текст-из-списка, любое другое значение) немного меняется их интерпретация.
Условия прописаны внутри приложенного документа.
Я ищу возможности упростить формулы преобразования введенных данных.
Есть ли у вас какие-нибудь идеи?

==============UPDATE====================
В общем задачу можно сформулировать как ввод параметров состояния некоторой среды двумя разными способами:
1. либо задается и температура, и давление: два разных числа в двух ячейках
2-5. либо задается температура ИЛИ давление на одной из двух явно указанных линий насыщения: Жидкость (liq) или Пар (vap) - задается одно число, а вместо другого числа задается одно из текстовых значений (выбор из выпадающего списка)

По результатам обработки введённых данных мне нужно получить:
* в первой ячейке получить текстовую строку: "TP" в первом случае, либо одну из текстовых строк "Tvap", "Tliq", "Pvap" или "Pliq" - в другом;
* во второй ячейке получить число, соответствующее указанной температуре для первого случая, либо указанному параметру (температуре или давлению) - в другом;
* а в третьей ячейке мне нужно получить введенное значение давления - для первого случая, либо пустое значение - в другом
* Если же вместо чисел или заданного текста вводится произвольный текст или что-то ещё, то включается обработка ошибки и выводятся пустые значения

Список допустимых текстовых строк состоит собственно из двух значений "vap" или "liq" и вводится непосредственно в поле допустимых значений выпадающего списка в желтых ячейках. В своем решении я использую виртуальный перечень {"vap":"liq"}

Приветствую коллеги,

Я только начинаю открывать для себя захватывающий мир "умных таблиц" MS Excel - поэтому у меня возникает много вопросов по их использованию вместе с привычными формулами: ВПР, ИНДЕКС, ПОИСКПОЗ, СУММЕСЛИ, etc... Буду признателен, если посоветуете интересный учебник или статью...

К примеру, очевидно удобно стало обращаться к целому столбцу умной таблицы; также я нашел возможность обращения к ячейкам текущей строки умной таблицы.
Мой же вопрос относится к случаю, когда требуется обратиться к целой строке (но не к текущей!) таблицы или к одной конкретной ячейке: существуют ли более элегантные способы сделать это в "умной таблице", нежели чем по старинке напрямую обращаться к конкретным ячейкам?

Я бы предположил, что по аналогии с заголовками столбцов должны существовать заголовки строк "умной таблицы", но к сожалению ничего похожего мне найти не удалось...

Коллеги, приветствую Вас!

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

Заранее благодарен

Дорогие друзья!
Подскажите, пожалуйста, появилась ли в современных версиях MS Excel возможность как-то обойти запрет на использование одномерного массива констант в качестве источника данных для выпадающего списка (проверка данных)?
Я раскопал на форуме аналогичную тему 2012 года, но тогда такой возможности не было найдено.
Всем здоровья и жизнелюбия!

Прошу помощи в решении следующей задачи:
Дана произвольная последовательность положительных чисел [для простоты иллюстрации: натуральных].
Можно ли без предварительной сортировки этой последовательности определить такое число, чтобы сумма всех чисел больше него оказалась бы больше 80% от суммы всех чисел?
Отдельно стоит вопрос что можно сделать, если таких чисел в последовательности несколько, а запрошенная граница проходит через середину их ряда...

Уважаемые коллеги,
Прошу помощи в упрощении кода, записанного макрорекодеромСуть задачи: Нажатием на одну кнопку найти такое значение переменной, чтобы обратить в ноль проверочное значение (добиться заранее заданного результата вычисления целевой функции) последовательно для трех независимых друг от друга функций.

Мне кажется, что текст макроса можно несколько упростить, но сам я за это не возьмусь Прошу поддержки.

В продолжение своей предыдущей темы
Обращение к диапазону внутри именованной таблицы констант

Я пытаюсь найти оптимальный вариант передачи в формулу параметра извне.
В принципе можно обойтись традиционным способом при помощи функции ДВССЫЛ(индекс_начала&":"&индекс_конца;1), но если получится хранить индексы в виде текстовой строки, а затем преобразовать ее вектор, то можно будет отказаться от всей связки функций Ч(ТРАНСП(СТРОКА(ДВССЫЛ(...

Формальная задача: ТРЕБУЕТСЯ получить массив {11;21;31} из второй строки ТАБЛИЦЫ опираясь на ДВА числовых параметра, записанных в ячейках C36:D36, или на ЕДИНЫЙ текстовый параметр, записанный в ячейке D40…

Коллеги!
Прошу помощи при решении следующей задачи:
Пусть организована некоторая таблица числовых и текстовых констант, представленная в виде именованного диапазона "Таблица"
При помощи функции ИНДЕКС() я могу обратиться к конкретному значению таблицы или к целой строке/столбцу внутри нее.
Я ищу возможность при помощи формул вернуть часть указанной строки по аналогии с работой функции СМЕЩ().
Мне бы даже подошел вариант, выдергивающий по одному нужные мне значения из большой таблицы и собирающие их в новый одномерный диапазон...

Пока мне удалось научиться возвращать целую строку таблицы, но замещать нулями ненужные данные. А хочется ненужное полностью обрезать - получать короткую строку.

Есть ли у Вас идеи как можно этого добиться?  

Уважаемые коллеги,

Подскажите, можно ли проще определять количество непустых ячеек в текстовом диапазоне нежели чем как разницу между числом ячеек и числом_пустот?

Уважаемые коллеги,

Передо мной стоит задача поиска обратной функции для заданной функции двух переменных: Требуется найти зависимость концентрации от температуры и давления тогда как известна зависимость температуры от давления и концентрации...
Реальная функция значительно сложнее, чем представленная в примере. Более того реальная функция реализована как UDF в чужом запароленном модуле (надстройке) - к ней у меня есть только пользовательский доступ.

Для этой цели я определяю область значений функции для каждой из переменных и руками подбираю результат до тех пор пока вычисление известной функции не становится близким к текущему значению переменной.
Например: для давления = 3 функция концентрации определена в диапазоне температур от 24 до 36.
Тогда я ручным перебором нахожу такое значение концентрации (10,4%), при котором вычисленное по прямой формуле значение температуры (23,98) становится достаточно близким к текущему значению температуры (24).
Затем я перехожу к следующей температуре (26) при том же давлении (3) и повторяю операцию перебора до нахождения концентрации (29,3%)
Правильный результат подсвечивается зеленым цветом ячейки при помощи УФ.

К сожалению, мне не удалось спрятать все прямые вычисления внутрь УФ (видимо из-за особенностей реализации чужой UDF) - поэтому потребовалась дополнительная таблица ниже.

Подскажите, пожалуйста, как можно автоматизировать (очевидно при помощи VBA) процесс перебора значений (от 0% до 100%) для всех ячеек заданного диапазона, содержащих числовые значения (или пустых)? Критерием окончания перебора в текущей ячейке является смена цвета ячейки на зеленый или просто выполнение условия из УФ.

Как можно понять, в моем оригинальном файле содержится гораздо больше ячеек - мне бы очень хотелось избежать нудной ручной работы...

Заранее благодарен,
Илья

Уважаемые коллеги,

Для выполнения моих вычислений (пример в колонке E) вместо прямой ссылки на ячейки я хочу использовать массив констант вида ={"Петя";1;2;3:"Вася";-10;-20;-30:"Маша";0;1;1}
До тех пор пока он содержит только числовые значения мне удается более-менее успешно получать из него нужные результаты (колонка F).
Однако, в том случае, если я включаю в массив констант текстовые значения моя формула сильно усложняется. И главное, что для меня неприемлемо: появляется необходимость несколько раз указывать в формуле свои переменные (числа из столбца D и имена из строки 11) - смотрите пример в столбце G.
Пока мне приходит в голову только использование функции СМЕЩ() - см. столбец H - к сожалению мне не удалось заставить ее работать с массивом констант.
Либо использование формулы массива (см. столбец I) - чего также хотелось бы избежать...

Могли бы вы предложить альтернативное решение, не требующее многократного обращения к одним и тем же переменным внутри формулы?

Заранее благодарен!

P.S. Если кто-нибудь знает как научить MS Excel получать единицу при возведении нуля в нулевую степень без обработки ошибок - пожалуйста, дайте знать

Коллеги,

Неожиданно столкнулся с различием в собственном понимании приоритетов арифметических действий и понимания MS Excel...

Я всегда предполагал, что возведение в степень имеет приоритет над простыми арифметическими операциями (в том числе над отрицанием) - более того, в справке MS Excel находится тому явное подтверждение.
Поэтому я ожидал, что выражение видане должно менять свой знак: сначала единица возводится в степень, а потом выполняется инверсия знака результата вычислений.

Каково же было мое изумление, когда я обнаружил чередование знаков результатов вычисления как будто бы в степень возводится именно число -1.

Сталкивался ли кто-нибудь с подобными странностями или по-вашему в этом нет ничего странного?

Уважаемые коллеги,

Для цели определения расстояния и направления от одной точки (начала отсчета) до других точек (точки заданы координатами на сфере и радиусом Земли) я использую формулы - они приведены в приложенном файле.
Если к формуле расчета длины вектора (она честно утащена отсюда) у меня нет претензий, то формулу учета направления (север - юг - запад - восток  ИЛИ циферблат часов) я придумывал сам. И мне кажется, что ее можно значительно упростить - нет ли у Вас плодотворных идей на эту тему?

С уважением,
Илья

Уважаемые коллеги,

Прошу помощи в оптимизации решения задачи: о поиске компонентного состава газа с минимальной и максимальной молярными массами при заданных пользователям ограничениях по содержанию каждого из компонентов:
* В таблице указан перечень возможных компонентов и значения их молярных масс (весовые коэффициенты оптимизационной задачи) - см. столбцы A и B - к сожалению, по условию задачи сам перечень компонентов сортировать нельзя...
* пользователь задает ограничения для минимального и максимального значения для каждого из компонентов (в столбцах J и K соответственно) - если обе ячейки в строке остаются незаполненными, то компонент игнорируется; если заполнена хотя бы одна из ячеек, то в расчет принимается указанное значение, либо значение по умолчанию (0% или 100% соответственно для МИН и МАКС)
* требуется подобрать такое сочетание долей каждого из компонентов, чтобы молярная масса смеси принимала минимальное и максимальное значения (речь идет о сумме произведений доли каждого из компонентов и его известной молярной массы)

Я решил задачу с использованием дополнительной таблицы, отсортированной по увеличению молярной массы компонентов (в столбцах N:S); но мне кажется, что даже при помощи формул можно найти более элегантное решение. Прав ли я?

Уважаемые коллеги,

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

Подскажите, пожалуйста, можно ли создать именованную_формулу с относительными ссылками без жесткой привязки к активному листу?
В рамках примера - для использования той же формулы не только на втором, но и на третьем листе.

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

Уважаемые коллеги,

Довольно часто мне приходится использовать вычисления, использующие экстраполяцию значений функции, заданной по нескольким точкам.
Для этой цели я обычно использую встроенные инструменты MS Excel, а именно уравнение линии тренда - такой точности вычислений достаточно для моих расчетов.
Но пока мне приходится вручную переписывать коэффициенты уравнения в ячейки листа, а хотелось бы каким-нибудь способом автоматизировать процесс.

Есть ли у кого-то идеи как это можно сделать?

Заранее благодарен,
Илья

Здравствуйте коллеги!

по причине долговременной болезни форума на сайте msexcel.ru я собрался задать свой вопрос здесь:

для решения одной из своих задач я столкнулся с необходимостью определения разделителя целой и дробной части числа на машине другого пользователя моего документа: у меня используется запятая, а зачатую у других пользователей встречается точка.
для себя я решил проблему при помощи имени, ссылающегося на формулу =ПСТР(1/2;2;1), которое возвращает запятую или точку в зависимости от настроек пользователя... Но может быть есть более правильный путь (без использования VBA)?

На форуме я нашел обсуждение этой проблемы здесь.