Excel и судоку

Next j

Next i

End Sub

↑ ↑ ↑ ↑

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

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

Рисунок 2.62.

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

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

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

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

Рисунок 2.66.

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

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

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

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

Рисунок 2.69.

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

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

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

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

Рисунок 2.70.

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

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

↓ ↓ ↓ ↓

MsgBox (t)

↑ ↑ ↑ ↑

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

↓ ↓ ↓ ↓

Cells (1001, 1002) = t

↑ ↑ ↑ ↑

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

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

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

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

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

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

Рисунок 3.1.

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

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

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

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

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

Рисунок 3.2.

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

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

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

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

Рисунок 3.3.

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

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

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

Рисунок 3.4.

Здесь уже в ячейке M2 Эксель находится число 729, но так и должно быть: именно столько кандидатов должно быть у совсем пустого судоку размером 9 на 9. Мы получим число 729, если 81 умножим на 9. При этом 81 – это количество клеток в судоку, и у каждой клетки должно быть ровно 9 кандидатов.

А теперь покажем еще несколько ячеек квадрата с кандидатами. Это будут кандидаты для четвертого блока основного судоку (рисунок 3.5.).

Рисунок 3.5.

После этого покажем, где должен находиться фрагмент КСК для седьмого блока основного судоку (рисунок 3.6).

Рисунок 3.6.

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

Кандидаты для блоков судоку с номерами 5, 6, 8 и 9 мы тут показывать не будем, так как их местоположение и так вполне очевидно.

Затем нужно сделать КСК более красивым. После заполнения заголовков строк и столбцов этого квадрата можно приступить к форматированию клеток этого квадрата (здесь пока речь идет только о раскрашивании блоков квадрата с кандидатами в разные цвета, а также о создании внешних границ для тех маленьких квадратиков КСК, которые имеют отношение к одной и той же ячейке судоку). Хотя на рисунках 3.1, … 3.6 все эти границы уже имеются, мы здесь хотим показать, как их можно создать достаточно быстро, с помощью макроса, не выделяя каждый раз нужную совокупность ячеек вручную.

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

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

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

↓ ↓ ↓ ↓

Sub Внешние_границы ()

Range(″N3:P5″).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders (xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders (xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders (xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders (xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

Selection.Borders(xlInsideVertical).LineStyle = xlNone

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

End Sub

↑ ↑ ↑ ↑

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

Рисунок 3.7.

Затем исправим этот макрос. Это же у нас не окончательная версия макроса, а только предварительная, «черновая», ведь мы пока сделали окантовку для одного маленького квадратика, а всего таких квадратиков должно быть 81. Добавим ускорение, а также с помощью циклов For…Next сделаем так, чтобы внешние границы были у всех квадратиков внутри КСК, а не у одного.

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

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

↓ ↓ ↓ ↓

Sub Внешние_границы ()

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

a1 = Range(″N3″).Column

a2 = Range(″AL27″).Column

a3 = Range(″N3″).Row

a4 = Range(″AL27″).Row

For i = a3 To a4 Step 3

For j = a1 To a2 Step 3

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

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders (xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders (xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders (xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders (xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

Selection.Borders(xlInsideVertical).LineStyle = xlNone

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Next j

Next i

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

End Sub

↑ ↑ ↑ ↑

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

Рисунок 3.8.

Хотелось бы оставить несколько комментариев к этому макросу.

Если в предыдущей версии макроса мы выделяли только один квадратик размером 3 на 3 ячейки Эксель, то здесь мы поочередно выделяем каждый квадратик. Нужно выделить все квадратики, которые символизируют возможные кандидаты для одной ячейки судоку. Всего ячеек судоку будет 81 (9 вертикальных полосок, в каждой вертикали ровно 3 столбца Эксель, и 9 горизонтальных полосок, в каждой горизонтали ровно 3 строки Эксель). Выделяем квадратики с помощью циклов For…Next. В этих же циклах появляется оператор Step, что означает шаг. В нашем конкретном примере Step принимает значение 3, потому что левая верхняя ячейка квадратика, в котором находятся кандидаты для ячейки А2, будет ровно на 3 столбца правее, чем левая верхняя ячейка для А1. И так каждый раз: левая верхняя ячейка для одной ячейки будет либо на 3 столбца правее, либо на три строки ниже, чем аналогичная левая верхняя ячейка такого же квадратика размером 3 на 3 с кандидатами для ближайшей соседней ячейки судоку.