Excel и судоку

Здесь три строчки выделены красным цветом (я это поясняю из-за того, что мне не известно, держит ли читатель в руках черно-белый или цветной экземпляр моей книги, при этом на черно-белой версии книги не видно больших различий между красным и черным цветами). Речь идет о строках с номерами 4, 5 и 6. На самом деле «ошибка» в строке 4 (в той, что содержит символ нижнего подчеркивания) следующая: короткий минус (математический знак минуса) превратился после автоматической замены в длинное тире; эта же «ошибка» касается и строки под номером 6. А пятая строка стала «ошибочной» из-за лишней пустой строки над ней, пустые строки в листинге макроса тоже считаются строками программы. После символа подчеркивания идет продолжение той же программной строки. И это продолжение не должно быть пустой строкой (про разбивку программных строк символом подчеркивания мы поговорим чуть позже).

Вот каким должен быть этот макрос после устранения всех этих «ошибок» (рисунок 1.12):

Рисунок 1.12.

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

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

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

В процессе написания книги автор столкнулся с еще одной проблемой, которая тоже имеет отношение к автозамене текста.

Из-за автозамены могут возникнуть искажения не только в тексте макроса, но и в тексте тех формул, что обычно находятся не в макросах, а именно в ячейках Эксель. Здесь обычно автозамена добавляет лишние пробелы в самих формулах, причем в тех местах формул, где обычно не нужны пробелы. Поэтому заранее предупреждаю о том, что обычно в формулах Эксель пробелы не нужны вообще, даже для отделения знака амперсанд (&). Правда, если в формулах Эксель добавить пробелы по обе стороны от каждого знака амперсанд (&), то это большой ошибкой не будет считаться, формула от этого не будет являться ошибочной. А вот пробелы перед многими другими символами в формулах Эксель часто приводят к ошибкам, например, может возникнуть сообщение в стиле «Это не формула».

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

Макросы пишутся на языке VBA (сокращение от Visual Basic for Applications). Applications – это приложения. Под приложениями понимаются программы, входящие в MS Office. Эксель – это только одна из этих самых программ, одно из приложений. Оператор, или выражение (минимальный исполняющийся код VBA), расположенный в одной строке макроса, можно разбить на несколько экранных строк. Если в конце какой-то строки макроса содержится пробел, а за пробелом – символ нижнего подчеркивания, то это значит, что строка макроса разбита, в следующей строке макроса будет продолжение того же самого кода VBA. Так переносятся на другую строчку разные части кода одного выражения. Имеется в виду перенос на другую строчку в самом редакторе VBA. На рисунке 1.12 мы уже приводили макрос, одна из строк которого разбита с помощью символа подчеркивания, но здесь приведем еще один пример:

↓ ↓ ↓ ↓

ActiveWorkbook.Names.Add Name:=″бло_″ & k, RefersToR1C1:= _

″=Основное_судоку! R″ & i & ″C″ & j & ″:R″ & i +2 & ″C″ & j +2

↑ ↑ ↑ ↑

Как было обещано ранее, после текста с фрагментом макроса приведем скриншот (рисунок 1.13):

Рисунок 1.13.

Здесь был приведен пример всего лишь одной программной строки макроса. Она занимает две экранные строчки, две строчки на странице экрана, но это одна строка программы, потому что главный результат выполнения этой строчки прописан одним оператором на VBA (здесь главная фраза в этой длинной строчке будет Add Name, то есть: добавить имя), а смысл этой строки макроса заключается в присвоении конкретного имени одной ячейке Эксель. Именно присвоение имени – это и есть одно действие, которое выполняется этой строкой макроса. Просто так получилось, что текст этой строки макроса оказался слишком длинным, поэтому и возникла необходимость в разбивке этого текста на экранные строки. Эта строка макроса разбита на две части не только в этой книге, но и в редакторе VBA. В редакторе VBA символ нижнего подчеркивания (_) всегда означает то, что продолжение этой же программной строки будет на следующей экранной строчке. При этом сам символ нижнего подчеркивания должен быть отделен от предыдущего текста пробелом.

