У меня есть несколько (10, если быть точным) рабочих листов в документе, отформатированном так, каждый из которых соответствует классу:

| Name | Time 1 | Time 2 | Time 3 | Time 4 |
--------------------------------------------
| Jack | Place1 | Place2 | Place3 | Place4 |
| John | Place4 | Place6 | Place2 | Place9 |
| Dave | Place8 | Place2 | Place5 | Place1 |

которые содержат информацию для людей, где они принадлежат в данный момент времени (например, расписание). В каждом классе около 25 человек и 9 разных мест. Что мне нужно сделать, это создать таблицы 4х9 (для каждого места и времени), в которых будут перечислены все люди, которые в данный момент находятся в данном месте. Так будет и так:

On one sheet:
Place 1 - Time 1
| Name | Signature |
--------------------
| Jack |           |
| Some |           | <--- this guy is from a different class (sheet)


On another sheet:
Place 2 - Time 2
| Name |           |
--------------------
| Jack |           |
| Dave |           |
| Mark |           | <--- again, another class

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

Спасибо за помощь!

1 ответ1

0

Так что я понял, это было сделано по этой формуле. Я заменю переменные с их объяснением. Это долго, но на самом деле это довольно легко. Пожалуйста, игнорируйте чешские версии функций: KDYŽ = IF , ŘÁDEK = ROW и ŘÁDKY = ROWS .

IFERROR(INDEX(Prima;SMALL(KDYŽ(A$1=Prima[Blok 1];ŘÁDEK(Prima[Blok 1])-3);ŘÁDKY(A$3:A3));1);
IFERROR(INDEX(Sekunda;SMALL(KDYŽ(A$1=Sekunda[Blok 1];ŘÁDEK(Sekunda[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1));1);
IFERROR(INDEX(TercieA;SMALL(KDYŽ(A$1=TercieA[Blok 1];ŘÁDEK(TercieA[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1));1);
IFERROR(INDEX(TercieB;SMALL(KDYŽ(A$1=TercieB[Blok 1];ŘÁDEK(TercieB[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1));1);
IFERROR(INDEX(Kvarta;SMALL(KDYŽ(A$1=Kvarta[Blok 1];ŘÁDEK(Kvarta[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1));1);
IFERROR(INDEX(KvintaA;SMALL(KDYŽ(A$1=KvintaA[Blok 1];ŘÁDEK(KvintaA[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1)-COUNTIF(Kvarta[Blok 1];A$1));1);
IFERROR(INDEX(KvintaB;SMALL(KDYŽ(A$1=KvintaB[Blok 1];ŘÁDEK(KvintaB[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1)-COUNTIF(Kvarta[Blok 1];A$1)-COUNTIF(KvintaA[Blok 1];A$1));1);
IFERROR(INDEX(Sexta;SMALL(KDYŽ(A$1=Sexta[Blok 1];ŘÁDEK(Sexta[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1)-COUNTIF(Kvarta[Blok 1];A$1)-COUNTIF(KvintaA[Blok 1];A$1)-COUNTIF(KvintaB[Blok 1];A$1));1);
IFERROR(INDEX(Septima;SMALL(KDYŽ(A$1=Septima[Blok 1];ŘÁDEK(Septima[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1)-COUNTIF(Kvarta[Blok 1];A$1)-COUNTIF(KvintaA[Blok 1];A$1)-COUNTIF(KvintaB[Blok 1];A$1)-COUNTIF(Sexta[Blok 1];A$1));1);
IFERROR(INDEX(Oktáva;SMALL(KDYŽ(A$1=Oktáva[Blok 1];ŘÁDEK(Oktáva[Blok 1])-3);ŘÁDKY(A$3:A3)-COUNTIF(Prima[Blok 1];A$1)-COUNTIF(Sekunda[Blok 1];A$1)-COUNTIF(TercieA[Blok 1];A$1)-COUNTIF(TercieB[Blok 1];A$1)-COUNTIF(Kvarta[Blok 1];A$1)-COUNTIF(KvintaA[Blok 1];A$1)-COUNTIF(KvintaB[Blok 1];A$1)-COUNTIF(Sexta[Blok 1];A$1)-COUNTIF(Septima[Blok 1];A$1));1);""

))))))))))

Основная часть повторяется там несколько раз, как альтернативный текст для функции IFERROR() :

IFERROR(
    INDEX(
        <table range>;
        SMALL(
            IF(
                <value to find>=<range in table to look in>;
                ROW(<range in table to look in>)-<number of rows above the first row of the table>
            );
            ROWS(<first-cell-in-results-column-to-this-cell range>) <note here>
        );
        <column number (starting from 1) with the return value>
    );
    <alternative for IFERROR>
)

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

Это хорошо, но это не единственное, что нам нужно сделать. Эта формула работает путем внутреннего создания массива возвращаемых значений внутри каждой ячейки списка и последующего выбора N-го наименьшего индекса (функция INDEX ) из него, где N устанавливается функцией ROWS внутри INDEX . Так что, если мы не сделаем следующее, то при переходе к следующей таблице попытаемся снова выбрать N-й индекс, однако в предыдущих таблицах уже есть некоторые значения. Нам нужно вычесть количество элементов, уже присутствующих в таблице, используя функцию COUNTIF . Поэтому после того, как мы добавим вторую итерацию нашей формулы, все будет выглядеть так:

IFERROR(
    INDEX(
        <table range>;
        SMALL(
            IF(
                <value to find>=<range in table to look in>;
                ROW(<range in table to look in>)-<number of rows above the first row of the table>
            );
            ROWS(<first-cell-in-results-column-to-this-cell range>)
        );
        <column number (starting from 1) with the return value>
    );
    IFERROR(
        INDEX(
            <table 2 range>;
            SMALL(
                IF(
                    <value to find>=<range in table 2 to look in>;
                    ROW(<range in table 2 to look in>)-<number of rows above the first row of the table 2>
                );
                ROWS(<first-cell-in-results-column-to-this-cell range>)
                -COUNTIF(<range in table 2 to look in>; <value to find>)
            );
            <column number (starting from 1) with the return value>
        );
        <another iteration or something else>
    )
)

И с каждой новой итерацией внутри следующего IFERROR мы должны добавить еще одну функцию -COUNTIF и сохранить предыдущие. Затем, когда все настроено, вы можете просто перетащить свою формулу на доску и создать списки, сколько захотите!

Я действительно надеюсь, что это кому-то поможет, если вы можете оставить комментарий :) Я потратил некоторое время на эту формулу и выяснил, как все работает, поэтому, если я сэкономил вам время, я был бы рад помочь! :)

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .