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

Вариант 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

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.

Конец ознакомительного фрагмента.

Текст предоставлен ООО «Литрес».

Прочитайте эту книгу целиком, купив полную легальную версию на Литрес.

Безопасно оплатить книгу можно банковской картой Visa, MasterCard, Maestro, со счета мобильного телефона, с платежного терминала, в салоне МТС или Связной, через PayPal, WebMoney, Яндекс.Деньги, QIWI Кошелек, бонусными картами или другим удобным Вам способом.