Excel и судоку

2.4. Имена на другом листе Эксель

Нам нужно создать второй лист на основном файле Эксель. Переименуем этот второй лист Эксель, присвоим ему новое имя (новый заголовок): Имена.

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

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

Ячейке A1 этого листа присвоим имя: ast. Это первые три буквы от латинского слова «Asterisco», что означает «звездочка». Поместим в эту ячейку тот самый символ, о котором мы уже говорили ранее, он очень похож на вид зонтика сверху). Этот символ можно найти во вкладке «Вставка», в группе «Символы», там есть кнопка «Символ». Я понимаю, что этот символ не очень похож на звездочку, но в любом случае (рано или поздно) этот символ, который находится в ячейке A1 листа Эксель под названием Имена, можно будет заменить на какой-то другой, какой больше нравится. Можно заменить и на «звездочку» (*). Главное, чтобы это был просто символ, то есть не буква и не цифра. Ну и желательно, чтобы этот символ не был очень похож на какую-нибудь букву или цифру. Этот символ нам понадобится чуть позже, когда мы будем заполнять квадрат, в котором будут расположены все кандидаты для судоку. В любом случае, мы условно будем называть этот символ «звездочкой», даже если он совсем не похож на «звездочку». Теперь расскажем про использование этого символа. Если какая-то ячейка основного судоку, например, Д2 основного судоку, будет заполнена цифрой (то есть будет не пустой), тогда в квадрате с кандидатами (его мы создадим позже) все 9 клеток, в которых содержатся возможные кандидаты для этой ячейки, будут состоять из этих так называемых «звездочек». Мы эту картину уже видели во введении этой книги. Но здесь приведем еще один пример с этим символом (рисунок 2.35):

Рисунок 2.35.

На этом рисунке расположен фрагмент квадрата кандидатов. Этот фрагмент показывает только кандидаты для среднего блока кандидатов, то есть для блока №5. Хотя некоторые строки и столбцы Эксель тут скрыты, это было сделано только для того, чтобы четко было видно, к каким строкам и столбцам Эксель относится информация, изображенная на рисунке. Столбец M Эксель показывает заголовки строк судоку, строка 2 Эксель показывает заголовки столбцов судоку. Как мы уже говорили, на этом рисунке мы видим кандидаты для центрального (пятого) квадратика судоку. В основном судоку в этом квадратике есть 4 цифры, это клетки Г5, Г6, Е5 и Е6 основного судоку. Для этих клеток основного судоку уже кандидаты не нужны, а потому именно те клетки квадрата кандидатов, которые отвечают за кандидаты именно этих клеток судоку, состоят только из тех символов, которые мы назвали «звездочками».

Для ячейки A1 этого листа создадим еще одно имя, оно будет дублером имени ast, Мы не будем добавлять префикс к имени ast. Просто возьмем первые три буквы русского слова «звезда», и получится совершенно новое имя: зве. Итак, одно имя содержит три латинские буквы, второе имя – три русские буквы.

Кроме того, символ из ячейки A1 мы можем называть «звездочка», это имя не будет никак отражаться официально, это не будет отражаться в диспетчере имен или где-то еще. Просто нужно иметь в виду: если в этой книге будет звучать слово «звездочка», то это значит, что речь идет о том символе или о тех символах, которые находятся в ячейке A1 листа под названием Имена. Мы в любое время можем исправить эту информацию, заменить тот символ, что там находится сейчас, на другой символ или на другие символы. Главное, чтобы было удобно. В любом случае, «звездочка» – это содержимое именно этой ячейки.

Ячейка A2 этого же листа будет пустой. Но это будет не простая пустота, а пустота «с секретиками». Там будет следующая формула:

=″″

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

Эта пустота «с секретиком», или «с секретиками», будет достаточно часто использоваться в этой книге.

Можно провести эксперимент. В ячейку D1 эксель (мы пока по-прежнему находимся на листе Имена, о возвращении на лист под названием Основное_судоку будет позже сообщено дополнительно) можно ввести формулу:

=A2+A3

Мы в результате в ячейке D1 увидим сообщение об ошибке, потому что в ячейке A2 у нас не ноль и вообще не цифра. Если же в этой формуле заменить A2 на A12, то в результате мы получим ноль. В ячейке A12 формально тоже пустота, но это уже другая пустота, без всяких «секретиков».

Можно провести еще один эксперимент.

В ячейку D2 можно ввести формулу:

=ЕЧИСЛО (A2)

Мы увидим результат: сообщение «ЛОЖЬ». Это значит, что то, что находится в ячейке A2, не является числом. Правда, если мы устроим аналогичную «проверку» ячейке A12, которая пока просто пустая и не содержит вообще никаких формул, то результат будет тем же, там тоже не число.

Поэтому можно провести еще один эксперимент.

