Электронные таблицы и судоку

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.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 покажем скриншот этого макроса:

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

Рисунок 2.45.

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

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

↓ ↓ ↓ ↓

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

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

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

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

Application.Run ″Судоку_2020.xlsm!

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

End Sub

↑ ↑ ↑ ↑

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

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

Рисунок 2.46.

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

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

Рисунок 2.47.

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

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

Рисунок 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 задавали границы для каждой строки судоку, то теперь нужно задать границы каждому столбцу судоку. Все остальное будет таким же, как и было.

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