Бесплатный фрагмент - Excel и судоку

Глава 1. Вместо введения

Данная книга пригодится не только тем, кто любит отгадывать судоку, но также и всем тем, кто хочет улучшить уровень своего владения электронными таблицами Эксель (Excel).

Основные темы, которые рассмотрены в данной книге:

· программирование в Эксель, работа с макросами;

· форматирование и условное форматирование ячеек Эксель;

· имена ячеек и групп (массивов, областей) ячеек;

· создание имен для формул в Эксель;

· создание имен для разрозненных диапазонов в Эксель;

· сокращение вложений в функции ЕСЛИ с сохранением всех задач, стоящих перед этой функцией;

· создание файла с макросами в Эксель, помогающего найти кандидаты для каждой ячейки судоку;

· фильтрация (отсеивание) ненужных кандидатов судоку;

· выделение самых главных (особых, уникальных) кандидатов судоку;

· полная или частичная автоматизация решения судоку от начала и до конца;

· работа в макросах (в редакторе VBA) с ячейками, массивами ячеек, а также с формулами, у которых уже есть свои имена в текущей книге Эксель;

· оптимизация работы с очень длинными (очень большими) формулами в Эксель;

· создание таких списков для ячеек Эксель, чтобы из них можно было выбрать нужный элемент для ввода в ячейку Эксель (создание списков для ячеек Эксель).

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

В данной книге рассказано о том, что такое кандидаты в судоку, как кандидаты помогают в решении судоку.

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

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

Многие программисты, улучшая свои навыки владения Эксель, получат удовольствие не сколько от разгадывания судоку, сколько от самого процесса программирования, то есть составления программ с помощью Эксель, которые помогут в отгадывании разных видов судоку.

Когда автор начал работать над этой книгой, то вначале хотел разобрать несколько видов судоку. Но когда выяснилось, что объем информации только для основного (классического) типа судоку занимает несколько сотен страниц, пришлось на этом остановиться, ограничиться в этой книге только основным видом судоку. Все судоку, рассмотренные в этой книге, объединяет одно: размер 9 на 9. Большинство судоку, которые существуют на сегодняшний день, составляются именно размером 9 на 9 клеток. Другие типы судоку будут рассмотрены в следующих книгах, которые выйдут позже. Таким образом, данная книга — только первая книга в целой серии книг по данной теме.

С точки зрения математики любое заполненное судоку — это латинский квадрат. Если говорить про судоку размером 9 на 9 — то судоку будет представлять из себя латинский квадрат размером 9 на 9. Это значит, что в каждой строке и в каждом столбце любая из цифр от 1 до 9 включительно будет содержаться только по одному разу. Кроме того, почти во всех разновидностях судоку (за редкими оригинальными исключениями) имеются маленькие квадратики размером 3 на 3, причем в каждом из этих квадратиков каждая цифра от 1 до 9 включительно встречается тоже только однажды. Эти маленькие квадратики размером 3 на 3 мы будем называть блоками. В каждом судоку 9 на 9 содержится ровно 9 блоков размером 3 на 3. Во многих видах судоку присутствуют и добавочные правила, например, диагональные судоку — это те судоку, на каждой из диагоналей которых нет повторяющихся цифр. Не только диагональные, но и многие другие виды судоку будут рассмотрены… но уже не в данной книге, а в других книгах этого же автора, которые выйдут чуть позже.

Макросы и отрывки из макросов расположены внутри следующих стрелочек:

↓ ↓ ↓ ↓

(Текст макроса или его часть)

↑ ↑ ↑ ↑

Все судоку (как неразгаданные, так и разгаданные), что использованы в данной книге, либо получены с помощью компьютера (с помощью компьютерных программ), либо составлены лично автором этой книги. Любые совпадения с теми судоку или их частями, что были когда-то опубликованы в печатном и/или электронном виде и, возможно, являются объектом чьего-то авторского права, являются случайными. Единственное исключение из этого правила — это то судоку, которое названо «самым сложным судоку в мире», оно было не раз опубликовано в открытом доступе, его автором считается Арто Инкала, математик из Финляндии. По шкале, которой обычно измеряют трудность судоку, этот судоку обычно оценивают в 11 (одиннадцать) баллов.

Основная цель данной книги — помочь не только всем любителям судоку, но и всем тем, кто хочет улучшить свои навыки владения Эксель. В книге рассказано, как можно с помощью Excel получить все кандидаты для каждой ячейки судоку, как отсечь большинство «лишних» кандидатов, как быстро найти тот кандидат — назовем его «главный» кандидат — который быстро можно перенести из таблицы с кандидатами в основное судоку. Это уже, в принципе, не совсем кандидат, потому что данный кандидат будет либо единственным для какой-то конкретной клетки (ячейки) судоку, либо все кандидаты рядом с ним будут автоматически считаться «лишними» для той ячейки судоку, в которой находится этот «главный» кандидат. «Главный» кандидат — это тот, который однозначно и немедленно должен быть перемещен из кандидатов в основное судоку. По сути, в этом и заключается работа по заполнению (или разгадыванию) судоку, а именно: нужно найти «главный» кандидат и переместить (записать) его в основное судоку. Мы в данной книге иногда этот кандидат будем называть также «уникальным», «основным», или «однозначным». Это по сути одно и то же. И означает это понятие тот кандидат, который надо переместить в основное судоку.

В данной книге будет встречаться термин «область». Мы под областью будем понимать строку, столбец или блок (маленький квадратик 3 х 3) судоку. Если какое-то утверждение будет справедливо и для каждой строки судоку, и для каждого столбца судоку, и для каждого блока (маленького квадрата 3 х 3 судоку), то мы просто употребим термин «область». Так, например, фраза «в любой области судоку каждая цифра от 1 до 9 включительно может встречаться только 1 раз» будет означать, что и в каждой строке, и в каждом столбце, и в каждом блоке судоку каждая цифра от 1 до 9 может находиться только 1 раз.

В Эксель тоже имеется понятие «область» — это интервал смежных ячеек, или же просто отдельная ячейка (иногда «область» может состоять из одной ячейки). В «область» Эксель может входить как массив, в котором есть несколько строк и несколько столбцов Эксель, так и тот массив, который содержит несколько смежных ячеек одной строки или одного столбца. В принципе, каждая область судоку является также и областью Эксель. Но не каждая область Эксель будет являться областью судоку. Область Эксель может объединять как несколько разных областей судоку, а может и вообще находиться вне квадрата, содержащего все ячейки судоку. А еще область Эксель может включать в себя несколько частей от разных областей судоку.

Существуют следующие 2 причины, по которым кандидат судоку может считаться «главным»:

1. Если в какой-то области судоку какой-то конкретный кандидат (например, единица или двойка) встречается только один раз. Рассмотрим эту ситуацию более подробно:

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

Это судоку, которое было изображено на рисунке 1.1, будет часто встречаться в этой книге. Поэтому давайте договоримся так: присвоим этому судоку номер, и это судоку будет называться в этой книге «судоку №1».

В данном судоку число 39 в верхнем левом углу рисунка означает, что в судоку на данном этапе заполнено ровно 39 цифр (в заполненном до конца судоку будет 81 цифра).

Правда, получается так, что буквы Е и Ё очень похожи, да и буква З (зэ) очень похожа на цифру 3 (три).

Чтобы избежать всяких неудобств, неразберихи или путаницы, связанных с похожими буквами и цифрами, изменим буквенную нумерацию строк судоку (рисунок 1.2):

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

Вот какими будут кандидаты для первой (верхней) строки этого судоку (рисунок 1.3):

Разобьем эту строку на три части, чтобы было не так мелко и было удобнее смотреть (рисунок 1.4):

Итак, рисунок 1.4 — это три фрагмента одного листа Эксель, но на самом листе Эксель они расположены так же, как это показано на рисунке 1.3, то есть занимают 4 строки Эксель, если считать и ту строку, которая содержит заголовки столбцов судоку (речь идет о той строке, которая содержит числа 91, 1, 1, 1, 2, … 8, 9, 9, 9).

Здесь есть несколько таких кандидатов, которые появляются только один раз в первой строке (в других строках тоже есть аналогичные кандидаты, но мы здесь более подробно рассмотрим первую, или верхнюю, строку судоку). Тут есть уникальные кандидаты, то есть те, которые встречаются в строке только однажды. Это цифры 5, 7 и 3. Цифра 5 встречается в ячейке А2, это и будет главным кандидатом для этой ячейки. Цифра 7 — это главный кандидат для ячейки А7, а цифра 3 — это главный кандидат для ячейки А9.

В этой строке судоку 5 цифр уже заполнено, 4 ячейки пустые. Это значит, что для четырех пустых ячеек нужно составить список всех возможных кандидатов.

Здесь в верхнем левом углу число 91 означает, что всего в данном судоку имеется 91 кандидат. На рисунке 1.3 мы видим не все кандидаты судоку, а только их часть, то есть те кандидаты, которые имеют отношение к первой (верхней) строке судоку.

Тут круглые символы (здесь мы их в виде текста показать не можем, просто скажем, что на рисунке 1.4 этих символов достаточно много, каждый из этих символов похож на вид зонтика сверху) означают, что для данной ячейки судоку не нужны кандидаты, потому что ячейка судоку уже заполнена. Двойные знаки минус означают, что ячейка судоку пустая, кандидаты для этой ячейки должны быть, но какая-то конкретная цифра не может быть кандидатом для данной ячейки. Конечно же, и круглые символы, и двойные минусы можно заменить любыми другими символами, которые больше всего нравятся (желательно не цифрами, не буквами и не числами, а просто красивыми символами), но об этом более подробно будет рассказано в другой главе этой книги.

Рассматривая судоку №1, мы можем сказать, что не нужны кандидаты для ячеек A1, A4, A5, A6 и A8 судоку, поскольку эти ячейки судоку уже заполнены, они не пустые.

Есть и такие ячейки судоку, у которых имеется всего один кандидат. Так, например, для ячейки А2 судоку кандидатом может быть только цифра 5, поэтому все клеточки вокруг этой пятерки заполнены двойными минусами. Для ячейки А2 судоку цифра 5 — это единственный кандидат. Обычно такие кандидаты называются «голыми одиночками». Но тему «голых одиночек» мы рассмотрим чуть позже, пока мы рассматриваем только те ситуации, при которых кандидат встречается только один раз в своей области. Правда иногда бывает и так, что этот самый кандидат, который уникален для своей области, будет единственным кандидатом для своей ячейки. Другими словами, не все уникальные кандидаты в своей области могут быть «голыми одиночками», это только частный случай. Итак, иногда «голые одиночки» могут быть уникальными для какой-то области судоку. А иногда уникальные кандидаты для какой-то области могут быть и «голыми одиночками». Но все это — только частные случаи.

Если бы для какой-то ячейки судоку были бы возможны все 9 кандидатов (вряд ли, конечно, такая ситуация может возникнуть в реальности), то возможные кандидаты для этой ячейки располагались бы так, как это показано на рисунке 1.5.

Рисунки 1.3, 1.4 и 1.5 — это фрагменты таблицы с кандидатами, фрагменты квадрата с кандидатами для ячеек судоку. Если, например, для какой-то ячейки судоку цифра 1 будет являться кандидатом, тогда эта единица будет располагаться исключительно в левом верхнем углу того маленького квадратика, который предназначен для всех кандидатов своей ячейки. Итак, любую цифру в таблице кандидатов можно считать кандидатом. Но если в таблице с кандидатами есть какая-то конкретная цифра, причем она встречается только однажды среди кандидатов одной строки или любой другой области судоку, тогда именно эта цифра и является не просто кандидатом для нужной нам ячейки судоку, а именно главным кандидатом. Здесь видно (рисунок 1.4), что среди всех кандидатов первой (верхней) строки судоку только один раз встречаются цифры: 5, 7 и 3. Это значит, что для тех ячеек, к которым относятся названные цифры, эти кандидаты являются «главными». Для ячейки А2 судоку «главным» кандидатом будет пятерка, для ячейки А7 судоку «главным» кандидатом будет семерка, для ячейки А9 судоку «главным» кандидатом будет тройка. Как видно, в одной строке судоку одновременно может присутствовать несколько «главных» кандидатов, но так бывает не всегда.

1.2. Если в каком-то столбце судоку нужный кандидат встречается только один раз, то этот кандидат тоже будет «главным». В том самом судоку, которое мы недавно рассматривали, можно увидеть несколько «главных» кандидатов в столбцах. Вот, например, все кандидаты первого (левого) столбца (рисунок 1.6):

Этот рисунок тоже можно разбить на 3 части для удобства, чтобы данные были не такими мелкими (получим рисунок 1.7):

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

Здесь всего однажды (только один раз во всем столбце судоку) встречаются цифры 4, 5 и 8. Эти цифры и будут главными кандидатами. Для ячейки Ж1 главным кандидатом будет четверка, для ячейки Е1 главным кандидатом будет пятерка, а для ячейки Д1 главным кандидатом будет восьмерка.

1.3. Аналогичная ситуация может возникнуть и в блоках, или маленьких квадратах (3 на 3) судоку. Если в каком-то одном блоке какой-то кандидат встречается только один раз, то этот кандидат тут же становится «главным» и должен перейти в основное судоку.

В рассматриваемом нами судоку такие кандидаты тоже есть. Покажем, например, девятый блок с кандидатами (девятый блок — это самый правый нижний блок). Кандидаты для этого блока расположены на рисунке 1.8:

Итак, здесь есть три «главных» кандидата. Для ячейки Ж7 «главным» кандидатом будет двойка, для ячейки И7 «главным» кандидатом будет восьмерка, для ячейки И8 «главным» кандидатом будет единица.

Все эти варианты, перечисленные в пункте 1, можно назвать так: «скрытые одиночки». Именно так эти ситуации называются в судоку. Но, честно говоря, на рисунке 1.8 цифры 1 и 8 также одновременно являются и «голыми одиночками», поскольку эти кандидаты — единственные для своих ячеек судоку. А вот цифра 2 — это типичная «скрытая одиночка»: она единственная двойка среди всех кандидатов своего блока, но при этом она не является «голой одиночкой», поскольку у ячейки Ж7 судоку есть и другой кандидат тоже.

2. Вторая основная причина, по которой кандидат судоку автоматически становится «главным» кандидатом, достаточно проста. Если у какой-то ячейки судоку есть всего один кандидат, он тут же автоматически становится «главным». Как уже говорилось ранее, именно такой кандидат называют «голая одиночка».

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

Например, в строке 1 «голые одиночки» — это ячейки А2 и А3 судоку; в первом столбце — это ячейки Г1 и И1, в девятом блоке — это ячейки И7, И8 и К7 судоку.