В ячейку D3 введем формулу:

=A2+0

В результате в ячейке D3 мы получим сообщение об ошибке:

#ЗНАЧ!

Но так и должно быть, это действительно ошибка. Ведь нельзя складывать число и то, что числом не является.

Если мы в ячейке D3 изменим формулу, A2 превратим в A12, тогда результатом сложения будет 0. В этом случае формула в D3 будет следующей:

=A12+0

В принципе, теперь столбец D можно удалить совсем (или просто очистить). Мы его использовали только для того, чтобы показать несколько «секретов» той пустоты, что находится в ячейке A2.

Итак, ячейка A2 содержит пустоту с «секретом» (ну или с секретиками, как было сказано ранее). Теперь надо присвоить этой ячейке имена. Первое имя будет пус (первые три буквы от русского слова «пустота». Второе имя у этой же ячейки будет vac – первые три буквы от латинского слова «vacuum», что в переводе значит «пустота».

Теперь переходим к ячейке A3. Введем туда знак апострофа, а затем два знака минус подряд (без пробелов между этими знаками). Вот что должно получиться (покажем фрагмент листа Эксель, рисунок 2.36):

Рисунок 2.36.

Что можно сказать по этому рисунку? Текущая ячейка – это ячейка A3 Эксель. Эта ячейка текущая потому, что она со всех сторон выделена окантовкой серо-зеленого цвета. Кроме того, текущий столбец Эксель (столбец A) выделен особым форматом, он более темный, чем другие столбцы. Точно то же самое можно сказать и про строку 3 – это текущая строка, и она тоже выделена особо, чуть темнее, чем остальные строки. В поле имени мы видим min, это значит, что именно такое имя присвоено этой ячейке.

Итак, нужно присвоить этой ячейке (то есть ячейке A3 Эксель) это имя (то есть имя min). Оно является сокращением от латинского слова «minima», то есть «минимум».

Присвоим этой ячейке (ячейке A3) и второе имя: мин – сокращение от русского слова «минимум». Главное – не запутаться: в Эксель есть формула, которая содержит эти же буквы и в той же последовательности.

Это формула МИН.

Она означает минимум.

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

Хотя можно придумать этой ячейке еще одно имя: кий. Бильярдный кий, действительно, очень похож на знак минуса. Это второе имя мы присваиваем исключительно для того, чтобы не путать наше имя «мин» с формулой минимума МИН.

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

Пусть еще одно имя для этой ячейки будет существенно отличаться от уже известных формул Эксель.

Тогда имя-дублер будет der – это будет сокращение от латинского слова derivari, то есть кий.

Если до этого мы содержимое ячейки A1 неофициально называли «звездочкой», то сейчас мы назовем – тоже неофициально – содержимое ячейки A3 «кием». Еще одно неофициальное имя для содержимого этой же ячейки будет «минус». Ведь имя «мин» – это сокращение именно от слова «минус».

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

Приведем пример. Мы уже недавно показывали рисунок 2.35. Там был фрагмент квадрата кандидатов.

Продублируем этот рисунок, чтобы упростить поиск нужной информации. Дадим дубликату этого рисунка номер 2.37:

Рисунок 2.37.

Ячейки W12, X12 и Y12 Эксель (там находятся два подряд знака минус) означают, что для ячейки Г4 основного судоку цифры 1, 2 и 3 не могут быть кандидатами. Если присмотреться еще внимательней к тем клеткам Эксель, которые отведены для ячейки Г4 судоку, то станет понятно: у этой ячейки всего 2 кандидата, это цифры 4 и 6. Все остальные цифры кандидатами для этой ячейки судоку не могут быть, поэтому вместо них мы видим «минусы», они же – «кии».

Еще хочется сказать несколько слов про апостроф, который можно использовать при вводе информации в ячейку. Если в ячейку Эксель ввести сначала апостроф, а после него любую информацию, то это будет означать, что информация после апострофа будет восприниматься как текст. Это будет происходить даже в тех случаях, если после апострофа будут стоять только одни цифры. Если мы введем в ячейку Эксель апостроф, затем какой-нибудь текст, а после этого нажмем на клавишу ввода (Enter), то апостроф автоматически исчезнет из этой ячейки, но останется видимым в строке формул. Его мы можем четко увидеть на рисунке 2.36. Если бы мы сделали скриншот с экрана еще раньше, во время ввода этой информации, после ввода апострофа и двух минусов, но до нажатия на клавишу Enter, мы бы увидели этот апостроф и в ячейке, и в строке формул. Но мы вначале нажали на клавишу ввода, после чего автоматически апостроф исчез из ячейки A3, а активной ячейкой стала другая ячейка. Затем мы снова сделали ячейку A3 текущей (активной), но это сделано было только для того, чтобы показать, как при этом будет выглядеть поле имени, строка формул и сама эта ячейка. Кстати, сам апостроф так официально и называется: текстовый префикс.

Теперь перейдем к ячейке A4.

В ней должен быть всего один символ: знак вопроса.

Присвоим этой ячейке первое имя: воп. Сокращение от русского слова «вопрос».

Второе имя этой ячейки тоже будет содержать только три буквы, и это будут буквы латинского алфавита. Имя будет qua, сокращение от слова «quaestio», то есть «вопрос».

Далее следующая ячейка, A5. Содержимое этой ячейки: две буквы, русское слово да. Первое имя ячейки совпадет с содержимым этой ячейки, второе имя ячейки sic. Это слово «да», но на латинском языке.

Затем ячейка A6. Содержимое ячейки: слово нет, первое имя совпадает с содержимым ячейки. Второе имя: non. Это латинское слово «нет».

На этом пока завершим присвоение имен и заполнение ячеек на листе под названием Имена. Вот что должно получиться на этом листе (рисунок 2.38):

Рисунок 2.38.

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

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

=СИМВОЛ (42)

Можно заменить и содержимое ячейки A3, оставив вместо двух минусов только один. Это можно сделать не только с помощью ввода знака минус с клавиатуры (если нужен только один минус, то можно обойтись и без символа апострофа), но с помощью формулы:

=СИМВОЛ (45)

После этого тот центральный фрагмент квадрата с кандидатами для основного судоку, который расположен на листе Основное_судоку (мы уже несколько раз показывали этот фрагмент), будет выглядеть так (рисунок 2.39):

Рисунок 2.39.

Правда, мы еще пока не разбирали подробно тему создания квадратов для кандидатов, ее мы рассмотрим позже.

Поэтому на данном этапе составления судоку мы не можем увидеть квадрат с кандидатами для судоку (или его фрагменты) именно в таком виде, как они показаны на рисунках 2.35, 2.37 и 2.39. Просто мы показываем эти фрагменты такими, какими они должны быть после того, как мы заполним все формулы для квадрата с кандидатами для судоку. Просто показываем, что на листе Эксель под названием Имена мы уже создали основные элементы, основные символы, которые нам вскоре пригодятся именно при составлении квадрата с кандидатами.

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

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

На этом этапе можно завершить работу с листом Имена и перейти (вернуться) к листу под названием Основное_судоку.

2.5. Проверка данных

В Эксель имеется одна очень интересная опция: проверка данных. Мы часто будем применять эту опцию для решения судоку.

Чтобы воспользоваться этой опцией, нужно выбрать вкладку Данные, группу Работа с данными, кнопку Проверка данных. Напомню, что название любой кнопки (вместе с основными подсказками к работе с той опцией, которой соответствует эта кнопка) можно узнать, если провести «мышкой» по этой кнопке и остановить след от «мышки» на этой кнопке. На рисунке 2.40 показано, как можно найти кнопку «Проверка данных».

Рисунок 2.40.

Итак, на рисунке 2.40 наглядно показано, как можно найти кнопку Проверка данных. Кстати, на этом рисунке еще раз изображен фрагмент квадрата с кандидатами для судоку.

А теперь продолжим говорить про проверку данных.

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

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

После нажатия на кнопку «Проверка данных» мы увидим форму, которую нужно заполнить следующим образом (рисунок 2.41):

Рисунок 2.41.

Для других ситуаций можно настроить и другие параметры, задать другие условия проверки. Мы выбрали тип данных «целое число», при этом задали минимум и максимум. В нашей конкретной ситуации минимум – это число 1, максимум – число 9, потому что именно из таких чисел состоят все ячейки судоку.

Целое число – это только один из вариантов, который можно выбрать в качестве условий проверки. Мы определили для этих целых чисел минимум и максимум, но там возможны и другие варианты выбора (рисунок 2.42):

Рисунок 2.42.

Итак, мы только что выбрали вариант «между», но там возможны также варианты «вне», «равно», «не равно», «больше», «меньше», «больше или равно», «меньше или равно». Это были возможные варианты для типа данных «целые числа».

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

Таким образом, мы заполнили только вкладку «Параметры»; осталось заполнить вкладки «Сообщения для ввода» и «Сообщение об ошибке». Эти пункты не обязательны к заполнению, можно их не заполнять. Даже если эти вкладки не заполнять совсем, все равно Эксель при попытке ввода данных, не соответствующих поставленным критериям, выдаст сообщение об ошибке.

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 – это имя той же пустоты с секретиками, но это имя переменной не на листе Эксель, а в самом макросе.

Аналогичным образом можно работать в макросах и с любыми другими именами, которые присутствуют на разных листах Эксель.

Кстати говоря, этот самый макрос (имеется в виду макрос под именем «Заменить_неправильные_пустые») можно еще немного улучшить.

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