#excel #excel-formula #excel-2016
#excel #excel-формула #excel-2016
Вопрос:
Этот вопрос поставил меня в серьезное тупик, поэтому я решил поделиться им с вами, ребята, и посмотреть, что у меня получится 🙂
Общая проблема
У меня есть следующие данные в электронной таблице
Как вы можете видеть, у меня есть два идентичных набора заголовков «Кошка, собака, Человек …» (точные имена не имеют значения)
Эти два набора классифицируются либо по столбцу «От», либо по строке «Кому». Основная часть таблицы представляет собой массив чисел от 0 до 1 или пустых ячеек.
По сути, я хочу найти, где заданное Rand() число занимает место в каждой строке. Затем я возвращаю элемент из строки «Кому», соответствующей месту, где будет размещено случайное число.
т.е. при чтении вдоль 1-й строки Cat ранжируется случайное число (тоже от 1 до 0). Если это, скажем, от 0 до 0,3658… Я возвращаю Cat, 0.7193 … и 1 дает мне Van
Поэтому в идеале я возвращаю значение из набора заголовков To, которое находится вертикально над верхней границей, в пределах которой Rand() находится число.
Попытка решения
Для достижения этого рейтинга я использовал Match(value,array,0) функцию (0, поскольку мои числа расположены в порядке возрастания).
Поэтому упрощенная версия моей формулы:
=Index(To_Headings,MATCH(RAND(),From_Row,0))
где To_Headings — массив E3:I3 и From_Row — массив, который я генерирую, используя дополнительную формулу, в результате чего D#:I# (# — номер строки, который связан с заголовками From , поэтому должен быть целым числом от 4 до 8)
Однако, если вы особенно наблюдательны, вы можете увидеть, что именно здесь мое решение терпит неудачу
Как я уже сказал, в идеале я хочу найти верхнюю границу того, где лежит RAND() , поскольку это всегда находится в том же столбце, что и мой желаемый вывод в заголовок. MATCH() с параметром 0 возвращает нижнюю границу того, где RAND() лежит. Обычно это столбец 1 слева от нужного столбца.
например, повторное чтение строки Cat — для случайного числа 0,5 границы, в пределах которых оно лежит, равны 0,3658… и 0,7193… . Верхняя граница находится непосредственно под Dog, моим желаемым результатом. Нижний столбец равен 1 столбцу слева от желаемого 1, поэтому в моей формуле я просто сдвигаюсь вправо при считывании с помощью Index
ОДНАКО пустые ячейки делают это бесполезным. Для случайного числа от 0,719 … до 1 нижняя граница теперь составляет два столбца слева от верхней границы. В других случаях это может быть 3 или 4, фактически любое число. Это потому, что пустые ячейки отодвигают верхнюю границу вправо.
Хорошо, учитывая все это, кто-нибудь может сказать мне, как ранжировать число RAND(), чтобы оно давало мне верхнюю границу? Конечно, я пробовал сопоставлять с -1 в качестве параметра, однако, поскольку для этого требуется порядок убывания, проблема тоже меняется!
Я думаю, я мог бы попробовать посчитать, есть ли пробелы справа от нижней границы, и компенсировать их INDEX на столько, а не только на 1, но я вижу, что это добавит много строк в мой код, и мне действительно нужно упростить его, поскольку он будет выполнятьсяпримерно в 10000 ячейках!!
Комментарии:
1. Рассматривали ли вы возможность использования VBA?
2. Я рассматривал это, но избегал по двум причинам; 1) Я в значительной степени полный новичок в VBA, поэтому не смог бы сделать это сам, не потратив серьезного времени на его изучение, а также не смог бы быстро изменять решения, если бы они были написаны для меня. 2) Я чувствую, что я так близок к этому, и у меня достаточно опыта работы с формулами, чтобы знать, что это возможно, что переход на VBA кажется чем-то вроде отказа добавляет раздражающие проблемы с реализацией (макросы всегда кажутся мне менее автоматическими)
3. Достаточно справедливо, особенно с частью кривой обучения. Но на каком-то этапе это может быть хорошей инвестицией времени. Существует предел тому, чего можно достичь с помощью встроенных функций. Если логика слишком сложна, это будет невозможно, и даже если это возможно, требуемые глубоко вложенные формулы быстро становятся нечитаемыми. С другой стороны, такой гуру формулы, как @ScottCraner, часто удивляет меня, показывая, как много можно сделать без VBA.
Ответ №1:
Вы можете использовать эту формулу массива.
Поскольку ваши данные расположены в порядке возрастания, будет найдена первая ячейка, в которой случайное число меньше или равно числу в наборе данных:
=INDEX($E$3:$I$3,MATCH(TRUE,$J$2<=E4:I4,0))
Будучи формулой массива, она должна быть подтверждена с помощью Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования. Если все сделано правильно, то Excel поместит {} вокруг формулы.
Ответ №2:
Я нашел еще один очень простой ответ, поэкспериментировав с Match функцией.
Все, что вам нужно сделать, это заполнить эти пробелы числом слева. Поскольку Match не ищется первый экземпляр числа, большего, чем случайное число, а вместо этого последний, заполняя пробелы, вся корректировка выполняется за вас.
Итак, пример изображения теперь выглядит следующим образом: 
Так, например, повторяется повторное чтение строки Cat, 0.719 …. Для случайного ввода 0,8 вместо остановки в столбце Dog, как раньше (поскольку 0,8<1, следовательно, 0,719… наибольшее число меньше случайного ввода), формула теперь каждый раз останавливает 1 столбец слева от верхней границы.
Теперь я признаю, что @ScottCraner дал идеальный ответ на мою проблему, как указано, однако формула массива привязана только к одному размеру (без VBA), тогда как формулы ячеек могут автоматически заполнять новый диапазон, поэтому для моего приложения я не могу использовать этот ответ. Поэтому я подумал, что добавлю это решение в качестве подхода к формуле ячейки.
Комментарии:
1. Какова была окончательная формула? И если мой ответ ответил на вопрос так, как он был задан, он должен быть отмечен как правильный. Изменение параметров после получения ответа обычно неприемлемо. Следует задать новый вопрос. Можно сделать диапазон динамическим, но потребуется больше информации. Если бы вы заявили, что диапазон может измениться, я мог бы дать лучший ответ.
2. Да, @Scott, я вижу, что должен был дать тебе такую возможность. Я подведу итог; Моя формула состоит из x столбцов, каждая из которых имеет длину y строк. В то время как ваша формула массива видит, в какую строку чисел поместить случайное число, в зависимости от того, в какой строке находится формула (т.е. в вашем массиве 1-я строка ранжирует случайное число в строке Cat ), моя формула выглядит в строке с тем же названием, что и содержимое ячейки непосредственно слева от нее. Также в моей формуле каждая ячейка имеет свое собственное связанное случайное число, в отличие от 1 числа для всего набора сравнений.
3. Теперь это может быть возможно повторить с помощью формулы массива, я не знаю. Но я чувствую, что вам понадобится больше деталей, поскольку моя проблема немного сложнее, чем предполагает пример. Учитывая, что предоставленный мной ответ означает, что в моих контрольных ячейках мне вообще не нужно изменять мою формулу, а в массиве чисел моя формула на самом деле упрощается , я подумал, что оставлю вопрос на этом. Я чувствовал, что дальнейшая детализация вашего подхода сделает ваш ответ слишком конкретным, чтобы быть полезным для других пользователей, и, решив проблему довольно хорошо, я не думал, что мне нужен более точный ответ.
4. Кстати, я намеревался отметить ваш ответ как правильный, поскольку 1) Он аккуратно решает проблему в том виде, в каком я ее представил, и 2) Я думаю, что это будет более полезно для людей с подобной проблемой, чем мой конкретный ответ. Я только до сих пор этого не делал, так как у меня есть привычка оставлять вопросы на день или два, чтобы накопить дополнительные мысли / альтернативные ответы. Я надеюсь, что все это (относительно) понятно!
5. Какова конкретная формула, которая расскажет мне больше, чем описание. Пожалуйста, опубликуйте точную формулу.