Во многих издательствах используется автозамена текста, поэтому некоторые символы автоматически изменяются. Например, одиночный апостроф часто превращается в двойную кавычку, а сами двойные кавычки часто меняют свой вид, свою форму (например, простые «прямые» кавычки заменяются «парными»). Чтобы избежать автозамену, мы в данной книге сделаем замену нескольких символов.

Так, например, символ апострофа мы заменили на символ минуты (′), символ кавычек мы заменили на символ секунды (″), поскольку эти символы останутся прежними даже после автозамены, которую предлагают книжные издательства.

При вставке этих символов в редактор VBA даже не обязательно делать обратную замену, превращая знаки минут в апострофы, а знаки секунд в кавычки. И знак минуты, и знак апострофа, в редакторе VBA видятся и воспринимаются одинаково, как знак апострофа. Правда, если эти символы вставлять не в редактор VBA, а в саму ячейку Эксель или в строку формул, тогда тут уже придется делать обратную замену, надо будет заменить символы секунд простыми «прямыми» кавычками, а символы минут простым одиночным апострофом. В любом случае, мы почти ко всем текстовым вариантам формул и макросов прилагаем и их скриншоты, чтобы не возникало вопросов насчет тех или иных символов. А иногда джае показываем только одни скриншоты, без текстовых вариантов.

Приведем пример. Покажем текст одного из макросов, в котором присутствуют знаки минут и секунд:

↓ ↓ ↓ ↓

Sub Удалить_форматы_с_кандидатов ()

Range (″N3:AN29″).Select

′это мы выделили квадрат с кандидатами

Selection.FormatConditions.Delete

End Sub

↑ ↑ ↑ ↑

Скопируем этот текст, затем вставим его в редактор VBA. Мы получим вот что (рисунок 1.9):

А теперь информация специально для тех моих читателей, которые, приобретя электронную (не печатную) версию моей книги, будут копировать макросы из книги в редактор VBA. Как видно из рисунка 1.9, в редакторе VBA могут образовываться лишние пустые строки. Для многих макросов эти пустые строки не помешают их нормальной работе. Но иногда может случиться и так, что некоторые строки макроса внутри редактора VBA окрасятся в красный цвет, станут неправильными, ошибочными. Это может происходить в тех случаях, если между двумя частями одной программной строки макроса, разделенной на две экранные строки, окажется пустая экранная строка. Тогда после первой экранной строки макроса будет знак подчеркивания (он на языке макросов и означает разделение одной программной строки на две части), а сразу за этой строкой окажется пустая строка, а не продолжение макроса. Данную проблему можно решить достаточно просто: нужно удалить ту пустую строку, которая окажется между двумя частями одной и той же программной строки макроса. В любом случае, при копировании текста макросов можно будет свериться с книгой, а если мешают все пустые строки (независимо от того, влияют ли они на корректную работу макроса), их лучше удалить, заодно проверить правильность остальных строк макроса.

Как уже было сказано ранее, чтобы не было проблем при преобразовании копируемого текста, мы просто рядом с текстом макроса приведем его скриншот, чтобы было четко видно, каким на самом деле должен быть макрос после решения всех проблем, связанных с автозаменой издательства. Иногда скриншот будет появляться и после текста формулы в Эксель, а не только после макроса или его фрагмента. Таким образом, только что приведенный макрос должен на самом деле выглядеть так (рисунок 1.10):

Тут четко видно, что такое «прямые кавычки», что такое знак апострофа.

Здесь хотелось бы написать только несколько предложений о том, что такое программная строка. Макрос — это компьютерная программа, то есть совокупность компьютерных (программных) строк. Каждый макрос должен иметь какую-то основную цель. Программист должен иметь представление о том конечном результате, который необходимо получить после выполнения этого макроса. Обычно этот конечный результат разбивается на небольшие этапы. Эти этапы, в свою очередь, можно разделить на еще более маленькие пункты. Самый маленький пункт при этом и будет заключаться в одной программной строке. Это будет минимальная часть макроса. Обычно одна строка макроса — это такое минимальное действие, которое может выполнить компьютер. Например: объявить массив; вычислить значение какой-то функции; добавить внешнюю границу какой-то ячейке или диапазону ячеек. То есть тут речь идет о самых простых компьютерных операциях, которые можно описать одним небольшим предложением. Но часто бывает так, что одна программная строка занимает несколько экранных строк (например, это бывает в тех случаях, когда одна формула состоит из многих переменных). В этом случае придется разбивать одну программную строку на несколько экранных строк (или на несколько печатных строк, если необходимо показать текст этой программной строки на бумажном носителе). Итак, нужно отличать строку программы и экранную (печатную) строку.

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

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

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

↓ ↓ ↓ ↓

Sub Имя_строкам_судоку ()

For i = 3 To 11

Range (Cells (i, 3), Cells (i, 11)).Select

ActiveWorkbook.Names.Add Name:= ″стр_″ & i — 2, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C3:R″ & i & ″C11″

ActiveWorkbook.Names (″стр_″ & i — 2).Comment = ″строка ″ & i — 2 & ″ судоку″

Next i

End Sub

↑ ↑ ↑ ↑

Если мы этот макрос скопируем и вставим в редактор VBA, то получим следующую информацию (рисунок 1.11):

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

Здесь три строчки выделены красным цветом (я это поясняю из-за того, что мне не известно, держит ли читатель в руках черно-белый или цветной экземпляр моей книги, при этом на черно-белой версии книги не видно больших различий между красным и черным цветами). Речь идет о строках с номерами 4, 5 и 6. На самом деле «ошибка» в строке 4 (в той, что содержит символ нижнего подчеркивания) следующая: короткий минус (математический знак минуса) превратился после автоматической замены в длинное тире; эта же «ошибка» касается и строки под номером 6. А пятая строка стала «ошибочной» из-за лишней пустой строки над ней, пустые строки в листинге макроса тоже считаются строками программы. После символа подчеркивания идет продолжение той же программной строки. И это продолжение не должно быть пустой строкой (про разбивку программных строк символом подчеркивания мы поговорим чуть позже).

Вот каким должен быть этот макрос после устранения всех этих «ошибок» (рисунок 1.12):

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

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

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

В процессе написания книги автор столкнулся с еще одной проблемой, которая тоже имеет отношение к автозамене текста.

Из-за автозамены могут возникнуть искажения не только в тексте макроса, но и в тексте тех формул, что обычно находятся не в макросах, а именно в ячейках Эксель. Здесь обычно автозамена добавляет лишние пробелы в самих формулах, причем в тех местах формул, где обычно не нужны пробелы. Поэтому заранее предупреждаю о том, что обычно в формулах Эксель пробелы не нужны вообще, даже для отделения знака амперсанд (&). Правда, если в формулах Эксель добавить пробелы по обе стороны от каждого знака амперсанд (&), то это большой ошибкой не будет считаться, формула от этого не будет являться ошибочной. А вот пробелы перед многими другими символами в формулах Эксель часто приводят к ошибкам, например, может возникнуть сообщение в стиле «Это не формула».

Но если этот знак амперсанд (&) встречается не в формулах Эксель, а в макросах на языке VBA, тогда его необходимо и слева, и справа отделять пробелами. Как было сказано ранее, некоторые формулы Эксель в данной книге дублируются рисунками, в которых изображен скриншот этих самых формул.

Макросы пишутся на языке VBA (сокращение от Visual Basic for Applications). Applications — это приложения. Под приложениями понимаются программы, входящие в MS Office. Эксель — это только одна из этих самых программ, одно из приложений. Оператор, или выражение (минимальный исполняющийся код VBA), расположенный в одной строке макроса, можно разбить на несколько экранных строк. Если в конце какой-то строки макроса содержится пробел, а за пробелом — символ нижнего подчеркивания, то это значит, что строка макроса разбита, в следующей строке макроса будет продолжение того же самого кода VBA. Так переносятся на другую строчку разные части кода одного выражения. Имеется в виду перенос на другую строчку в самом редакторе VBA. На рисунке 1.12 мы уже приводили макрос, одна из строк которого разбита с помощью символа подчеркивания, но здесь приведем еще один пример:

↓ ↓ ↓ ↓

ActiveWorkbook.Names.Add Name:=″бло_″ & k, RefersToR1C1:= _
″=Основное_судоку! R″ & i & ″C″ & j & ″:R″ & i +2 & ″C″ & j +2

↑ ↑ ↑ ↑

Как было обещано ранее, после текста с фрагментом макроса приведем скриншот (рисунок 1.13):

Здесь был приведен пример всего лишь одной программной строки макроса. Она занимает две экранные строчки, две строчки на странице экрана, но это одна строка программы, потому что главный результат выполнения этой строчки прописан одним оператором на VBA (здесь главная фраза в этой длинной строчке будет Add Name, то есть: добавить имя), а смысл этой строки макроса заключается в присвоении конкретного имени одной ячейке Эксель. Именно присвоение имени — это и есть одно действие, которое выполняется этой строкой макроса. Просто так получилось, что текст этой строки макроса оказался слишком длинным, поэтому и возникла необходимость в разбивке этого текста на экранные строки. Эта строка макроса разбита на две части не только в этой книге, но и в редакторе VBA. В редакторе VBA символ нижнего подчеркивания (_) всегда означает то, что продолжение этой же программной строки будет на следующей экранной строчке. При этом сам символ нижнего подчеркивания должен быть отделен от предыдущего текста пробелом.

Но иногда бывает так, что длинные строки нельзя разделить на несколько строк в редакторе Visual Basic (точнее говоря, разделить можно, но это разделение возможно не в любом месте строки макроса). Обычно при попытке разбить программную строку в «неправильном» месте Эксель выдает сообщение об ошибке (рисунок 1.14).

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

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

Просто нужно иметь в виду, что несколько книжных строк — это всего лишь одна «экранная» строка макроса, которая просто не уместилась на одной строчке страницы книги. Приведем пример:

↓ ↓ ↓ ↓

Selection.FormatConditions(Selection.FormatConditions.Count). SetFirstPriority

↑ ↑ ↑ ↑

Приведем скриншот (рисунок 1.16):

Тут приведен не один макрос, а только одна его часть, одна программная строка. Если в тексте макроса книжная строка начинается не с «красной строки», то есть не имеет отступа слева, то это значит, что данная книжная строчка является продолжением предыдущей строки макроса, в редакторе VBA обе эти строки будут занимать одну строчку. В приведенном примере все именно так и происходит. Тут приведена всего лишь одна «программная» строка макроса, ее надо в редакторе VBA разместить на одной строке. Эта строка макроса разместилась на двух книжных строчках.

И еще один момент. В Эксель, как и во многих других программных продуктах, есть многоуровневое меню. Давайте сразу определимся, как будут называться основные части этого меню (рисунок 1.17):

Конечно, можно применять также и просто слово «меню».

Хотелось бы добавить еще несколько слов.

Если вдруг встретится точка в конце имени ячейки или диапазона ячеек, то это значит, что данная точка — просто знак препинания, но не часть имени ячейки или диапазона ячеек.

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

Правда, иногда точки могут встречаться в середине имен ячеек или диапазонов Эксель и тогда эти точки уже будут являться частью имени, но об этом будет сообщено отдельно.

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

1. Убедиться, что текущий язык ввода — это русский язык.

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

3. Вставить нужный фрагмент в другое место.

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

В процессе работы над книгой я обратил внимание на еще одно проявление автозамены. Тут речь идет о пробелах. Автозамена часто добавляет ненужные пробелы, но и убирает нужные. Например, если после знака + (плюс) тут же следует пробел, а за пробелом цифра в тексте макроса, то автозамена автоматически удаляет пробел между знаком плюс и этой цифрой. Конечно, эту проблему можно решить. Например, если удалить пустую строку, расположенную сразу после строки с подобными «ошибками», то редактор VBA автоматически расставит все пробелы по правильным местам. Но на всякий случай скажу: в редакторе VBA при написании макросов знаки равно (=), плюс (+), минус (-), умножение (*), деление (/), возведение в степень (^) отделяются пробелами и слева, и справа. Если в этой книги и будут встречаться исключения из этого правила, то они будут связаны только с автозаменой издательства.

На моем канале в Rutube создан целый плейлист, где я преобразовываю макросы этой книги так, чтобы «отменить» автозамену издательства и привести эти макросы в нормальный вид. Я не буду здесь публиковать адрес плейлиста, чтобы не создавать инцидентов, связанных с законодательством о рекламе. Просто скажу, что у меня есть несколько страниц в так называемой «всемирной паутине», и в Rutube, и в дзен.

Название канала автора в рутуб: Эксель, судоку, математика…

Название канала в дзен: Эксель, судоку, артобъекты…

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

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

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

Вначале я хотел применять видео только для преобразования макросов, для «отмены» автозамены издательства. Но позже у меня возникла и другая идея — показывать на видео основные моменты, показанные в книге, в том числе и те, что не связаны с корректировкой макросов.

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

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

Глава 2. Создание и подготовка файла

2.1. Создание файла для судоку

В самом начале нужно создать файл Эксель, и сохранить его так, чтобы это был файл с поддержкой макросов. Само название файла должно быть таким:

Судоку_2020.xlsm

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

Здесь расширение файла (xlsm) означает то, что у нас имеется не простой файл Эксель, а файл с поддержкой макросов.

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

Итак, теперь наш файл готов.

2.2. Подготовка файла

Прежде всего надо выбрать тот лист Эксель, на котором будут находиться и основное судоку, и основной квадрат с кандидатами для судоку, и несколько вспомогательных квадратов. Их тоже придется создавать. Например, отдельный квадрат для кандидатов-единиц, отдельный квадрат для тех клеток судоку, у которых есть ровно один кандидат, и так далее. Этому листу Эксель нужно присвоить имя: Основное_судоку. Хоть иногда можно встретить информацию о том, что слово «судоку» — это слово мужского рода, эта информация неверная. Слово «судоку» в русском языке — это существительное среднего рода.

Затем необходимо на этом листе изменить ширину столбцов. Это надо сделать для того, чтобы каждая ячейка Эксель превратилась из прямоугольника в квадрат. Может быть, совсем идеального квадрата не получится, но будет прямоугольник, который очень похож на квадрат. Для этого надо выделить весь лист, а затем установить ширину столбца: 3,00 у.е. (26 пиксель).

Далее можно выделить те клетки Эксель, в которых будет расположено основное судоку. Необходимо также учесть заголовки строк и столбцов судоку, о которых мы говорили во введении этой книги. Можно покрасить каждый блок (квадратик размером 3 на 3) судоку в определенный цвет.

