Марлен — учитель, и у нее есть ученики, которые любят поиск слов. Она считает, что на их изготовление уходит много времени, но студенты, кажется, намного лучше запоминают материал курса, когда она их использует. Марлен поинтересовалась, есть ли способ автозаполнения диапазона ячеек буквами алфавита от A до Z. Таким образом, она может использовать эту функцию для заполнения квадратов поиска слова буквами, прежде чем она заменит некоторые из этих букв на фактические слова для поиска.

Инструмент «Автозаполнение» в Excel имеет несколько стандартных последовательностей, которые заполняются автоматически, например даты и числовые последовательности. Однако очень мощная часть автозаполнения заключается в том, что вы можете создавать настраиваемые списки, которые инструмент использует так же легко, как и встроенные последовательности. Чтобы создать собственный список вручную, вы можете выполнить следующие действия:

  1. Выберите «Параметры» в меню «Инструменты». Excel отображает диалоговое окно «Параметры».

  2. Убедитесь, что выбрана вкладка Custom Lists. (См. Рис. 1.)

  3. В поле «Список записей» введите каждую букву алфавита, по одной букве в строке. (Нажимайте Enter после каждой вводимой буквы.)

  4. Щелкните Добавить.

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

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

  1. Выберите «Параметры» в меню «Инструменты». Excel отображает диалоговое окно «Параметры».

  2. Убедитесь, что выбрана вкладка Custom Lists. Выбранный диапазон ячеек должен отображаться в поле «Импортировать список из ячеек».

  3. Щелкните Импорт.

Теперь вы можете закрыть диалоговое окно и использовать настраиваемый список по своему усмотрению.

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

Один из простых способов получить случайные буквы — использовать следующую формулу:

=CHAR(RANDBETWEEN(65,90))

Эта формула работает, потому что функция RANDBETWEEN возвращает случайное числовое значение между двумя предоставленными граничными значениями. В этом случае он вернет значение от 65 до 90, которые являются значениями ASCII букв A и Z соответственно. Затем функция CHAR используется для преобразования этого случайного числового значения в фактическую букву.

Функция RANDBETWEEN является частью пакета Analysis ToolPak, надстройки, которую многие люди установили в Excel. (Выберите «Инструменты | Надстройки», чтобы узнать, установлена ​​ли она у вас.) Если вы предпочитаете не включать надстройку, вы можете использовать более простую формулу, например следующую:

=CHAR((65+(90-65)*RAND()))

Функция CHAR должна показаться вам знакомой; единственное отличие заключается в использовании функции RAND для генерации случайного значения вместо RANDBETWEEN.

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

Существует множество способов составить такой макрос; следующее — особенно гибкое. Он будет работать либо с предварительно выбранным диапазоном (диапазон, выбранный при запуске макроса), либо вы можете выбрать диапазон после запуска макроса.

Sub AlphaFill()

Dim Cell, CellChars     Dim Default, Prompt, Title     Dim rangeSelected As Range     Dim UpperCase As Boolean

Title = "AlphaFill Cell Selection"

Default = Selection.Address     Prompt = vbCrLf _       & "Use mouse in conjunction with " _       & "SHIFT and CTRL keys to" & vbCrLf _       & "click and drag or type in name(s) " _       & "of cell(s) to AlphaFill" & vbCrLf & vbCrLf _       & "Currently selected cell(s): "

& Selection.Address

On Error Resume Next     Set rangeSelected = InputBox(Prompt, Title, _       Default, Type:=8)

If rangeSelected Is Nothing Then Exit Sub

UpperCase = True     Randomize     For Each Cell In rangeSelected         CellChars = Chr(64 + Int((Rnd * 26) + 1))

If Not UpperCase Then CellChars = LCase(CellChars)

Cell.Value = CellChars     Next End Sub

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

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3109) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-AutoFilling_with_the_Alphabet [Автозаполнение с помощью алфавита].