Но иногда бывает так, что длинные строки нельзя разделить на несколько строк в редакторе Visual Basic (точнее говоря, разделить можно, но это разделение возможно не в любом месте строки макроса). Обычно при попытке разбить программную строку в «неправильном» месте Эксель выдает сообщение об ошибке (рисунок 1.14).

Рисунок 1.14.

Если мы в редакторе VBA захотим разбить длинную строчку программы на несколько строк, но при этом забудем в конце первой строки добавить знак подчеркивания, то мы увидим другое сообщение об ошибке (рисунок 1.15):

Рисунок 1.15.

Но не всегда бывает возможность разделить одну строку кода макроса на несколько строк в редакторе Visual Basic, а количество символов в строке книги всегда ограничено. Поэтому иногда может быть и так, что одна строка в редакторе VBA может занимать несколько строк на странице книги. Такую ситуацию можно просто найти: если какая-то книжная строчка, которая является частью макроса или его фрагмента, будет начинаться не с «красной» строки, то это значит, что данная строка книги не начинает новую строку макроса (программы), а является продолжением предыдущей строки.

Просто нужно иметь в виду, что несколько книжных строк – это всего лишь одна «экранная» строка макроса, которая просто не уместилась на одной строчке страницы книги. Приведем пример:

↓ ↓ ↓ ↓

Selection.FormatConditions(Selection.FormatConditions.Count). SetFirstPriority

↑ ↑ ↑ ↑

Приведем скриншот (рисунок 1.16):

Рисунок 1.16.

Тут приведен не один макрос, а только одна его часть, одна программная строка. Если в тексте макроса книжная строка начинается не с «красной строки», то есть не имеет отступа слева, то это значит, что данная книжная строчка является продолжением предыдущей строки макроса, в редакторе VBA обе эти строки будут занимать одну строчку. В приведенном примере все именно так и происходит. Тут приведена всего лишь одна «программная» строка макроса, ее надо в редакторе VBA разместить на одной строке. Эта строка макроса разместилась на двух книжных строчках.

И еще один момент. В Эксель, как и во многих других программных продуктах, есть многоуровневое меню. Давайте сразу определимся, как будут называться основные части этого меню (рисунок 1.17):

Рисунок 1.17.

Конечно, можно применять также и просто слово «меню».

Хотелось бы добавить еще несколько слов.

Если вдруг встретится точка в конце имени ячейки или диапазона ячеек, то это значит, что данная точка – просто знак препинания, но не часть имени ячейки или диапазона ячеек.

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

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

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

1. Убедиться, что текущий язык ввода – это русский язык.

2. Скопировать нужный фрагмент, который содержит и русские, и латинские буквы.

3. Вставить нужный фрагмент в другое место.

Если во время копирования и вставки язык ввода будет другим, тогда русские буквы могут быть заменены на другие символы (в основном, на знаки вопроса).

В процессе работы над книгой я обратил внимание на еще одно проявление автозамены. Тут речь идет о пробелах. Автозамена часто добавляет ненужные пробелы, но и убирает нужные. Например, если после знака + (плюс) тут же следует пробел, а за пробелом цифра в тексте макроса, то автозамена автоматически удаляет пробел между знаком плюс и этой цифрой. Конечно, эту проблему можно решить. Например, если удалить пустую строку, расположенную сразу после строки с подобными «ошибками», то редактор VBA автоматически расставит все пробелы по правильным местам. Но на всякий случай скажу: в редакторе VBA при написании макросов знаки равно (=), плюс (+), минус (-), умножение (*), деление (/), возведение в степень (^) отделяются пробелами и слева, и справа. Если в этой книги и будут встречаться исключения из этого правила, то они будут связаны только с автозаменой издательства.

На моем канале в Rutube создан целый плейлист, где я преобразовываю макросы этой книги так, чтобы «отменить» автозамену издательства и привести эти макросы в нормальный вид. Я не буду здесь публиковать адрес плейлиста, чтобы не создавать инцидентов, связанных с законодательством о рекламе. Просто скажу, что у меня есть несколько страниц в так называемой «всемирной паутине», и в Rutube, и в дзен.

Название канала автора в рутуб: Эксель, судоку, математика…

Название канала в дзен: Эксель, судоку, артобъекты…

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

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

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

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

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

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

Глава 2. Создание и подготовка файла

2.1. Создание файла для судоку

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