Должна получиться вот какая картинка (рисунок 2.1):

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

Если для заголовков строк Эксель используются цифры, а для заголовков столбцов Эксель — буквы, то при создании заголовков строк и столбцов судоку мы будем поступать иначе: в заголовках строк будут буквы, а в заголовках столбцов — цифры.

Так, например, ячейка А1 судоку — это самая верхняя левая ячейка судоку. Она расположена в ячейке C3 Эксель. Левый столбец Эксель (столбец A), а также верхняя строка Эксель (строка 1) скрыты, но это сделано только для того, чтобы рисунок с расположением основного судоку на листе Эксель был более удобным, чтобы было четко понятно, в какой именно области Эксель расположено всё то, что изображено на рисунке. Если читатели моей книги будут использовать на своем компьютере все то, что написано в данной книге, то я хочу сразу уточнить, что скрытие столбцов и строк, которое часто применяется в книге, совершать не надо, в книге это скрытие происходит только для удобства подачи информации. Если в каких-то ситуациях появится реальная необходимость скрытия столбцов или строк Эксель, то об этом будет сообщено отдельно.

2.3. Создание имен внутри основного судоку

2.3.1. Имя для строки 1

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

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

Начнем с присвоения имен строкам судоку. Первая строка будет верхней строкой. У этой строки судоку будет имя: стр_1. Аналогично, вторая строка судоку будет называться стр_2.

Сам макрос, присваивающий имена строкам судоку, можно составить в несколько этапов.

Первый этап: создание одного макроса, который присвоил бы первой строке макроса имя стр_1.

1) Нажмем на кнопку «Запись макроса» (запустим команду Запись макроса»).

Эту команду и эту кнопку можно найти во вкладке «Разработчик», в группе «Код» (рисунок 2.2):

Если вкладка «Разработчик» по каким-то причинам отсутствует, ее можно добавить. Для этого нужно в меню «Файл» выбрать подменю «Параметры», а затем пункт «Настроить ленту».

Далее появятся параметры Эксель, связанные с настройкой ленты. Для добавления вкладки «Разработчик» нам понадобится правая половина всего этого огромного массива информации. Там находится список, предназначенный для добавления, удаления, переименования и изменения порядка вкладок, групп и команд.

Покажем фрагмент от правой части (из правого столбца, то есть из пункта «Основные вкладки») той информации, что входит в пункт «Настроить ленту» (рисунок 2.3):

Стрелочкой выделена вкладка «Разработчик». Если флажок («галочка») возле этого пункта будет отсутствовать, то это значит, что этого пункта пока нет в перечне видимых вкладок. Иногда вкладка «Разработчик» бывает невидимой (скрытой), обычно это бывает при первом запуске Эксель и длится до тех пор, пока пользователь сам не сделает видимой эту вкладку. Итак, эта проблема решается достаточно просто, включением указанного флажка.

2) После того, как мы убедились, что у нас имеется вкладка «Разработчик», можно приступить к записи макроса. Для этого нужно выбрать команду «Запись макроса», которая находится в группе «Код». Покажем это всё на рисунке 2.4:

Здесь стрелочкой выделена команда «Запись макроса». Когда мы выберем эту команду, нас спросят про имя создаваемого макроса. Присвоим ему имя: Имя_строкам_судоку.

3) Выделим строку 1 судоку, то есть самую верхнюю строку судоку. К этой строке относятся ячейки Эксель от C3 до K3 включительно.

4) Присвоим выделенному диапазону имя: стр_1. Это можно сделать двумя способами (точнее говоря, только одним — любым, на выбор — из следующих двух способов):

1: С помощью Диспетчера имен. Его (то есть диспетчер имен) можно найти так: Вкладка «Формулы», Группа «Определенные имена», кнопка «Диспетчер имен».

После нажатия на кнопку «Диспетчер имен» можно увидеть три варианта действий для каждого имени: «Создать…», «Изменить…» или «Удалить». В нашей конкретной ситуации нам нужен пункт «Создать…», ведь мы же именно создаем новое имя. Затем нужно будет заполнить графу «Имя», написав туда «стр_1» (рисунок 2.5):

Как видно из рисунка 2.5, в графе «Имя» нужно ввести то имя, которые мы хотим присвоить ячейке или диапазону ячеек (в нашем случае это имя «стр_1», оно появилось не сразу при загрузке этой формы, а было введено нами с клавиатуры); в графе «Область» по умолчанию (сразу при первом появлении формы «Создание имени») введен пункт «Книга», и это значит, что вновь создаваемое имя можно будет использовать для каждого листа текущей книги. В графе «примечание» можно (при желании) записать примечание для данного имени, это поле не является обязательным. Если это поле оставить пустым, тогда мы создадим имя без примечания. В графе «Диапазон» вводится адрес ячейки или диапазона ячеек, к которым относится вновь создаваемое имя. Обычно этот пункт тоже заполнять не надо, он заполняется автоматически, он берется из той области Эксель, которую мы уже заранее предварительно выделили. Мы же недавно выделили нужный диапазон ячеек, этот диапазон и отображается здесь. Правда, при желании этот диапазон можно изменить (например, если мы вовремя поняли, что выделили не тот диапазон, или хотим по каким-то причинам изменить диапазон, выбрать другой диапазон).

Кстати, этот диапазон при необходимости можно изменить двумя способами: введя с клавиатуры адрес диапазона или нажав мышкой на квадратик (рисунок 2.6):

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

В более новых версиях Эксель этот же квадратик выглядит немного иначе, в виде стрелочки, направленной вверх (рисунок 2.7):

Как только мы нажмем на кнопку «OK» (которая есть в той форме, что изображена на рисунке 2.6 или 2.7, в зависимости от версии Эксель), то в нашу книгу добавится новое имя. В нашем случае это будет имя «стр_1», оно будет принадлежать диапазону ячеек Эксель, который состоит из строки 1 судоку. Напомню, строка №1 судоку — это самая верхняя строка судоку. Во всех тех судоку, о которых речь идет в данной книге, верхняя строка судоку будет расположена в строго определенном месте Эксель. Это будут ячейки Эксель с C3 по K3 включительно.

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

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

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

Хотелось бы еще несколько слов сказать о том, какие есть правила для создания имен. В имени не может быть пробелов. Имя должно начинаться с буквы или знака нижнего подчеркивания, но не может начинаться с цифры.

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

Итак, второй способ присвоения имени в Эксель связан именно с полем имени.

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

Если мы выделим какой-то диапазон ячеек и попытаемся этим способом присвоить выделенному диапазону или ячейке такое имя, которое уже принадлежит какому-то другому диапазону или ячейке, то мы просто перейдем к тому диапазону или ячейке, которым это самое имя было присвоено ранее. Другими словами, диспетчер имен можно использовать не только для создания новых имен ячеек Эксель или их диапазонов, но и для перехода к ячейкам или диапазонам с известными именами, присвоенными ранее. Кроме того, нельзя присвоить какое-то имя еще раз, если это же имя уже имеется и принадлежит другой ячейке или другому диапазону ячеек. А еще в диспетчере имен можно набирать просто адрес ячейки, например: D1 или R40. Не важно, присвоено ли этой ячейке какое-то имя или не присвоено, все равно с помощью диспетчера имен можно попасть к любой ячейке Эксель, если ввести ее адрес. Поэтому, говоря о правилах создания имен для ячеек Эксель и их диапазонов, нужно добавить еще один пункт: эти имена не должны совпадать с уже существующими адресами ячеек и их диапазонов. Например, нельзя присвоить имя S17 или AAN14.

А применительно к нашей конкретной ситуации (напомню, речь идет о том, что надо присвоить имя стр_1 первой (верхней) строке судоку) нужно поступить так: мы уже заранее выделили нужный диапазон (это строка 1 судоку, она находится в интервале от C3 до K3 Эксель), и нам осталось только одно: ввести новое название интервала стр_1 в поле имени. После этого нажимаем на клавишу Enter. Вот что должно получиться после этого (рисунок 2.9):

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

Небольшое отступление от основной темы. Здесь (на рисунке 2.9) в ячейке B2 Эксель мы впервые встречаем цифру. Это цифра 39, она означает количество заполненных клеток в основном судоку. Чтобы добавить эту цифру к судоку, нужно ввести в ячейку B2 Эксель следующую формулу:

= СЧЁТ (C3:K11)

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

5) Выполним команду «Остановить запись», то есть остановим запись макроса. Эту команду можно найти на вкладке «Разработчик», в группе «Код» Во время записи макроса команда «Запись макроса» превращается в команду «Остановить запись».

Хотя мы создали целый макрос, присвоили ему имя (Имя_строкам_судоку), но у нас пока есть не окончательная, а черновая, предварительная версия этого макроса. Этот макрос пока присваивает имя только первой (верхней) строке судоку. Позже мы исправим этот макрос. Необходимо сделать его таким, чтобы была возможность присвоения имени не одной строке судоку, а каждой строке. Но основной текст макроса, его «каркас», мы уже создали.

Таким образом, имя для первой (верхней) строки судоку создано.

2.3.2. Имена для остальных строк судоку

Итак, только что было создано имя для первой (верхней) строки судоку. Но всего в судоку есть не одна строка, а 9 строк. Чтобы присвоить имена каждой строке судоку, нам нужно будет изменить тот макрос, который присваивал имя первой строке судоку. Что для этого надо сделать?

1) Войдем во вкладку (в меню) Разработчик, группу «Код», выберем там подменю (кнопку, команду) Макросы. Найдем (выделим) макрос под названием Имя_строкам_судоку, нажмем на кнопку Изменить.

Мы увидим текст самого макроса. Вот это текст:

↓ ↓ ↓ ↓

Sub Имя_строкам_судоку ()

′

′ Имя_строкам_судоку Макрос

′

′

Range(″C3:K3″).Select

ActiveWorkbook.Names.Add Name:=″стр_1″, RefersToR1C1:= _

″=Основное_судоку! R3C3:R3C11″

ActiveWorkbook.Names(″стр_1″).Comment =″″

End Sub

↑ ↑ ↑ ↑

Далее покажем скриншот этого макроса (рисунок 2.10):

Итак, уточним, из чего обычно состоит любой макрос.

Первая строка макроса начинается с оператора Sub (это означает «начало макроса»); далее следует имя макроса, а также пустые скобки.

Любой макрос в Эксель можно назвать процедурой. Процедура в Excel на языке VBA — это последовательность инструкций, расположенных в модуле, доступ к которому осуществляется через VBA.

Пустые скобки после имени макроса в Excel обозначают, что процедуре не передаётся никаких аргументов. Чисто теоретически, могут существовать и такие макросы или процедуры, которым передаются какие-то аргументы с самого начала, в этом случае после оператора Sub и названия процедуры будут находиться не пустые скобки, а скобки с какой-то информацией внутри. В данной книге мы эти варианты подробно рассматривать не будем, здесь мы только объясняем, почему почти во всех макросах сразу после имени макроса можно встретить пустые скобки.

Затем идут несколько строк макроса, которые содержат знак апострофа (′). В макросах апостроф — это то же самое, что и оператор Rem, то есть начало ремарки, комментария.

В любом макросе, созданном с помощью команды «Запись макроса», всегда будут присутствовать несколько строк с ремарками (комментариями), причем одна из этих ремарок дублирует первоначальное имя, присвоенное этому макросу. И у макроса, который мы назвали Имя_строкам_судоку, тоже в самом начале должны присутствовать эти строчки. Эти строки с ремарками (комментариями к макросу) часто бывают не нужны совсем, поэтому мы можем эти строки с ремарками удалить. Хотелось бы сообщить, что в данной книге все макросы, созданные с помощью команды «Запись макроса», будут показаны без дополнительных ремарок, которые автоматически создаются в начале макроса. Правда, при желании какие-то свои ремарки можно добавлять, причем ремарки можно помещать в любые места программы (макроса). В принципе, можно и оставить те ремарки, что создаются автоматически в самом начале (другими словами, удаление этих самых ремарок — это ритуал не обязательный, и если они не мешают, то их можно оставить).

Таким образом, текст макроса после удаления строк с ремарками (примечаниями) будет следующим:

↓ ↓ ↓ ↓

Sub Имя_строкам_судоку ()

Range(″C3:K3″).Select

ActiveWorkbook.Names.Add Name:=″стр_1″, RefersToR1C1:= _

″=Основное_судоку! R3C3:R3C11″

ActiveWorkbook.Names(″стр_1″).Comment =″″

End Sub

↑ ↑ ↑ ↑

Дублируем этот текст макроса картинкой-скриншотом (рисунок 2.11):

Итак, любые макросы обычно завершаются строкой «End Sub», что означает завершение макроса. Иногда можно запрограммировать выход из программы и в середине макроса тоже (например, при выполнении определенных условий, которые прописаны в операторах If...Then, что означает в переводе на русский язык Если… Тогда), но в этом случае выход из макроса будет не «End Sub», а «Exit Sub». В любом случае, в макросе может только один раз находиться команда «End Sub», и она может присутствовать исключительно в самом конце макроса.

Предпоследняя строчка этого макроса (та, что находится чуть выше строки End Sub, добавляет комментарии, но не к самому макросу, а к тем именам в Эксель, которые присоединяет к нашему файлу этот самый макрос). Две пустые кавычки в самом конце этой строки макроса означают, что в данном случае применен пустой комментарий, можно сказать, что никакого комментария к создаваемым именам нет вообще.

Конечно же, при желании можно добавить комментарий к именам, расположенным в Эксель. Например, предпоследнюю строчку макроса (ту самую строчку, что находится чуть выше строки End Sub), можно заменить следующим образом:

↓ ↓ ↓ ↓

ActiveWorkbook.Names(″стр_1″).Comment =″строка 1 судоку″

↑ ↑ ↑ ↑

Продублируем эту строчку макроса скриншотом (рисунок 2.12):

Если комментарии к именам не нужны совсем, можно эту строчку макроса пропустить. А можно перед этой строчкой оставить апостроф — знак ремарки — и тогда можно будет позже в любое время снова вернуть строчку с комментарием, удалив знак ремарки. В любом случае, все эти комментарии к именам видны только в диспетчере имен. Он находится во вкладке «Формулы», в группе «Определенные имена». Там находятся все имена, и если у какого-то имени имеется примечание, то оно тоже будет видно в диспетчере имен. Если книга Эксель содержит много имен, то примечания к именам позволят сориентироваться и не запутаться в этом огромном массиве имен. Главное в том, чтобы создаваемое примечание было бы понятным. И сами имена, и примечания к ним можно добавлять как вручную, так и с помощью макросов. Если есть необходимость добавить, изменить или удалить примечание к имени, это можно сделать в любое время с помощью диспетчера имен.

Далее нужно изменить текст этого макроса таким образом, чтобы один макрос смог присвоить имена каждой строке судоку. У нас же имеется 9 строк судоку, а не одна. Можно, конечно, каждой строке присвоить имя отдельно и без макроса, но этот путь не самый быстрый и не самый лучший.

Вот каким получится измененный текст макроса:

↓ ↓ ↓ ↓

Sub Имя_строкам_судоку ()

For i = 3 To 11

Range (Cells (i, 3), Cells (i, 11)).Select

ActiveWorkbook.Names.Add Name:=″стр_″ & i — 2, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C3:R″ & i & ″C11″

ActiveWorkbook.Names (″стр_″ & i — 2).Comment = ″″

Next i

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса в редакторе VBA покажем на рисунке 2.13:

Здесь хотелось бы сделать небольшое отступление от основной темы (от темы судоку) и рассказать про разбивку той информации, что в тексте макроса заключена внутри кавычек. Сравним макросы, скриншоты которых находятся на рисунках 2.11 и 2.13. Обратим внимание только на одну строчку — на ту, которая добавляет комментарии к именам. Вот как эта строчка выглядела в первом макросе:

↓ ↓ ↓ ↓

ActiveWorkbook.Names(″стр_1″).Comment =″″

↑ ↑ ↑ ↑

И вот как та же строчка выглядит уже во втором случае:

↓ ↓ ↓ ↓

ActiveWorkbook.Names (″стр_″ & i — 2).Comment = ″″

↑ ↑ ↑ ↑

Приведенные фрагменты разных вариаций одного и того же макроса очень похожи между собой. Разница только в маленьком фрагменте. В первом случае это (″стр_1″). А втором — (″стр_″ & i — 2).

Мы заменили цифру 1 на i-2, то есть на формулу, в которой есть и переменная (i), и действие вычитание. Эта замена произошла в соответствии со всеми правилами. Все замененные элементы мы освободили от кавычек, а также отделили от других частей формулы VBA знаком амперсанд (&), который со всех сторон окружен пробелами. Мы еще много раз будем встречать подобные ситуации, когда в формулах внутри ячеек Эксель или в фрагментах макроса в редакторе VBA придется разбивать текст, который находится внутри кавычек, заменять фрагменты этого текста. В других главах этой книги еще раз будут подробно описаны правила подобных замен. Это дублирование происходит специально, поскольку данная замена текста внутри кавычек имеет очень большое значение, она может пригодиться при решении разных задач программирования.

А теперь, продолжим. Мы видим, что в тексте этого макроса (Имя_строкам_судоку) несколько раз используется выражение «i-2». Это значит, что мы можем ввести другую переменную (например, m), которая будет равна этому самому значению i-2. Тогда макрос будет выглядеть так:

↓ ↓ ↓ ↓

Sub Имя_строкам_судоку ()

For i = 3 To 11

m = i — 2

Range (Cells (i, 3), Cells (i, 11)).Select

ActiveWorkbook.Names.Add Name:=″стр_″ & m, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C3:R″ & i & ″C11″

ActiveWorkbook.Names (″стр_″ & m).Comment = ″″

Next i

End Sub

↑ ↑ ↑ ↑

А вот и скриншот этой вариации макроса (рисунок 2.14):

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

↓ ↓ ↓ ↓

ActiveWorkbook.Names (″стр_″ & i — 2).Comment = ″строка ″ & i — 2 & ″ судоку″

↑ ↑ ↑ ↑

Скриншот этой строки макроса приведем ниже (рисунок 2.15):

Если мы используем новую переменную m, тогда эта же строка будет выглядеть так:

↓ ↓ ↓ ↓

ActiveWorkbook.Names (″стр_″ & m).Comment = ″строка ″ & m & ″ судоку″

↑ ↑ ↑ ↑

Скриншот этой строки макроса изобразим на рисунке 2.16:

Макрос готов, но у него есть один существенный недостаток: этот макрос очень медленный.

Поэтому сейчас мы рассмотрим, как именно можно ускорить работу многих макросов (конечно же, речь идет об ускорении не сколько работы макросов, сколько об ускорении Эксель в целом).

Многие макросы замедляют свою работу по следующим причинам:

1. Часто срабатывает обновление экрана.

2. Постоянно происходит автоматический пересчет формул.

3. Постоянно происходит отслеживание событий.

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

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

А вначале хотелось бы сказать о том, что макросы можно создавать не только с помощью команды «Запись макроса». Есть и еще один способ создания: непосредственно в редакторе VBA. В этом случае текст макроса будет набираться непосредственно с клавиатуры.

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

Итак, расскажем подробно, как можно зайти в редактор VBA:

1. Найдем вкладку «Разработчик», выберем группу «Код». Затем выберем команду «Visual Basic» (рисунок 2.17):

После этого мы войдем в этот самый редактор VBA. Главный его недостаток состоит в том, что эта часть Эксель обычно не переводится на разные языки мира, поэтому необходимо просто смириться с тем, что основной язык редактора VBA — английский.

Кстати, в этой книге несколько раз уже говорилось о том, что тексты разных макросов можно копировать из электронной версии этой книги (затем, при необходимости, исправить те «ошибки», которые могут присутствовать из-за автозамены символов, которая присутствует в издательстве, публикующем мою книгу, об этом уже говорилось ранее), а затем вставлять нужный текст макросов в редактор VBA. Именно команда (кнопка) «Visual Basic», о которой говорится сейчас, позволит перейти в этот самый редактор VBA. В этом редакторе тексты макросов можно не только вставлять, предварительно скопировав их из любых источников, но и просто набирать с клавиатуры.

Итак, как уже говорилось ранее, нам необходимо создать еще два макроса, и создавать мы их будем непосредственно в редакторе VBA, или просто в редакторе. Первый макрос будет включать ускорение Эксель, а второй макрос будет выключать ускорение, то есть возвращать всё на свои места. Между запуском этих двух макросов нужно запустить «основной макрос», то есть тот самый макрос, работу которого необходимо ускорить, но этот пункт мы рассмотрим достаточно подробно чуть позже, пока лишь рассмотрим только те макросы, которые включают и выключают ускорение.

Конечно же, работа «с ускорением» имеет и «плюсы», и «минусы». Главный «плюс» — ускоренная работа Эксель. Главный «минус» в том, что это ускорение применимо не ко всем макросам. Лучше перед использованием так называемого «ускорения» сделать копии нужных файлов, чтобы не потерять важные данные. В любом случае, если в данной книге приводятся какие-то макросы и содержится рекомендация использовать эти макросы с «ускорением» (либо приводится текст макросов, в котором видно, что этот макрос используется с «ускорением»), то это значит, что при применении «ускорения» к этим конкретным макросам мы получим только сокращение времени выполнения макроса, и при этом не будет наблюдаться никаких негативных последствий (например, «зависания» Эксель или искажения данных). К сожалению, всего этого не всегда можно будет наблюдать, если мы будем использовать так называемое «ускорение» для любых других макросов.

Во время так называемого «ускорения» нет пересчета формул, и это значит, что некоторые данные могут отражаться некорректно, это касается зависимых ячеек. Но все эти недостатки исправятся после запуска того макроса, который выключает ускорение. Итак, вот тексты этих макросов. Первый макрос включает ускорение.

↓ ↓ ↓ ↓

Sub Ускорение_включить ()

′Отключаем обновление экрана

Application.ScreenUpdating = False

′Отключаем автоматический пересчет формул

Application.Calculation = xlCalculationManual

′Отключаем отслеживание событий

Application. EnableEvents = False

′Отключаем разбивку на печатные страницы

ActiveWorkbook.ActiveSheet. DisplayPageBreaks = False

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса разместим на рисунке 2.18:

И второй макрос, который выключает ускорение:

↓ ↓ ↓ ↓

Sub Ускорение_выключить ()

′Возвращаем обновление экрана

Application.ScreenUpdating = True

′Возвращаем автоматический пересчет формул

Application.Calculation = xlCalculationAutomatic

′Включаем отслеживание событий

Application. EnableEvents = True

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса — на рисунке 2.19:

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

Мы уже говорили о том, что само «ускорение», о котором говорится в данной книге, можно применить не ко всем макросам. Более подробно мы расскажем про то, где применять ускорение нельзя, или можно, но с определенными особенностями, мы расскажем позже, в последних главах этой книги. Пока лишь скажем одно: если в макросах совсем нет формул для ячеек Эксель, но есть присвоение (создание) имен для ячеек Эксель и их диапазонов, построение графиков, то можно смело применять это самое «ускорение».

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

Но теперь продолжим говорить про ускорение тех файлов, для которых это ускорение действительно приемлемо, то есть приведет только к ускорению Эксель и не будет иметь никаких негативных последствий или искажения данных.

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

Вариант №1: С помощью создания одного нового (дополнительного) макроса. Этот вариант можно реализовать с помощью команды «Запись макроса».

Все это можно осуществить за несколько шагов:

1: Начинаем запись нового макроса. Нажимаем на кнопку «Запись макроса», новому макросу присвоим имя:

Имя_строкам_судоку_быстро.

2: Запускаем макрос, включающий так называемое «ускорение». Чтобы запустить этот макрос, нужно найти вкладку «Разработчик», выбрать в ней группу «Код». Затем запустить команду «Макросы» (рисунок 2.20):

После нажатия на кнопку «Макросы» мы увидим список всех макросов, которые уже есть в нашем файле.

В нашем конкретном случае сначала нужно найти макрос под названием Ускорение_включить; затем нажать на кнопку «выполнить», после чего дождаться, когда этот макрос выполнится.

Кстати, именно так (с помощью команды/кнопки «Макросы») и выполняются (запускаются) все макросы.

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

3. Запускаем «основной макрос». В нашем конкретном случае речь идет о макросе под названием Имя_строкам_судоку.

4. Запускаем макрос, выключающий так называемое «ускорение». напомню, этот макрос называется Ускорение_выключить.

5. Подождем, когда макрос, выключающий ускорение, завершит свою работу, а затем остановим запись макроса. Напомню, что остановить запись макроса можно с помощью той самой команды во вкладке «Разработчик» и в группе «Код», с которой мы и начинали запись макроса. Во время записи макроса кнопка «Запись макроса» преобразуется в кнопку «Остановить запись». Останавливая запись макроса, мы тем самым завершаем запись макроса под названием Имя_строкам_судоку_быстро.

Если мы захотим посмотреть текст этого макроса, то мы увидим следующую информацию:

↓ ↓ ↓ ↓

Sub Имена_строкам_судоку_быстро ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

Application.Run ″Судоку_2020.xlsm! Имя_строкам_судоку″

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

На рисунке 2.21 покажем скриншот текста этого макроса в редакторе VBA.

Как выяснилось во время написания этой книги, автозамена текста, которая присутствует во многих книжных издательствах, автоматически добавляет пробел после восклицательного знака. Это я обращаюсь ко всем моим читателям. В макросе, который мы только что привели, есть восклицательные знаки, а пробелы после восклицательных знаков добавлены автозаменой. После этих восклицательных знаков не должно быть пробелов. Правда, если ввести в редакторе VBA этот текст вместе с пробелами после восклицательных знаков, то макрос будет продолжать корректно работать, словно и нет этих пробелов. Другими словами, нахождение пробелов в данной ситуации хоть и является ошибкой в программировании, но эта ошибка не очень большая, она не приводит к неправильной или ошибочной работе макроса. Но условно можно сказать, что «в правилах хорошего тона» при программировании во время написании строки кода (макроса), которая запускает один макрос из другого макроса, не надо отделять пробелами тот восклицательный знак, который отделяет название (имя) файла от названия того макроса, который необходимо запустить.

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

Вариант №2: Почти то же самое, что и при первом варианте, но без создания нового макроса.

При этом варианте алгоритм действий будет следующий:

1. Запускаем макрос, который включает ускорение.

2. Запускаем тот макрос, который необходимо ускорить. В нашем случае это будет макрос под названием Имя_строкам_судоку.

3. Запускаем макрос, выключающий ускорение.

Вариант №3: Изменить тот макрос, который надо ускорить. Это можно тоже сделать несколькими разными способами:

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

При использовании этого способа макрос Имя_строкам_судоку примет следующий вид:

↓ ↓ ↓ ↓

Sub Имя_строкам_судоку ()

′Отключаем обновление экрана

Application.ScreenUpdating = False

′Отключаем автоматический пересчет формул

Application.Calculation = xlCalculationManual

′Отключаем отслеживание событий

Application. EnableEvents = False

′Отключаем разбивку на печатные страницы

ActiveWorkbook.ActiveSheet. DisplayPageBreaks = False

For i = 3 To 11

Range (Cells (i, 3), Cells (i, 11)).Select

ActiveWorkbook.Names.Add Name:=″стр_″ & i — 2, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C3:R″ & i & ″C11″

ActiveWorkbook.Names (″стр_″ & i — 2).Comment = ″строка ″ & i — 2 & ″ судоку″

Next i

′Возвращаем обновление экрана

Application.ScreenUpdating = True

′Возвращаем автоматический пересчет формул

Application.Calculation = xlCalculationAutomatic

′Включаем отслеживание событий

Application. EnableEvents = True

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса в редакторе VBA на рисунке 2.22:

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

При использовании этого способа нужно предварительно добавить в нужный файл (в книгу Эксель) те два макроса, которые отвечают за включение и отключение ускорения. Мы про эти макросы уже говорили.

При использовании этого способа макрос Имя_строкам_судоку примет следующий вид:

↓ ↓ ↓ ↓

Sub Имя_строкам_судоку ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 3 To 11

Range (Cells (i, 3), Cells (i, 11)).Select

ActiveWorkbook.Names.Add Name:=″стр_″ & i — 2, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C3:R″ & i & ″C11″

ActiveWorkbook.Names (″стр_″ & i — 2).Comment = ″строка ″ & i — 2 & ″ судоку″

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса на рисунке 2.23:

Лично я (автор этой книги) предпочитаю тот вариант, которому здесь присвоен номер 3.2. Но на вкус и цвет, как известно, товарища нет. Возможно, кому-то из моих читателей больше понравится другой вариант.

Мы уже раньше говорили о том, что к именам ячеек или диапазонов ячеек можно добавлять префиксы. Наш файл (наша книга Эксель), действительно, будет содержать немало разных имен. Если мы не боимся запутаться во множестве этих имен, то оставим все эти имена без изменений, то есть без префиксов. Но для удобства можно к именам добавить префиксы. Например, префикс «bs..» будет означать, что данное имя будет иметь отношение к основному судоку (поскольку судоку — это латинский квадрат, то мы часто будем прибегать не только к латинским буквам, но и к словам латинского языка; bs — это первые буквы слов basic Sudoku, что в переводе означает «основное судоку»). Конечно же, после префикса вполне достаточно и одной точки, но я снова столкнулся с той автозаменой, которая есть у издательства, где я публикую эту книгу. Эта самая автозамена часто ставит пробел сразу после точки. Для художественных произведений наличие пробела после точки всегда обязательно, но тут речь идет о программировании, это уже другая история. Тут после точек не всегда должен быть пробел. Точки внутри компьютерных программ — это уже не знаки препинания. Поэтому я обращаюсь ко всем моим читателям: можно, конечно, после префикса оставить и две точки сразу, страшного в этом ничего не будет, более того, это можно позиционировать как своеобразную «фишку», «изюминку». Но если хочется оставить только одну точку после префиксов, то вторую точку можно просто убрать. В этой книге всегда будут располагаться две точки сразу после префиксов, поскольку в этом случае автозамена не будет добавлять после двух подряд точек пробелы, которые тут совершенно не нужны. Убирать вторую точку или нет после префикса — каждый читатель моей книги сам решит для себя.

Чисто теоретически, можно к имени ячейки или диапазона применить и несколько префиксов (например, двойной префикс), но мы пока остановимся на варианте с одним префиксом.

Тогда при составлении макроса для присвоения имен строкам судоку с использованием варианта 3.2, о котором говорилось ранее, мы получим следующий текст макроса (этот вариант макроса будет создавать имена строк с префиксом):

↓ ↓ ↓ ↓

Sub Имя_строкам_судоку ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 3 To 11

Range (Cells (i, 3), Cells (i, 11)).Select

ActiveWorkbook.Names.Add Name:=″bs..стр_″ & i — 2, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C3:R″ & i & ″C11″

ActiveWorkbook.Names (″bs..стр_″ & i — 2).Comment = ″строка ″ & i — 2 & ″ судоку″

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса с двойным префиксом в основных именах изображен на рисунке 2.24.

А можно оба варианта объединить, в этом случае один макрос будет создавать имена строкам судоку и с префиксом, и без префикса. Вот каким будет текст этого макроса:

↓ ↓ ↓ ↓

Sub Имя_строкам_судоку ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 3 To 11

Range (Cells (i, 3), Cells (i, 11)).Select

ActiveWorkbook.Names.Add Name:=″стр_″ & i — 2, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C3:R″ & i & ″C11″

ActiveWorkbook.Names (″стр_″ & i — 2).Comment = ″строка ″ & i — 2 & ″ судоку″

ActiveWorkbook.Names.Add Name:=″bs..стр_″ & i — 2, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C3:R″ & i & ″C11″

ActiveWorkbook.Names (″bs..стр_″ & i — 2).Comment = ″строка ″ & i — 2 & ″ судоку″

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса на рисунке 2.25:

Итак, дело сделано. Имена строкам судоку присвоены. Причем даже в нескольких экземплярах. Каждый выбирает для себя, какие именно имена использовать чаще (с префиксами или без них). Можно выбирать любой вариант, какой больше нравится. А можно выбрать и оба варианта, а именно: добавлять и те имена, что содержат префиксы, и другие имена, которые без префиксов. Как уже говорилось ранее, одной и той же ячейке Эксель или группе (диапазону) ячеек можно присваивать одновременно несколько разных имен.

После того, как мы присвоили все имена строкам судоку, можно проверить, что получилось. Например, можно выделить строку 8 судоку и посмотреть, что будет изображено в поле имени (рисунок 2.26):

Итак, что мы видим на рисунке 2.26? Выделена строка 8 судоку (это ячейки с C10 по K10 Эксель включительно). В поле имени — название (имя) выделенного диапазона. Мы знаем, что этому диапазону присвоено одновременно несколько разных имен, поэтому в диспетчере имен мы видим только то имя диапазона, которое по алфавиту будет стоять первым. Мы уже говорили о том, что на первом месте будут буквы латинского алфавита, а буквы русского алфавита будут после. Поэтому мы видим то имя диапазона, которое начинается с буквы латинского алфавита.

На этом тему о присвоении имен строкам судоку можно считать вполне исчерпанной.

2.3.3. Имена для столбцов судоку

Далее присвоим имена столбцам судоку.

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

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

Вот каким будет окончательный вариант этого макроса:

↓ ↓ ↓ ↓

Sub Имя_столбцам_судоку ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For j = 3 To 11

Range (Cells (3, j), Cells (11, j)).Select

ActiveWorkbook.Names.Add Name:=″сто_″ & j — 2, RefersToR1C1:= _

″=Основное_судоку! R3C″ & j & ″:R11″ & ″C″ & j

ActiveWorkbook.Names (″сто_″ & j — 2).Comment = ″столбец ″ & j — 2 & ″ судоку″

ActiveWorkbook.Names.Add Name:=″bs..сто_″ & j — 2, RefersToR1C1:= _

″=Основное_судоку! R3C″ & j & ″:R11″ & ″C″ & j

ActiveWorkbook.Names (″bs..сто_″ & j — 2).Comment = ″столбец ″ & j — 2 & ″ судоку″

Next j

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

На рисунке 2.27 изображен скриншот этого макроса в редакторе VBA.

Имена строкам и столбцам судоку присвоены, далее присвоим имена блокам (квадратикам) судоку.

Возможно, у моих читателей может возникнуть вопрос о том, как создавать копию макроса. Это лучше сделать в редакторе VBA. Там можно выделить весь текст макроса, затем нажать на правую кнопку мыши. Появится контекстное меню, надо выбрать пункт «Copy», то есть «Копировать». Затем снимаем выделение текста макроса, находим в редакторе VBA пустое место и уже на пустом месте, не внутри какого-либо макроса, снова нажимаем на правую кнопку мыши. Теперь надо выбрать пункт «Paste», то есть «Вставить». После копирования макроса нужно сразу изменить имя у второго макроса, двух макросов с одним именем в одном файле Эксель быть не должно.

2.3.4. Имена для блоков судоку

Теперь нужно аналогичным образом присвоить номера всем блокам (квадратикам) судоку. Но вначале определимся с нумерацией блоков (рисунок 2.28):

Итак, левый верхний блок — это блок с номером 1, верхний средний блок — это блок с номером 2, и так далее.

Блоку №1 присвоим имя бло_1, блоку №2 присвоим имя бло_2, остальным блокам присвоим имена аналогично. Если говорить про те имена блоков, которые будут содержать префиксы, тогда у блока номер один будет имя: bs..бло_1.

Можно, конечно, сначала с помощью макроса присвоить имена первому блоку, затем отредактировать этот макрос таким образом, чтобы он присваивал имена не одному блоку, а всем блокам сразу. Но этот вариант не самый быстрый. Мы же уже ранее достаточно подробно рассматривали такую методику, когда говорили про присвоение имен всем строкам судоку. Этой методикой можно было воспользоваться ранее, потому что на первом этапе создания судоку у нас не было других похожих макросов. Но теперь у нас уже есть несколько похожих макросов, их только надо немного переделать, создать копию нужного макроса и присвоить этой копии макроса другое имя. И это всё значит, что мы скопируем тот макрос, который давал имена столбцам судоку, а новую копию макроса назовем Имя_блокам_судоку.

Далее покажем окончательный вариант этого макроса:

↓ ↓ ↓ ↓

Sub Имя_блокам_судоку ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

k = 1

For i = 3 To 9 Step 3

For j = 3 To 9 Step 3

Range (Cells (i, j), Cells (i +2, j + 2)).Select

ActiveWorkbook.Names.Add Name:=″бло_″ & k, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C″ & j & ″:R″ & i +2 & ″C″ & j +2

ActiveWorkbook.Names (″бло_″ & k).Comment = ″блок ″ & k & ″ судоку″

ActiveWorkbook.Names.Add Name:=″bs..бло_″ & k, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C″ & j & ″:R″ & i +2 & ″C″ & j +2

ActiveWorkbook.Names (″bs..бло_″ & k).Comment = ″блок ″ & k & ″ судоку″

k = k +1

Next j

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса в редакторе VBA покажем на рисунке 2.29:

Но, поскольку часто можно блоки называть не только блоками, но и квадратиками, можно создать еще один макрос, который будет присваивать имена не блокам, а квадратикам. Тогда первый квадратик будет называться ква_1, а если применить префикс — тогда bs..ква_1. Вот текст этого макроса.

↓ ↓ ↓ ↓

Sub Имя_квадратикам_судоку ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

k = 1

For i = 3 To 9 Step 3

For j = 3 To 9 Step 3

Range (Cells (i, j), Cells (i +2, j + 2)).Select

ActiveWorkbook.Names.Add Name:=″ква_″ & k, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C″ & j & ″:R″ & i +2 & ″C″ & j +2

ActiveWorkbook.Names (″ква_″ & k).Comment = ″квадратик ″ & k & ″ судоку″

ActiveWorkbook.Names.Add Name:=″bs..ква_″ & k, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C″ & j & ″:R″ & i +2 & ″C″ & j +2

ActiveWorkbook.Names (″bs..ква_″ & k).Comment = ″квадратик ″ & k & ″ судоку″

k = k +1

Next j

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

На рисунке 2.30 покажем скриншот этого макроса в редакторе VBA.

Теперь тему присвоения имен блокам и квадратикам судоку можно считать исчерпанной.

2.3.5. Имена клеткам основного судоку

Итак, имена строкам, столбцам и блокам судоку присвоены. Теперь нужно присвоить имена каждой клетке судоку. Здесь тоже мы будем присваивать двойные имена. Во-первых, клетки могут именоваться в стиле кле_11, кле_12, кле_13, и т. д. При этом первая цифра будет означать номер строки, вторая цифра — номер столбца. Не обязательно читать (и произносить) эти цифры как двузначные числа (в стиле кле-одиннадцать, кле-двенадцать), лично мне нравится прочтение каждой цифры в отдельности. Например, кле-один-один, кле-один-два, и так далее. Мне кажется, что так было бы более удобно, чтобы было очевидно отделение номера строки от номера столбца.

Можно добавить и имена-дублеры, они будут содержать один префикс (bs.). А вот комментарии к этим именам можно опустить, не применять. Здесь оставлена одна точка после префикса bs, поскольку не всегда автозамена издательства оставляет пробел после одиночной точки.

Это будет принцип №1 — то есть такой принцип присвоения имен клеткам судоку, при котором клетки будут иметь «цифровые» имена (такие имена, когда и строки судоку, и столбцы судоку имеют цифровую кодировку).

Но есть и принцип №2. В этом случае клеткам можно присваивать имена по-другому. Так, например, левая верхняя клетка судоку будет именоваться кле_А1 (читаем: кле-а-один).

Кстати, буквы от А до К, которые понадобятся при присвоении этих имен и символизируют заголовки строк судоку, у нас уже имеются (они уже находятся в столбце B Эксель). Имеются и нужные цифры, которые символизируют номера столбцов судоку, они расположены в строке 2 Эксель. Поэтому сложностей с присвоением нужных имен ячейкам быть не должно, можно брать нужные буквы и цифры именно из листа Эксель, надо только указать ссылки на нужные ячейки.

Вот текст макроса, присваивающего номера клеткам судоку по принципу номер один:

↓ ↓ ↓ ↓

Sub Имена_основным_клеткам ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 1 To 9

For j = 1 To 9

Cells (i +2, j + 2).Select

ActiveWorkbook.Names.Add Name:=″кле_″ & _

i & j, RefersToR1C1:= _

″=Основное_судоку! R″ & i +2 & ″C″ & j +2

ActiveWorkbook.Names.Add Name:=″bs..кле_″ & _

i & j, RefersToR1C1:= _

″=Основное_судоку! R″ & i +2 & ″C″ & j +2

Next j

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса — на рисунке 2.31:

Ну и, во-вторых, клетки можно именовать по принципу кле_А1, кле_А2, кле_А3 и т. д. Вот текст макроса, который присваивает имена клеткам судоку по принципу №2:

↓ ↓ ↓ ↓

Sub Имена_основным_клеткам_2 ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 1 To 9

For j = 1 To 9

Cells (i +2, j + 2).Select

ActiveWorkbook.Names.Add Name:=″кле_″ & _

Cells (i +2, 2) & Cells (2, j +2), RefersToR1C1:= _

″=Основное_судоку! R″ & i +2 & ″C″ & j +2

ActiveWorkbook.Names.Add Name:=″bs..кле_″ & _

Cells (i +2, 2) & Cells (2, j +2), RefersToR1C1:= _

″=Основное_судоку! R″ & i +2 & ″C″ & j +2

Next j

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса покажем на рисунке 2.32.

Можно объединить в одном макросе оба эти принципа (номер один и номер два). В этом случае текст макроса будет таким:

↓ ↓ ↓ ↓

Sub Имена_основным_клеткам_3 ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 1 To 9

For j = 1 To 9

Cells (i +2, j + 2).Select

ActiveWorkbook.Names.Add Name:=″кле_″ & _

i & j, RefersToR1C1:= _

″=Основное_судоку! R″ & i +2 & ″C″ & j +2

ActiveWorkbook.Names.Add Name:=″bs..кле_″ & _

i & j, RefersToR1C1:= _

″=Основное_судоку! R″ & i +2 & ″C″ & j +2

ActiveWorkbook.Names.Add Name:=″кле_″ & _

Cells (i +2, 2) & Cells (2, j +2), RefersToR1C1:= _

″=Основное_судоку! R″ & i +2 & ″C″ & j +2

ActiveWorkbook.Names.Add Name:=″bs..кле_″ & _

Cells (i +2, 2) & Cells (2, j +2), RefersToR1C1:= _

″=Основное_судоку! R″ & i +2 & ″C″ & j +2

Next j

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса находится на рисунке 2.33.

Как и прежде, можно вводить в макрос новые дополнительные переменные. Например, m (m=i+2), а также n (n=j+2).

В этом случае этот макрос примет следующий вид:

↓ ↓ ↓ ↓

Sub Имена_основным_клеткам_3 ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 1 To 9

For j = 1 To 9

m=i+2

n=j+2

Cells (m, n).Select

ActiveWorkbook.Names.Add Name:=″кле_″ & _

i & j, RefersToR1C1:= _

″=Основное_судоку! R″ & m & ″C″ & n

ActiveWorkbook.Names.Add Name:=″bs..кле_″ & _

i & j, RefersToR1C1:= _

