Excel и судоку

Excel и судоку

Андрей Евгеньевич Зайнулин

© Андрей Евгеньевич Зайнулин, 2025

ISBN 978-5-0068-3724-9

Создано в интеллектуальной издательской системе Ridero

Глава 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.1, будет часто встречаться в этой книге. Поэтому давайте договоримся так: присвоим этому судоку номер, и это судоку будет называться в этой книге «судоку №1».

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

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

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

Рисунок 1.2.

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

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

Рисунок 1.3.

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

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

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

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

Рисунок 1.6.

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

Рисунок 1.7.

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

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

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

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

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

Рисунок 1.9.

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

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

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

Рисунок 1.11.

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