Судоку_2020.xlsm

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

Здесь расширение файла (xlsm) означает то, что у нас имеется не простой файл Эксель, а файл с поддержкой макросов.

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

Итак, теперь наш файл готов.

2.2. Подготовка файла

Прежде всего надо выбрать тот лист Эксель, на котором будут находиться и основное судоку, и основной квадрат с кандидатами для судоку, и несколько вспомогательных квадратов. Их тоже придется создавать. Например, отдельный квадрат для кандидатов-единиц, отдельный квадрат для тех клеток судоку, у которых есть ровно один кандидат, и так далее. Этому листу Эксель нужно присвоить имя: Основное_судоку. Хоть иногда можно встретить информацию о том, что слово «судоку» – это слово мужского рода, эта информация неверная. Слово «судоку» в русском языке – это существительное среднего рода.

Затем необходимо на этом листе изменить ширину столбцов. Это надо сделать для того, чтобы каждая ячейка Эксель превратилась из прямоугольника в квадрат. Может быть, совсем идеального квадрата не получится, но будет прямоугольник, который очень похож на квадрат. Для этого надо выделить весь лист, а затем установить ширину столбца: 3,00 у.е. (26 пиксель).

Далее можно выделить те клетки Эксель, в которых будет расположено основное судоку. Необходимо также учесть заголовки строк и столбцов судоку, о которых мы говорили во введении этой книги. Можно покрасить каждый блок (квадратик размером 3 на 3) судоку в определенный цвет.

Должна получиться вот какая картинка (рисунок 2.1):

Рисунок 2.1.

На рисунке 2.1 четко видно, в какой области листа Эксель должно располагаться основное судоку. Также видны заголовки не только строк и столбцов Эксель, но и заголовки строк и столбцов судоку.

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

Так, например, ячейка А1 судоку – это самая верхняя левая ячейка судоку. Она расположена в ячейке C3 Эксель. Левый столбец Эксель (столбец A), а также верхняя строка Эксель (строка 1) скрыты, но это сделано только для того, чтобы рисунок с расположением основного судоку на листе Эксель был более удобным, чтобы было четко понятно, в какой именно области Эксель расположено всё то, что изображено на рисунке. Если читатели моей книги будут использовать на своем компьютере все то, что написано в данной книге, то я хочу сразу уточнить, что скрытие столбцов и строк, которое часто применяется в книге, совершать не надо, в книге это скрытие происходит только для удобства подачи информации. Если в каких-то ситуациях появится реальная необходимость скрытия столбцов или строк Эксель, то об этом будет сообщено отдельно.

2.3. Создание имен внутри основного судоку

2.3.1. Имя для строки 1

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

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

Начнем с присвоения имен строкам судоку. Первая строка будет верхней строкой. У этой строки судоку будет имя: стр_1. Аналогично, вторая строка судоку будет называться стр_2.

Сам макрос, присваивающий имена строкам судоку, можно составить в несколько этапов.

Первый этап: создание одного макроса, который присвоил бы первой строке макроса имя стр_1.

1) Нажмем на кнопку «Запись макроса» (запустим команду Запись макроса»).

Эту команду и эту кнопку можно найти во вкладке «Разработчик», в группе «Код» (рисунок 2.2):

Рисунок 2.2.

Если вкладка «Разработчик» по каким-то причинам отсутствует, ее можно добавить. Для этого нужно в меню «Файл» выбрать подменю «Параметры», а затем пункт «Настроить ленту».

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

Покажем фрагмент от правой части (из правого столбца, то есть из пункта «Основные вкладки») той информации, что входит в пункт «Настроить ленту» (рисунок 2.3):

Рисунок 2.3.

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

2) После того, как мы убедились, что у нас имеется вкладка «Разработчик», можно приступить к записи макроса. Для этого нужно выбрать команду «Запись макроса», которая находится в группе «Код». Покажем это всё на рисунке 2.4:

Рисунок 2.4.

Здесь стрелочкой выделена команда «Запись макроса». Когда мы выберем эту команду, нас спросят про имя создаваемого макроса. Присвоим ему имя: Имя_строкам_судоку.

3) Выделим строку 1 судоку, то есть самую верхнюю строку судоку. К этой строке относятся ячейки Эксель от C3 до K3 включительно.

4) Присвоим выделенному диапазону имя: стр_1. Это можно сделать двумя способами (точнее говоря, только одним – любым, на выбор – из следующих двух способов):

1: С помощью Диспетчера имен. Его (то есть диспетчер имен) можно найти так: Вкладка «Формулы», Группа «Определенные имена», кнопка «Диспетчер имен».

После нажатия на кнопку «Диспетчер имен» можно увидеть три варианта действий для каждого имени: «Создать…», «Изменить…» или «Удалить». В нашей конкретной ситуации нам нужен пункт «Создать…», ведь мы же именно создаем новое имя. Затем нужно будет заполнить графу «Имя», написав туда «стр_1» (рисунок 2.5):

Рисунок 2.5.

Как видно из рисунка 2.5, в графе «Имя» нужно ввести то имя, которые мы хотим присвоить ячейке или диапазону ячеек (в нашем случае это имя «стр_1», оно появилось не сразу при загрузке этой формы, а было введено нами с клавиатуры); в графе «Область» по умолчанию (сразу при первом появлении формы «Создание имени») введен пункт «Книга», и это значит, что вновь создаваемое имя можно будет использовать для каждого листа текущей книги. В графе «примечание» можно (при желании) записать примечание для данного имени, это поле не является обязательным. Если это поле оставить пустым, тогда мы создадим имя без примечания. В графе «Диапазон» вводится адрес ячейки или диапазона ячеек, к которым относится вновь создаваемое имя. Обычно этот пункт тоже заполнять не надо, он заполняется автоматически, он берется из той области Эксель, которую мы уже заранее предварительно выделили. Мы же недавно выделили нужный диапазон ячеек, этот диапазон и отображается здесь. Правда, при желании этот диапазон можно изменить (например, если мы вовремя поняли, что выделили не тот диапазон, или хотим по каким-то причинам изменить диапазон, выбрать другой диапазон).

Кстати, этот диапазон при необходимости можно изменить двумя способами: введя с клавиатуры адрес диапазона или нажав мышкой на квадратик (рисунок 2.6):

Рисунок 2.6.

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

В более новых версиях Эксель этот же квадратик выглядит немного иначе, в виде стрелочки, направленной вверх (рисунок 2.7):

Рисунок 2.7.

Как только мы нажмем на кнопку «OK» (которая есть в той форме, что изображена на рисунке 2.6 или 2.7, в зависимости от версии Эксель), то в нашу книгу добавится новое имя. В нашем случае это будет имя «стр_1», оно будет принадлежать диапазону ячеек Эксель, который состоит из строки 1 судоку. Напомню, строка №1 судоку – это самая верхняя строка судоку. Во всех тех судоку, о которых речь идет в данной книге, верхняя строка судоку будет расположена в строго определенном месте Эксель. Это будут ячейки Эксель с C3 по K3 включительно.

2: С помощью поля имени. Поле имени обычно находится левее строки формул. И поле имени, и строка формул обычно расположены чуть выше заголовков столбцов Эксель (рисунок 2.8).

Рисунок 2.8.

Обычно в поле имени отображается адрес текущей ячейки. Если выделен диапазон ячеек, тогда в поле имени отображается левая верхняя ячейка этого диапазона.

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

Хотелось бы еще несколько слов сказать о том, какие есть правила для создания имен. В имени не может быть пробелов. Имя должно начинаться с буквы или знака нижнего подчеркивания, но не может начинаться с цифры.

Начиная со второго знака имени можно использовать следующие символы: буквы, цифры, точки и знаки нижнего подчеркивания. Любые другие символы запрещено использовать, в том числе и пробел. В любом случае, при попытке ввести имя, которое будет создано с нарушением правил (например, при применении недопустимого для имени символа), мы не сможем это имя добавить в список имен, а получим сообщение, включающее в себя значок Warning Message, то есть сообщение с предупреждением, это сообщение обычно содержит большой желтый треугольник с восклицательным знаком. Кроме того, все попытки создать имя с ошибкой приведут не только к сообщению с предупреждением, но и к тому, что имя с ошибкой создано не будет, на данном этапе процесс создания имени будет завершен (и отменен), новое имя с ошибкой создано не будет.