″=Основное_судоку! R″ & m & ″C″ & n

ActiveWorkbook.Names.Add Name:=″кле_″ & _

Cells (m, 2) & Cells (2, n), RefersToR1C1:= _

″=Основное_судоку! R″ & m & ″C″ & n

ActiveWorkbook.Names.Add Name:=″bs..кле_″ & _

Cells (m, 2) & Cells (2, n), RefersToR1C1:= _

″=Основное_судоку! R″ & m & ″C″ & n

Next j

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

Покажем скриншот этого макроса на рисунке 2.34.

Вот, в принципе, и всё, что можно сказать о присвоении имен самым главным элементам основного судоку.

2.3.6. Еще несколько имен

Мы присвоили имена строкам, столбцам, квадратикам (блокам) основного судоку, а также каждой клетке основного судоку.

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

Этой клетке можно присвоить имя даже без макроса. Это имя будет простым:

КЧ

Буквы русские, прописные. Сокращение от словосочетания «контрольное число».

Можно (хотя и не обязательно) этой же ячейке также присвоить еще несколько имен и их дублеров, например

bs..КЧ

счет

bs..счет

Кстати, весь квадрат основного судоку — а это интервал ячеек Эксель от C3 до K11 — тоже должен быть с именем. Присвоим этому интервалу простое имя:

судоку

Этому имени можно не присваивать дублер с префиксом. Здесь все просто. Имя судоку будет означать одно: это квадрат 9 на 9 клеток, который нужно заполнить согласно правилам судоку.

На этом завершается присвоение имен самым важным элементам основного судоку.

Напомню, что мы присвоили имена всем строкам, столбцам, блокам и простым одиночным клеточкам основного судоку, а также квадрату, содержащему все клетки основного судоку. Наличие всех этих имен поможет нам при отгадывании судоку.

2.4. Имена на другом листе Эксель

Нам нужно создать второй лист на основном файле Эксель. Переименуем этот второй лист Эксель, присвоим ему новое имя (новый заголовок): Имена.

В этом листе можно установить ширину каждого столбца: 4 у. е. При этом каждая ячейка Эксель будет не квадратом, а прямоугольником небольшой ширины.

Перейдем на лист под названием Имена, создадим в нем столбцы указанной ширины. Мы часто будем обращаться к этому листу Эксель, а пока создадим в нем несколько имен.

Ячейке A1 этого листа присвоим имя: ast. Это первые три буквы от латинского слова «Asterisco», что означает «звездочка». Поместим в эту ячейку тот самый символ, о котором мы уже говорили ранее, он очень похож на вид зонтика сверху). Этот символ можно найти во вкладке «Вставка», в группе «Символы», там есть кнопка «Символ». Я понимаю, что этот символ не очень похож на звездочку, но в любом случае (рано или поздно) этот символ, который находится в ячейке A1 листа Эксель под названием Имена, можно будет заменить на какой-то другой, какой больше нравится. Можно заменить и на «звездочку» (*). Главное, чтобы это был просто символ, то есть не буква и не цифра. Ну и желательно, чтобы этот символ не был очень похож на какую-нибудь букву или цифру. Этот символ нам понадобится чуть позже, когда мы будем заполнять квадрат, в котором будут расположены все кандидаты для судоку. В любом случае, мы условно будем называть этот символ «звездочкой», даже если он совсем не похож на «звездочку». Теперь расскажем про использование этого символа. Если какая-то ячейка основного судоку, например, Д2 основного судоку, будет заполнена цифрой (то есть будет не пустой), тогда в квадрате с кандидатами (его мы создадим позже) все 9 клеток, в которых содержатся возможные кандидаты для этой ячейки, будут состоять из этих так называемых «звездочек». Мы эту картину уже видели во введении этой книги. Но здесь приведем еще один пример с этим символом (рисунок 2.35):

На этом рисунке расположен фрагмент квадрата кандидатов. Этот фрагмент показывает только кандидаты для среднего блока кандидатов, то есть для блока №5. Хотя некоторые строки и столбцы Эксель тут скрыты, это было сделано только для того, чтобы четко было видно, к каким строкам и столбцам Эксель относится информация, изображенная на рисунке. Столбец M Эксель показывает заголовки строк судоку, строка 2 Эксель показывает заголовки столбцов судоку. Как мы уже говорили, на этом рисунке мы видим кандидаты для центрального (пятого) квадратика судоку. В основном судоку в этом квадратике есть 4 цифры, это клетки Г5, Г6, Е5 и Е6 основного судоку. Для этих клеток основного судоку уже кандидаты не нужны, а потому именно те клетки квадрата кандидатов, которые отвечают за кандидаты именно этих клеток судоку, состоят только из тех символов, которые мы назвали «звездочками».

Для ячейки A1 этого листа создадим еще одно имя, оно будет дублером имени ast, Мы не будем добавлять префикс к имени ast. Просто возьмем первые три буквы русского слова «звезда», и получится совершенно новое имя: зве. Итак, одно имя содержит три латинские буквы, второе имя — три русские буквы.

Кроме того, символ из ячейки A1 мы можем называть «звездочка», это имя не будет никак отражаться официально, это не будет отражаться в диспетчере имен или где-то еще. Просто нужно иметь в виду: если в этой книге будет звучать слово «звездочка», то это значит, что речь идет о том символе или о тех символах, которые находятся в ячейке A1 листа под названием Имена. Мы в любое время можем исправить эту информацию, заменить тот символ, что там находится сейчас, на другой символ или на другие символы. Главное, чтобы было удобно. В любом случае, «звездочка» — это содержимое именно этой ячейки.

Ячейка A2 этого же листа будет пустой. Но это будет не простая пустота, а пустота «с секретиками». Там будет следующая формула:

=″″

Это знак равенства, а вслед за ним — две кавычки подряд. Между кавычек нет ничего, просто пустота, нет даже пробела. Это очень важно. В любых других пустых ячейках нет этой формулы. И значение других пустых ячеек может быть приравнено к нулю, чего нельзя сказать про нашу ячейку A2. В нашей ячейке A2 пустота, но не простая пустота.

Эта пустота «с секретиком», или «с секретиками», будет достаточно часто использоваться в этой книге.

Можно провести эксперимент. В ячейку D1 эксель (мы пока по-прежнему находимся на листе Имена, о возвращении на лист под названием Основное_судоку будет позже сообщено дополнительно) можно ввести формулу:

=A2+A3

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

Можно провести еще один эксперимент.

В ячейку D2 можно ввести формулу:

=ЕЧИСЛО (A2)

Мы увидим результат: сообщение «ЛОЖЬ». Это значит, что то, что находится в ячейке A2, не является числом. Правда, если мы устроим аналогичную «проверку» ячейке A12, которая пока просто пустая и не содержит вообще никаких формул, то результат будет тем же, там тоже не число.

Поэтому можно провести еще один эксперимент.

В ячейку D3 введем формулу:

=A2+0

В результате в ячейке D3 мы получим сообщение об ошибке:

#ЗНАЧ!

Но так и должно быть, это действительно ошибка. Ведь нельзя складывать число и то, что числом не является.

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

=A12+0

В принципе, теперь столбец D можно удалить совсем (или просто очистить). Мы его использовали только для того, чтобы показать несколько «секретов» той пустоты, что находится в ячейке A2.

