Как мне выполнить повторную сортировку данных при открытии файла?

Question

У меня есть рабочая тетрадь с данными, которую я хотел бы пересортировать (случайным образом, если это имеет значение) каждый раз, когда рабочая тетрадь открывается. (Например, повторная сортировка по столбцу B, сгенерированному вызовом rand() , в рабочей книге, изображенной ниже, каждый раз, когда файл открывается.)

Я не сценарист, но не похоже, что

• Excel 2013, можно ли сортировать данные автоматически?
• Автоматическая сортировка для листа Excel

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

Я предполагаю, что есть что-то вроде сценария из четырех строк, который я мог бы написать, чтобы сделать это, но я понятия не имею, как.

Как я могу заново отсортировать книгу при открытии файла?

_{[Версия Excel 14.0.7180.5002, часть офиса 2010.]}

---

_{В случае, если кому-то интересно, использование таково: я отправляю список своему сообществу списка кандидатов для голосования; Я бы хотел, чтобы список был представлен случайным образом, чтобы не смещать голоса в верхнюю часть стека. (Есть более ста кандидатов и только один голос для голосования.)}

Answer 1

В VBA это можно сделать с помощью одной строки кода в одной подпрограмме:

Private Sub Workbook_Open()

    Sheet1.Range("A:B").Sort Key1:=Sheet1.Range("B:B"), Order1:=xlAscending, Header:=xlYes

End Sub

(Замените каждый экземпляр "sheet1" именем вашей рабочей таблицы (не рабочей книгой))

Объяснение Layman: событие Workbook_Open происходит, когда книга Excel открывается и запускается один раз. Там одна строка кода в основном гласит: используйте метод сортировки в этой области ячеек на основе столбца B. Сортируйте результаты по возрастанию. Игнорировать верхнюю строку, потому что это заголовок.

Обратите внимание, что значения в столбце B не будут отображаться отсортированными. Это связано с тем, что Excel дважды пересчитывает функцию rand() - один раз при открытии рабочей книги и один раз при сортировке данных. Вы могли бы добавить больше кода для контроля, когда произойдут вычисления, но в вашем случае это не имеет значения; конечный результат заключается в том, что имена расположены в случайном порядке.

Шаг за шагом, как заставить это работать:

1. Сохраните вашу книгу как макрокоманду. Файл> Сохранить как> Измените раскрывающийся список "Сохранить как тип" на «Книга с поддержкой макросов Excel (* .xlsm)».

2. Включить вкладку разработчика. Файл> Параметры> Настройка ленты> Отметьте "Разработчик" на основных вкладках. Нажмите ОК

3. Вкладка "Разработчик"> нажмите "Visual Basic". Откроется новое окно. Щелкните правой кнопкой мыши "ThisWorkbook" и щелкните левой кнопкой мыши "View Code".

4. Вставьте подпрограмму в область кода справа. Измените имя объекта рабочего листа с sheet1 на то, каким оно должно быть. Сохраните, закройте, снова откройте, и вы должны увидеть случайную сортировку имен.

Что касается скрытых вещей и внутренней работы VBA, в Интернете достаточно ресурсов. К сожалению, я все еще немного делаю это в своей профессиональной жизни, и в качестве хорошего настольного справочника я выбрал "Освоение VBA для Microsoft Office 2010" от Sybex.

Answer 2

Еще одна альтернатива для рассмотрения (без VBA).

Предполагая, что исходный список находится в $H$2:$H$7 , а формула ячейки в диапазоне $B$2:$B$7 равна RAND() , введите формулу

=INDEX($H$2:$H$7,SUMPRODUCT(--((FLOOR($B$2:$B$7*100000000,1)*0.00000001+(ROW($B$2:$B$7)-ROW($B$2))*0.000000001)<FLOOR(B2*100000000,1)*0.00000001))+1,1)

в A2 и скопируйте / вставьте ячейку в A3:A7 и сохраните файл.

Список в A2:A7 будет пересортирован каждый раз при открытии.

Но обратите внимание, что он также будет пересортироваться каждый раз, когда $B$2:$B$7 пересчитывается.

Вы можете переместить статический список из представления (на другом листе / скрыть / защитить и т.д.).

Комментарий: деталь с функцией FLOOR()

(FLOOR($B$2:$B$7*100000000,1)*0.00000001+(ROW($B$2:$B$7)-ROW($B$2))*0.000000001)

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

редактировать

Лучше ввести формулу в A2 виде формулы массива (Ctrl+Shift+Enter). Удивительно, но это не делает заметной разницы для Excel или LibreOffice Calc , но с Gnumeric только форма массива.

Редактировать 2

Это еще более простая формула массива (Ctrl+Shift+Enter) для помещения в A2:

=INDEX($H$2:$H$7,1+SUM(1*($B$2:$B$7&$H$2:$H$7<B2&H2)))

Answer 3

Если вы хотите избежать использования VBa, самое простое решение - использовать сводную таблицу.

Выберите столбцы, вставьте сводную таблицу на новый лист, расположите столбцы в "табличной форме" и используйте ручной фильтр (по возрастанию или по возрастанию, это неважно) на случайном числе, затем перейдите на вкладку "Параметры". (в сводной таблице), выберите "Параметры" и найдите подвкладку "Данные", нажмите « актуализировать данные при открытии ».

Вы можете скрыть первый лист, содержащий исходные данные, если хотите.

Вуаля!

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

Answer 4

Так что случайным образом делает это немного интереснее. Столбец A в этом примере (1- й столбец) is = rand (), и вы находитесь на рабочем листе Разрывы с 10000 строками

Private Sub Workbook_Open() 

 ActiveWorkbook.Worksheets("Breaks").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("A1:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Breaks").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub