Excel и судоку

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.

Рисунок 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.

Рисунок 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.

Рисунок 2.51.

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

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

Рисунок 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.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.54.

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

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

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

Рисунок 2.55.

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

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

Рисунок 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 изображен скриншот этого макроса.

Рисунок 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.58.

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

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

Рисунок 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.

Рисунок 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