Итак, ячейка A2 содержит пустоту с «секретом» (ну или с секретиками, как было сказано ранее). Теперь надо присвоить этой ячейке имена. Первое имя будет пус (первые три буквы от русского слова «пустота». Второе имя у этой же ячейки будет vac — первые три буквы от латинского слова «vacuum», что в переводе значит «пустота».

Теперь переходим к ячейке A3. Введем туда знак апострофа, а затем два знака минус подряд (без пробелов между этими знаками). Вот что должно получиться (покажем фрагмент листа Эксель, рисунок 2.36):

Что можно сказать по этому рисунку? Текущая ячейка — это ячейка A3 Эксель. Эта ячейка текущая потому, что она со всех сторон выделена окантовкой серо-зеленого цвета. Кроме того, текущий столбец Эксель (столбец A) выделен особым форматом, он более темный, чем другие столбцы. Точно то же самое можно сказать и про строку 3 — это текущая строка, и она тоже выделена особо, чуть темнее, чем остальные строки. В поле имени мы видим min, это значит, что именно такое имя присвоено этой ячейке.

Итак, нужно присвоить этой ячейке (то есть ячейке A3 Эксель) это имя (то есть имя min). Оно является сокращением от латинского слова «minima», то есть «минимум».

Присвоим этой ячейке (ячейке A3) и второе имя: мин — сокращение от русского слова «минимум». Главное — не запутаться: в Эксель есть формула, которая содержит эти же буквы и в той же последовательности.

Это формула МИН.

Она означает минимум.

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

Хотя можно придумать этой ячейке еще одно имя: кий. Бильярдный кий, действительно, очень похож на знак минуса. Это второе имя мы присваиваем исключительно для того, чтобы не путать наше имя «мин» с формулой минимума МИН.

Но если среди моих читателей есть такие люди, для кого не составит проблем схожесть имени мин с функцией МИН, то можно использовать именно имя мин вместо имени кий. Если у любой ячейки Эксель или у массива этих ячеек будет присутствовать одновременно несколько имен в Эксель, то это значит, что можно в любое время применять любое из этих имен.

Пусть еще одно имя для этой ячейки будет существенно отличаться от уже известных формул Эксель.

Тогда имя-дублер будет der — это будет сокращение от латинского слова derivari, то есть кий.

Если до этого мы содержимое ячейки A1 неофициально называли «звездочкой», то сейчас мы назовем — тоже неофициально — содержимое ячейки A3 «кием». Еще одно неофициальное имя для содержимого этой же ячейки будет «минус». Ведь имя «мин» — это сокращение именно от слова «минус».

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

Приведем пример. Мы уже недавно показывали рисунок 2.35. Там был фрагмент квадрата кандидатов.

Продублируем этот рисунок, чтобы упростить поиск нужной информации. Дадим дубликату этого рисунка номер 2.37:

Ячейки W12, X12 и Y12 Эксель (там находятся два подряд знака минус) означают, что для ячейки Г4 основного судоку цифры 1, 2 и 3 не могут быть кандидатами. Если присмотреться еще внимательней к тем клеткам Эксель, которые отведены для ячейки Г4 судоку, то станет понятно: у этой ячейки всего 2 кандидата, это цифры 4 и 6. Все остальные цифры кандидатами для этой ячейки судоку не могут быть, поэтому вместо них мы видим «минусы», они же — «кии».

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

Теперь перейдем к ячейке A4.

В ней должен быть всего один символ: знак вопроса.

Присвоим этой ячейке первое имя: воп. Сокращение от русского слова «вопрос».

Второе имя этой ячейки тоже будет содержать только три буквы, и это будут буквы латинского алфавита. Имя будет qua, сокращение от слова «quaestio», то есть «вопрос».

Далее следующая ячейка, A5. Содержимое этой ячейки: две буквы, русское слово да. Первое имя ячейки совпадет с содержимым этой ячейки, второе имя ячейки sic. Это слово «да», но на латинском языке.

Затем ячейка A6. Содержимое ячейки: слово нет, первое имя совпадает с содержимым ячейки. Второе имя: non. Это латинское слово «нет».

На этом пока завершим присвоение имен и заполнение ячеек на листе под названием Имена. Вот что должно получиться на этом листе (рисунок 2.38):

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

Можно изменить содержимое ячеек A1 и A3. Например, в ячейку A1 можно поместить тот символ, что максимально похож на звездочку. Это знак умножения. Его можно ввести либо с клавиатуры, либо с помощью формулы:

=СИМВОЛ (42)

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

=СИМВОЛ (45)

После этого тот центральный фрагмент квадрата с кандидатами для основного судоку, который расположен на листе Основное_судоку (мы уже несколько раз показывали этот фрагмент), будет выглядеть так (рисунок 2.39):

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

Поэтому на данном этапе составления судоку мы не можем увидеть квадрат с кандидатами для судоку (или его фрагменты) именно в таком виде, как они показаны на рисунках 2.35, 2.37 и 2.39. Просто мы показываем эти фрагменты такими, какими они должны быть после того, как мы заполним все формулы для квадрата с кандидатами для судоку. Просто показываем, что на листе Эксель под названием Имена мы уже создали основные элементы, основные символы, которые нам вскоре пригодятся именно при составлении квадрата с кандидатами.

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

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

На этом этапе можно завершить работу с листом Имена и перейти (вернуться) к листу под названием Основное_судоку.

2.5. Проверка данных

В Эксель имеется одна очень интересная опция: проверка данных. Мы часто будем применять эту опцию для решения судоку.

Чтобы воспользоваться этой опцией, нужно выбрать вкладку Данные, группу Работа с данными, кнопку Проверка данных. Напомню, что название любой кнопки (вместе с основными подсказками к работе с той опцией, которой соответствует эта кнопка) можно узнать, если провести «мышкой» по этой кнопке и остановить след от «мышки» на этой кнопке. На рисунке 2.40 показано, как можно найти кнопку «Проверка данных».

Итак, на рисунке 2.40 наглядно показано, как можно найти кнопку Проверка данных. Кстати, на этом рисунке еще раз изображен фрагмент квадрата с кандидатами для судоку.

А теперь продолжим говорить про проверку данных.

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

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

После нажатия на кнопку «Проверка данных» мы увидим форму, которую нужно заполнить следующим образом (рисунок 2.41):

Для других ситуаций можно настроить и другие параметры, задать другие условия проверки. Мы выбрали тип данных «целое число», при этом задали минимум и максимум. В нашей конкретной ситуации минимум — это число 1, максимум — число 9, потому что именно из таких чисел состоят все ячейки судоку.

Целое число — это только один из вариантов, который можно выбрать в качестве условий проверки. Мы определили для этих целых чисел минимум и максимум, но там возможны и другие варианты выбора (рисунок 2.42):

Итак, мы только что выбрали вариант «между», но там возможны также варианты «вне», «равно», «не равно», «больше», «меньше», «больше или равно», «меньше или равно». Это были возможные варианты для типа данных «целые числа».

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

Таким образом, мы заполнили только вкладку «Параметры»; осталось заполнить вкладки «Сообщения для ввода» и «Сообщение об ошибке». Эти пункты не обязательны к заполнению, можно их не заполнять. Даже если эти вкладки не заполнять совсем, все равно Эксель при попытке ввода данных, не соответствующих поставленным критериям, выдаст сообщение об ошибке.

2.6. Условное форматирование для повторов

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

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

1. Начать запись макроса.

Новый макрос назовем Формат_повторы_строки.

2. Выделить первую (верхнюю) строку судоку.

3. Создать правило условного форматирования для выделенной области.

Кстати, кнопку для создания правил условного форматирования можно найти так: вкладка Главная, группа Стили, кнопка Условное форматирование. Среди нескольких команд, которые появятся при нажатии на эту кнопку, нас больше всего будет интересовать команда «Создать правило»

Итак, создадим правило под пунктом «Форматировать только уникальные или повторяющиеся значения». По умолчанию выбираются именно повторяющиеся значения, в этом списке менять ничего не надо. Нужно просто применить тот формат, который мы будем использовать для повторяющихся значений.

Далее нажать на кнопку «Формат…», и уже можно приступать к созданию нужного формата.

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

После того, как мы выбрали все нужные форматы, можно нажать на кнопку OK.

4. Выделить вторую строку судоку.

5. Придать второй строке судоку условный формат. Это можно сделать так же, как мы уже делали для первой строки судоку. Нужно создать еще одно правило условного форматирования. Все действия полностью аналогичны тем, которые были в пункте 3 и касались первой ячейки судоку.

6. Повторить пункты 4 и 5 несколько раз для всех остальных строк судоку (выделить строку, затем присвоить ей нужный условный формат).

7. Завершить запись макроса.

Изменить этот макрос, добавив в него те строки, которые придадут ему ускорение.

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

↓ ↓ ↓ ↓

Sub Заменить_неправильные_пустые ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 3 To 11

For j = 3 To 11

k = Cells (i, j).Value

emp = ThisWorkbook.Names(″vac″).RefersToRange.Value

If k = emp Or k = 0 Then

Cells (i, j) = Empty

End If

Next j

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса в редакторе VBA покажем на рисунке 2.43.

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

Одна из самых интересных строчек этого макроса:

↓ ↓ ↓ ↓

emp = ThisWorkbook.Names(″vac″).RefersToRange.Value

↑ ↑ ↑ ↑

В этой строчке макроса говорится о том, как можно в макросе работать с теми именами, которые есть в Эксель. В нашем конкретном случае имя «vac» мы уже ранее внедрили в наш файл Эксель, там содержится пустота с секретиками. А второе имя — emp — это имя той же пустоты с секретиками, но это имя переменной не на листе Эксель, а в самом макросе.

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

Кстати говоря, этот самый макрос (имеется в виду макрос под именем «Заменить_неправильные_пустые») можно еще немного улучшить.

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

↓ ↓ ↓ ↓

Sub Заменить_неправильные_пустые ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 3 To 11

For j = 3 To 11

k = Cells (i, j).Value

emp = ThisWorkbook.Names(″vac″).RefersToRange.Value

If k = emp Or k = 0 Then

Cells (i, j) = Empty

End If

If Len (k)> 1 Or IsNumeric (k) = False Then

Cells (i, j) = Empty

End If

Next j

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

На рисунке 2.44 покажем скриншот этого макроса:

Откроем один секрет. Можно не вводить дополнительную переменную (emp). Тогда вместо строки

↓ ↓ ↓ ↓

If k = emp Or k = 0 Then

↑ ↑ ↑ ↑

мы введем другую строку:

↓ ↓ ↓ ↓

If k = [vac] Or k = 0 Then

↑ ↑ ↑ ↑

Тут квадратные скобки будут означать то, что имя внутри этих скобок уже есть в основном файле, в диспетчере имен. Главное в том, что имя vac присвоено в Эксель только одной ячейке, а не диапазону ячеек, а потому возможно значение этой ячейки использовать в какой-нибудь формуле, так как у одной ячейки может быть содержание (значение, наполнение, то есть текст, число или что-то другое, что может находиться внутри этой ячейки, тогда именно имя этой ячейки, заключенное в квадратные скобки, и будет означать значение этой ячейки). В принципе, можно было бы добавить после квадратных скобок точку, а затем слово Value (то есть значение), но при отсутствии этой точки и любых операторов после этой точки подразумевается «по умолчанию», что тут идет речь именно о значении.

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

После создания всех трех макросов можно создать четвертый макрос, и этот макрос будет просто запускать по очереди все эти три макроса, о которых мы только что говорили. Этот макрос можно назвать Формат_повторы_судоку.

Вот каким должен быть текст этого макроса:

↓ ↓ ↓ ↓

Sub Формат_повторы_все ()

Application.Run ″Судоку_2020.xlsm! Формат_повторы_строки″

Application.Run ″Судоку_2020.xlsm! Формат_повторы_столбцы″

Application.Run ″Судоку_2020.xlsm! Формат_повторы_блоки″

End Sub

↑ ↑ ↑ ↑

На рисунке 2.45 покажем скриншот этого макроса:

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

Поскольку мы уже недавно говорили про тот макрос, который заменяет неправильные пустые ячейки, можно этот макрос добавить к макросу Формат_повторы_все. Тогда новый (окончательный) вариант этого макроса примет следующий вид:

↓ ↓ ↓ ↓

Sub Формат_повторы_все ()

Application.Run ″Судоку_2020.xlsm! Формат_повторы_строки″

Application.Run ″Судоку_2020.xlsm! Формат_повторы_столбцы″

Application.Run ″Судоку_2020.xlsm! Формат_повторы_блоки″

Application.Run ″Судоку_2020.xlsm!
Заменить_неправильные_пустые″

End Sub

↑ ↑ ↑ ↑

При копировании прошу обратить внимание: здесь есть одна строка макроса, которая не уместилась в одну строку книги. В макросе это только одна программная строка.

Скриншот этого макроса приведем на рисунке 2.46.

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

Хотелось бы привести пример. Вот вроде бы нормальное судоку, где на первый взгляд есть только цифры и пустые ячейки. Мы уже несколько раз показывали это судоку, называли его «Судоку №1» (рисунок 2.47):

Но если мы выполним те макросы, которые придадут особый формат повторам в каждой области судоку, то мы получим не совсем красивую картину (рисунок 2.48):

Тут выделены специальным форматом (красный фон, белый текст, зачеркивание) такие ячейки, которые содержат одинаковую информацию. Может быть, там находятся один или два пробела, а может быть, там просто пустые ячейки «с секретом», о которых мы говорили ранее. В любом случае, эти ячейки нужно привести в порядок, именно это и сделает макрос под названием Заменить_неправильные_пустые, о котором мы говорили раньше.

На этом завершаются главные настройки для квадрата, в котором будет располагаться основное судоку.

2.6.1. Еще лучше и проще

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

Разобьем эту задачу на несколько этапов:

Этап 1. Условное форматирование повторов в строках судоку.

Этот этап можно решить разными способами (можно выбрать любой, какой больше всего нравится):

Вариант 1.1. С помощью субпрограммы (подпрограммы). Этот вариант существенно отличается от того, который был приведен ранее (в подглавке 2.6).

Здесь речь идет о такой части программы, переход к которой осуществляется оператором GoSub. Обычно подпрограммы завершает оператор Return, он возвращает программу к тому месту, откуда был осуществлен переход к подпрограмме.

В нашем конкретном случае нужно поступить следующим образом:

◊ начать запись макроса. Новому макросу присвоим имя: «УФ_повторы_строки». Здесь УФ — это сокращение от слов «условный формат».

◊ выделить первую (верхнюю) строку судоку (поскольку ей уже присвоено имя, можно просто ввести это имя в поле имени);

◊ зайти в условное форматирование, присвоить выделенному фрагменту все нужные форматы. Напомню, в нашем конкретном случае речь идет о красном фоне ячейки, белом цвете шрифта, а также о применении зачеркивания текста в ячейке;

◊ остановить запись макроса;

◊ войти в новый макрос, изменить его текст. Основные направления изменения текста макроса следующие:

> отделить ту часть макроса, которая отвечает за присвоение условного формата, выделить ее в подпрограмму (например, в начале подпрограммы можно добавить ремарку о начале подпрограммы), добавить в конце этой подпрограммы оператор Return;

> добавить в программу цикл For...Next, причем внутри цикла нужно будет выделять каждую строку судоку по очереди и тут же добавить переход к подпрограмме. Обратим внимание на то, что мы уже недавно составляли похожий макрос, когда выделяли внутри цикла For...Next каждую строку судоку, чтобы присвоить имена каждой строке судоку;

> отделить основной текст макроса от подпрограммы оператором Exit Sub (покинуть макрос, выйти из макроса, завершить макрос). Это нужно сделать для того, чтобы избежать того лишнего запуска подпрограммы, к которому нет и не должно быть оператора GoSub здесь речь идет о том, что после завершения всего цикла For...Next не должно быть лишних переходов к подпрограмме, все эти переходы будут осуществлены исключительно внутри цикла For...Next. При этом, если мы решили применить ко всей этой программе ускорение, о котором говорилось ранее, то отмену ускорения нужно будет осуществить непосредственно перед оператором Exit Sub, то есть перед выходом из программы (из макроса). В этом случае весь текст макроса будет следующим:

↓ ↓ ↓ ↓

Sub УФ_повтор_строки ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 3 To 11

Range (Cells (i, 3), Cells (i, 11)).Select

GoSub 50

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

Exit Sub

50 Rem начало субпрограммы

Selection.FormatConditions.AddUniqueValues

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

Selection.FormatConditions (1).DupeUnique = xlDuplicate

With Selection.FormatConditions(1).Font

.Strikethrough = True

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 255

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Return

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса покажем на рисунке 2.49.

Вариант 1.2. С помощью отдельного макроса вместо подпрограммы.

Есть и еще один вариант решения этой задачи. Те самые строки нашего макроса, что мы в предыдущем варианте превращали в подпрограмму (субпрограмму), можно объединить не в подпрограмму, а в отдельный макрос, и тогда вместо многократного обращения к субпрограмме мы будем иметь дело к запуску отдельного макроса, причем этот запуск также нужно будет осуществлять несколько раз.

В этом случае основной макрос будет таким:

↓ ↓ ↓ ↓

Sub УФ_повтор_строки ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 3 To 11

Range (Cells (i, 3), Cells (i, 11)).Select

Application.Run ″Судоку_2020.xlsm! УФ_повторы″

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

Скриншот — на рисунке 2.50.

Итак, в данном макросе уже нет субпрограммы, но вместо нее есть обращение к отдельному макросу, который, в принципе, содержит те же строки, что раньше были в субпрограмме. Этот вариант намного более удобный, чем предыдущий, поскольку в данном варианте тот макрос, который мы здесь назвали УФ_повторы, мы в будущем можем использовать и в других макросах тоже. Например, в том макросе, который будет создавать условное форматирование для повторов в столбцах и блоках судоку.

Кстати, а вот и текст того самого макроса, который заменил субпрограмму:

↓ ↓ ↓ ↓

Sub УФ_повторы ()

Selection.FormatConditions.AddUniqueValues

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

Selection.FormatConditions (1).DupeUnique = xlDuplicate

With Selection.FormatConditions(1).Font

.Strikethrough = True

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 255

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса показан на рисунке 2.51.

Лично мне (автору этой книги) больше нравится последний вариант, которому мы присвоили номер 1.2. Но еще один вариант (под условным номером 1.1) содержится в этой книге только для того, чтобы показать, что у любой задачи может существовать несколько вариантов решения. Каждый выбирает для себя, какой именно вариант ему больше всего нравится или подходит.

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

Здесь мы четко видим, что в верхней строке судоку расположены две девятки. Эти две девятки выделены специальным форматом. Двух девяток в одной строке судоку быть не должно (как и не должно быть в одной строке судоку двух любых одинаковых цифр), поэтому они обе выделены специальным форматом с помощью условного форматирования. Как минимум, одна из этих девяток будет лишней. Обычно в самом начале известно несколько цифр судоку. Если одна из тех цифр, что на каком-то этапе решения судоку оказалась в числе повторяющихся в одной строке, была среди тех самых цифр, что были известны в самом начале, то именно эту цифру и надо оставить в судоку. В той ситуации, что изображена на рисунке 2.52, нужно оставить ту девятку, которая находится в ячейке А4 судоку. Кстати, у нас это судоку встречалось уже несколько раз, мы его назвали «судоку номер один», и один из рисунков, где можно встретить это судоку, можно найти на рисунке 1.1. Там находится первоначальный вариант этого судоку, то есть тот вариант, который публикуется в электронном или печатном виде для отгадывания. Там четко видно, что в ячейке А4 судоку есть девятка. А это значит, что лишняя девятка на рисунке 2.52 — это та, что находится в ячейке А2 судоку.

Этап 2. Условное форматирование повторов в столбцах судоку.

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

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

Вариант 2.1. С помощью подпрограммы.

↓ ↓ ↓ ↓

Sub УФ_повтор_столбцы ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For j = 3 To 11

Range (Cells (3, j), Cells (11, j)).Select

GoSub 50

Next j

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

Exit Sub

50 Rem начало субпрограммы

Selection.FormatConditions.AddUniqueValues

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

Selection.FormatConditions (1).DupeUnique = xlDuplicate

With Selection.FormatConditions(1).Font

.Strikethrough = True

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 255

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Return

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса на рисунке 2.53.

Вариант 2.2. С помощью отдельного макроса вместо подпрограммы.

В этом случае макрос будет следующим:

↓ ↓ ↓ ↓

Sub УФ_повтор_столбцы ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For j = 3 To 11

Range (Cells (3, j), Cells (11, j)).Select

Application.Run ″Судоку_2020.xlsm! УФ_повторы″

Next j

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса на рисунке 2.54.

Здесь тоже есть запуск макроса под названием «УФ_повторы». Этот самый макрос, который мы уже рассматривали, он отвечает именно за создание того или иного условного формата (то есть задание нужного фона ячеек, шрифта текста и т.д.). Если в предыдущем случае речь шла про условный формат повторов в строках, то сейчас уже мы задаем условный формат для повторов в столбцах.

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

Вот результат действия этого макроса (рисунок 2.24):

Тут две единицы встретились в одном столбце. И они обе выделены с помощью условного формата.

А вот и еще один пример: тут одновременно две двойки и в одной строке, и в одном столбце (рисунок 2.56):

Конечно же, тут «лишняя» двойка — это та, которая находится в ячейке Г4 судоку.

Этап 3. Условное форматирование повторов в блоках судоку.

Здесь все полностью аналогично тому, что было проделано недавно для строк и столбцов судоку. Опять у нас есть два основных варианта решения этой проблемы: подпрограмма или отдельный макрос.

Вариант 3.1. С помощью подпрограммы:

↓ ↓ ↓ ↓

Sub УФ_повтор_блоки ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 3 To 9 Step 3

For j = 3 To 9 Step 3

Range (Cells (i, j), Cells (i +2, j + 2)).Select

GoSub 50

Next j

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

Exit Sub

50 Rem начало субпрограммы

Selection.FormatConditions.AddUniqueValues

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

Selection.FormatConditions (1).DupeUnique = xlDuplicate

With Selection.FormatConditions(1).Font

.Strikethrough = True

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 255

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Return

End Sub

↑ ↑ ↑ ↑

На рисунке 2.57 изображен скриншот этого макроса.

Вариант 3.2. С помощью отдельного макроса. В этом случае текст макроса будет следующим:

↓ ↓ ↓ ↓

Sub УФ_повтор_блоки ()

Application.Run ″Судоку_2020.xlsm! Ускорение_включить″

For i = 3 To 9 Step 3

For j = 3 To 9 Step 3

Range (Cells (i, j), Cells (i +2, j + 2)).Select

Application.Run ″Судоку_2020.xlsm! УФ_повторы″

Next j

Next i

Application.Run ″Судоку_2020.xlsm! Ускорение_выключить″

End Sub

↑ ↑ ↑ ↑

На рисунке 2.58 — скриншот этого макроса.

Итак, макрос под названием УФ_повторы мы уже несколько раз встречали, это тот самый макрос. Разница только в тех строчках, что отвечают за выделение каждого блока (квадратика) судоку.

А вот на рисунке 2.59 есть пример того, как «лишняя» цифра есть одновременно и в строке, и в столбце, и в блоке («квадратике»).

Конечно же, здесь «лишняя» цифра только одна. Это четверка в ячейке А2 судоку. Если удалить эту четверку, тогда оставшиеся четверки не будут создавать повторы в строке, столбце или блоке судоку. Удаление же любой другой четверки из того перечня, что выделены особым форматом на рисунке 2.59, по-прежнему оставит ошибки в судоку.

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

Таким образом, окончательная версия макроса, который должен задавать специальный условный формат для повторов в каждой области судоку, будет следующей:

↓ ↓ ↓ ↓

Sub УФ_повторы_ОС_все ()

Application.Run ″Судоку_2020.xlsm! УФ_повтор_строки″

Application.Run ″Судоку_2020.xlsm! УФ_повтор_столбцы″

Application.Run ″Судоку_2020.xlsm! УФ_повтор_блоки″

Application.Run _
″Судоку_2020.xlsm! Заменить_неправильные_пустые″

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса покажем на рисунке 2.60.

Комментарии: «Заменить_неправильные_пустые» — это название того макроса, который уберет лишние ячейки, в том числе и те, что кажутся пустыми. В названии макроса «ОС» — это сокращение от словосочетаний «основное судоку». Можно в макрос «УФ_повторы_ОС_все» добавить еще одну строку (в самый конец макроса), которая выделила бы ячейку B2 Эксель. Это можно сделать для того, чтобы после завершения этого макроса у нас не был бы выделен девятый блок судоку. Тогда строчка макроса перед «End Sub» будет следующей:

↓ ↓ ↓ ↓

Range(″B2″).Select

↑ ↑ ↑ ↑

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

2.7. Время выполнения макросов

Здесь будет небольшое отступление от основной темы (то есть от темы судоку). Но эта тема касается и Эксель, и макросов, и подсчета времени выполнения макроса, и увеличения скорости выполнения макросов.

Создадим другой файл Эксель.

Назовем его так: «Про_ускорение_макросов». Этот файл тоже создадим таким образом, чтобы в нем была поддержка макросов.

Хотя, на самом деле, тут будет идти речь не столько про ускорение макросов, сколько про ускорение Эксель в целом. Естественно, если ускоряется работа Эксель, то ускоряется и работа макросов.

Итак, в том файле, который мы только что создали, создадим всего один макрос. Этот макрос будет создавать таблицу умножение размером 1000 на 1000. Это значит, что всего в пределах этого макроса нужно будет выполнить один миллион вычислений.

Вначале создадим этот макрос таким образом, чтобы не было никаких ускорений вообще.

Вот текст этого макроса:

↓ ↓ ↓ ↓

Sub Умножение ()

For i = 1 To 1000

For j = 1 To 1000

Cells (i, j).Select

ActiveCell.Value = i * j

Next j

Next i

End Sub

↑ ↑ ↑ ↑

На рисунке 2.61 покажем скриншот этого макроса.

Затем добавим к этому макросу такие строки, которые позволили бы вычислять время выполнения макроса (в секундах).

Есть в VBA такой оператор: Timer. Он вычисляет количество секунд, что прошло от начала суток. Например, в какой-нибудь макрос можно добавить строку (обычно она добавляется в самом начале макроса):

↓ ↓ ↓ ↓

t=Timer ()

↑ ↑ ↑ ↑

В результате мы просто присвоим переменной t значение оператора Timer. Как уже говорили ранее, Timer — это время в секундах, прошедшее с начала суток. Мы еще нигде не отображаем это значение, но его отображать пока не нужно. Все дело в том, что нам надо отдельно зафиксировать время, которое определяется оператором Timer, в начале выполнения макроса, но также отдельно зафиксировать тот же параметр, но на время окончания выполнения макроса. Тогда разницей между этими двумя параметрами и будет означать то самое время выполнения макроса, которое мы хотим найти.

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

↓ ↓ ↓ ↓

t=Timer () -t

↑ ↑ ↑ ↑

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

А теперь продолжим работу с макросом, который создает таблицу умножения размером 1000 на 1000. На первом этапе создадим этот макрос таким, что он будет работать совсем без ускорения:

↓ ↓ ↓ ↓

Sub Умножение ()

t = Timer ()

For i = 1 To 1000

For j = 1 To 1000

Cells (i, j).Select

ActiveCell.Value = i * j

Next j

Next i

t = Timer () — t

MsgBox (t)

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса покажем на рисунке 2.62.

Если мы выполним этот макрос, то после завершения этого макроса мы получим сообщение в MsgBox (рисунок 2.63):

Здесь видно, что время выполнения макроса превышает 2200 секунд. Это почти 40 минут.

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

↓ ↓ ↓ ↓

Sub Умножение ()

t = Timer ()

′Отключаем обновление экрана

Application.ScreenUpdating = False

′Отключаем автоматический пересчет формул

Application.Calculation = xlCalculationManual

′Отключаем отслеживание событий

Application. EnableEvents = False

′Отключаем разбивку на печатные страницы

ActiveWorkbook.ActiveSheet. DisplayPageBreaks = False

For i = 1 To 1000

For j = 1 To 1000

Cells (i, j).Select

ActiveCell.Value = i * j

Next j

Next i

′Возвращаем обновление экрана

Application.ScreenUpdating = True

′Возвращаем автоматический пересчет формул

Application.Calculation = xlCalculationAutomatic

′Включаем отслеживание событий

Application. EnableEvents = True

t = Timer () — t

MsgBox (t)

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса покажем на рисунке 2.64.

Теперь снова запустим макрос и получим другое значение времени выполнения макроса (рисунок 2.65):

Как видно, время выполнения макроса сократилось больше, чем в 60 раз!

А теперь проведем второй эксперимент. Применим к исходному макросу другой тип ускорения. Мы на этот раз будем использовать не «отключение пересчета формул», а ликвидацию выделения или активации ячеек.

Вот каким будет текст макроса в этом случае:

↓ ↓ ↓ ↓

Sub Умножение ()

t = Timer ()

For i = 1 To 1000

For j = 1 To 1000

Cells (i, j) = i * j

Next j

Next i

t = Timer () — t

MsgBox (t)

End Sub

↑ ↑ ↑ ↑

На рисунке 2.66 — скриншот этого макроса.

Мы здесь даже не добавляем после фразы Cells (i,j) добавку «.Value» (значение), все равно и так имеется в виду, что мы задаем для нужной ячейки именно значение, а не что-то иное.

А вот и результат выполнения этого макроса (как и прежде, мы имеем в виду не весь результат, а только время выполнения макроса). Покажем его на рисунке 2.67.

У нас время выполнения макроса сократилось еще больше — почти в 120 раз!

А теперь мы можем объединить оба варианта ускорения.

Вот каким будет текст макроса:

↓ ↓ ↓ ↓

Sub Умножение ()

t = Timer ()

′Отключаем обновление экрана

Application.ScreenUpdating = False

′Отключаем автоматический пересчет формул

Application.Calculation = xlCalculationManual

′Отключаем отслеживание событий

Application. EnableEvents = False

′Отключаем разбивку на печатные страницы

ActiveWorkbook.ActiveSheet. DisplayPageBreaks = False

For i = 1 To 1000

For j = 1 To 1000

Cells (i, j) = i * j

Next j

Next i

′Возвращаем обновление экрана

Application.ScreenUpdating = True

′Возвращаем автоматический пересчет формул

Application.Calculation = xlCalculationAutomatic

′Включаем отслеживание событий

Application. EnableEvents = True

t = Timer () — t

MsgBox (t)

End Sub

↑ ↑ ↑ ↑

Скриншот этого макроса покажем на рисунке 2.68.

Запустим этот макрос и посмотрим на время его выполнения (рисунок 2.69):

Итак, как видно из этого рисунка, применение двух способов ускорения макросов одновременно позволяет сократить время выполнения макроса примерно в 200 раз!

Конечно же, в основном почти простые макросы выполняются достаточно быстро, и для многих простых (маленьких) макросов время выполнения будет равно или почти равно нулю. Под простыми мы здесь имеем в виду такие макросы, которые не связаны с обработкой большого количества информации. Если есть желание или необходимость сравнить время выполнения двух разных очень быстрых макросов, то можно использовать MsgBox таким образом, чтобы выводились не секунды, а миллисекунды (одна секунда — это одна тысяча миллисекунд). Тогда строку Msgbox (t) нужно будет заменить на Msgbox (1000 * t), и это будет время в миллисекундах.

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

Иногда это число может быть представлено в экспоненциальном виде. Посмотрим, например, на рисунок 2.70:

А если мы исправим макрос, попросим показывать не секунды, а миллисекунды, то можем получить число 13,67188. Но можем получить и другое число при втором (повторном) запуске этого же макроса. Часто бывает так, что один и тот же макрос во время разных запусков выполняется разное количество секунд.

Конечно же, время выполнения макроса можно отображать не только в MsgBox, но и в какой-либо ячейке Эксель. В этом случае нужно будет строчку макроса

↓ ↓ ↓ ↓

MsgBox (t)

↑ ↑ ↑ ↑

заменить на другую, например:

↓ ↓ ↓ ↓

Cells (1001, 1002) = t

↑ ↑ ↑ ↑

В этом случае время выполнения макроса будет отображено в ячейке ALN1001 Эксель, потому что именно у этой ячейки номер строки 1001, а номер столбца 1002.

Глава 3. Основной квадрат с кандидатами

3.1. Создание квадрата с кандидатами

А сейчас настала пора добраться до составления квадрата с кандидатами. Можно придумать для этого квадрата специальное название: КСК. Это будет так и расшифровываться: квадрат с кандидатами.

Во-первых, выделим среди всего листа Эксель под именем Основное_судоку те клетки Эксель, где у нас будут находиться кандидаты для судоку.

Покажем левую верхнюю часть нашего КСК (вместе с заголовками строк и столбцов самого квадрата кандидатов, а также с заголовками строк и столбцов Эксель), рисунок 3.1:

Итак, рисунок 3.1. показывает, где в Эксель должны располагаться кандидаты для первого (левого верхнего) блока основного судоку.

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

Первым делом надо заполнить заголовки строк и столбцов КСК. На рисунке 3.1 заголовки строк расположены в столбце M Эксель. Поскольку на рисунке расположен не весь квадрат кандидатов, а только его часть, то напомню о том, что буквы в заголовках строк квадрата с кандидатами будут такими же, что мы уже видели в основном судоку, просто каждая буква встречается по три раза. Во введении этой книги мы достаточно подробно рассказывали про буквы, которые используются в заголовках основного судоку, в КСК будем использовать те же буквы, что мы уже использовали в основном судоку, но каждая из них будет использоваться три раза подряд.

Аналогичная ситуация и с цифрами, все цифры от единицы до девяти присутствуют в заголовках столбцов КСК по три раза. Эти цифры-заголовки находятся в строке №2 Эксель.

Покажем те фрагменты пустого КСК, что примыкают к заголовкам строк и столбцов в рисунке 3.2. Честно говоря, это не просто фрагменты квадрата с кандидатами для пустого судоку, а пока просто рисунок, заготовка для будущего квадрата с кандидатами, макет. Здесь пока нет конкретных формул для самого квадрата с кандидатами, пока нет вообще никаких формул, мы их добавим позже. Этот рисунок, как и предыдущий, показан только для того, чтобы было четко понятно, в каких именно строках и столбцах Эксель будет расположен сам КСК, как будут выглядеть заголовки его строк и столбцов, где эти заголовки будут расположены. Итак, рисунок 3.2:

Рисунок 3.2 показывает, где будут находиться кандидаты для второго и третьего блоков основного судоку.

Здесь скрыты многие столбцы Эксель, но это скрытие произошло исключительно для удобства подачи информации.

Буквы А, Б, В (в столбце M Эксель) — это, в принципе, те же самые буквы, что были в основном судоку, в заголовках строк основного судоку. А цифры 4, 5, 6, …, 9 (строка 2 Эксель) — это те же самые цифры, что были в заголовках столбцов основного судоку. Просто в КСК в три раза больше и строк, и столбцов, чем в основном судоку. А поэтому каждой ячейке основного судоку соответствует целых 9 ячеек квадрата с кандидатами. Это и не удивительно, ведь каждой клетке основного судоку соответствует 9 клеток в квадрате с кандидатами, например, в КСК есть отдельная ячейка для кандидата-единицы, отдельная — для кандидата-двойки, и так далее.

Кстати, вот как будет выглядеть тот же фрагмент КСК, если судоку будет заполнено полностью (рисунок 3.3):

Так, например, ячейки W3…Y5 Эксель — это 9 ячеек, но все они — возможные кандидаты для ячейки А4 основного судоку.

Если в данной конкретной ситуации в этих ячейках находятся звездочки, то это значит, что у ячейки А4 основного судоку нет кандидатов вообще (как, кстати, их и нет для других ячеек судоку, поскольку на этом рисунке показан фрагмент квадрата с кандидатами для того судоку, которое уже заполнено полностью, целиком). В ячейке M2 Эксель находится 0, это значит, что в квадрате с кандидатами есть именно 0 кандидатов.

А аналогичный фрагмент КСК для совершенно пустого судоку, в котором пока нет ни одной цифры, будет выглядеть так (рисунок 3.4).