Как закодировать текст в excel

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

Урок: Кодировка в Microsoft Word

Работа с кодировкой текста

Кодировка текста – эта набор электронных цифровых выражений, которые преобразуются в понятные для пользователя символы. Существует много видов кодировки, у каждого из которых имеются свои правила и язык. Умение программы распознавать конкретный язык и переводить его на понятные для обычного человека знаки (буквы, цифры, другие символы) определяет, сможет ли приложение работать с конкретным текстом или нет. Среди популярных текстовых кодировок следует выделить такие:

• Windows-1251;
• KOI-8;
• ASCII;
• ANSI;
• UKS-2;
• UTF-8 (Юникод).

Последнее наименование является самым распространенным среди кодировок в мире, так как считается своего рода универсальным стандартом.

Чаще всего, программа сама распознаёт кодировку и автоматически переключается на неё, но в отдельных случаях пользователю нужно указать приложению её вид. Только тогда оно сможет корректно работать с кодированными символами.

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

Способ 1: изменение кодировки с помощью Notepad++

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

1. Запускаем приложение Notepad++. Кликаем по пункту «Файл». Из открывшегося списка выбираем пункт «Открыть». Как альтернативный вариант, можно набрать на клавиатуре сочетание клавиш Ctrl+O.



2. Запускается окно открытия файла. Переходим в директорию, где расположен документ, который некорректно отобразился в Экселе. Выделяем его и жмем на кнопку «Открыть» в нижней части окна.



3. Файл открывается в окне редактора Notepad++. Внизу окна в правой части строки состояния указана текущая кодировка документа. Так как Excel отображает её некорректно, требуется произвести изменения. Набираем комбинацию клавиш Ctrl+A на клавиатуре, чтобы выделить весь текст. Кликаем по пункту меню «Кодировки». В открывшемся списке выбираем пункт «Преобразовать в UTF-8». Это кодировка Юникода и с ней Эксель работает максимально корректно.



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



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

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

Способ 2: применение Мастера текстов

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

1. Запускаем программу Excel. Нужно активировать именно само приложение, а не открыть с его помощью документ. То есть, перед вами должен предстать чистый лист. Переходим во вкладку «Данные». Кликаем на кнопку на ленте «Из текста», размещенную в блоке инструментов «Получение внешних данных».



2. Открывается окно импорта текстового файла. В нем поддерживается открытие следующих форматов:
   • TXT;
   • CSV;
   • PRN.

   Переходим в директорию размещения импортируемого файла, выделяем его и кликаем по кнопке «Импорт».



3. Открывается окно Мастера текстов. Как видим, в поле предварительного просмотра символы отображаются некорректно. В поле «Формат файла» раскрываем выпадающий список и меняем в нем кодировку на «Юникод (UTF-8)».
   

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



4. Открывается следующее окно Мастера текста. Тут можно изменить знак разделителя, но рекомендуется оставить настройки по умолчанию (знак табуляции). Жмем на кнопку «Далее».



5. В последнем окне имеется возможность изменить формат данных столбца:
   • Общий;
   • Текстовый;
   • Дата;
   • Пропустить столбец.

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



6. В следующем окне указываем координаты левой верхней ячейки диапазона на листе, куда будут вставлены данные. Это можно сделать, вбив адрес вручную в соответствующее поле или просто выделив нужную ячейку на листе. После того, как координаты добавлены, в поле окна жмем кнопку «OK».



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

Способ 3: сохранение файла в определенной кодировке

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

1. Переходим во вкладку «Файл». Кликаем по пункту «Сохранить как».



2. Открывается окно сохранения документа. С помощью интерфейса Проводника определяем директорию, где файл будет храниться. Затем выставляем тип файла, если хотим сохранить книгу в формате отличном от стандартного формата Excel (xlsx). Потом кликаем по параметру «Сервис» и в открывшемся списке выбираем пункт «Параметры веб-документа».



3. В открывшемся окне переходим во вкладку «Кодировка». В поле «Сохранить документ как» открываем выпадающий список и устанавливаем из перечня тот тип кодировки, который считаем нужным. После этого жмем на кнопку «OK».



4. Возвращаемся в окно «Сохранения документа» и тут жмем на кнопку «Сохранить».

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

Существует и другой путь к изменению настроек кодировки сохраненного текста.

1. Находясь во вкладке «Файл», кликаем по пункту «Параметры».



2. Открывается окно параметров Эксель. Выбираем подпункт «Дополнительно» из перечня расположенного в левой части окна. Центральную часть окна прокручиваем вниз до блока настроек «Общие». Тут кликаем по кнопке «Параметры веб-страницы».



3. Открывается уже знакомое нам окно «Параметры веб-документа», где мы проделываем все те же действия, о которых говорили ранее.



Теперь любой документ, сохраненный в Excel, будет иметь именно ту кодировку, которая была вами установлена.

Как видим, у Эксель нет инструмента, который позволил бы быстро и удобно конвертировать текст из одной кодировки в другую. Мастер текста имеет слишком громоздкий функционал и обладает множеством не нужных для подобной процедуры возможностей. Используя его, вам придется проходить несколько шагов, которые непосредственно на данный процесс не влияют, а служат для других целей. Даже конвертация через сторонний текстовый редактор Notepad++ в этом случае выглядит несколько проще. Сохранение файлов в заданной кодировке в приложении Excel тоже усложнено тем фактом, что каждый раз при желании сменить данный параметр, вам придется изменять глобальные настройки программы.

Для кодирования символов в unicode в программе Excel используется функция UNICODE. Она предназначена для выполнения преобразования первого символа строки в соответствующее ему кодовое (числовое) значение согласно таблице стандарта кодировки Юникод, и возвращает соответствующее числовое значение.

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

Примеры использования функции UNICODE для кодирования символов в Excel

Пример 1. В таблице введены несколько букв русского алфавита. Необходимо в смежном столбце вывести численные представления указанных символов в кодировке Юникод.

Чтобы вывести сразу все значения, выделим ячейки B1:B7 и запишем следующую формулу, а для подтверждения нажмем комбинацию клавиш CTRL+Enter:

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



Кодирование и представление символов чисел и в кодировке Юникод

Пример 2. Вывести массив случайных чисел от 0 до 9, создать массив кодов Unicode для данных чисел. Определить, существует ли между ними взаимосвязь (визуально с помощью графика).

Вид таблицы со столбцом, заполненным случайными числами:

Для заполнения столбца была использована функция СЛУЧМЕЖДУ(0;9). При любом действии на листе Excel, данная функция выполняет пересчет значений. Чтобы получить статические данные, полученные числа были скопированы и вставлены в ячейки с использованием инструмента Специальная вставка -> значения.

Вычислим значения кодов Unicode с помощью формулы:

Построим график:

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

Посимвольное кодирование паролей в Excel

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

Вид таблицы данных:

Чтобы автоматизировать процедуру шифрования, необходима следующая логика:

1. Функция, выполняющая разбивку строки на отдельные символы.
2. При переборе каждого символа из массива выполняется преобразование в числовое представление Юникод.
3. Формирование новой строки из численных представлений символов.

Для такой задачи рационально использовать макросы VBA. С помощью функций Excel можно реализовать полу ручной подход:

1. Разбивка на символы с помощью функции ПСТР и запись символов в отдельные ячейки.
2. Преобразование с помощью функции UNICODE.
3. Создание новой строки с использованием функции СЦЕП.

Получим массивы символов для каждого из паролей:

Функция ПСТР возвращает часть строки, ограниченную номерами позиций символов, указанных в качестве второго и третьего аргумента. Поскольку требуется вывести 1 символ, третий аргумент принимает значение 1. Функция ЧИСЛСТОЛБ($A$2:G$2) с зафиксированными ячейками (символ $) возвращает требуемый номер столбца, соответствующий номеру символа в строке.

Растянем данную формулу по строке и получим следующее:

Для получения шифра первого пароля используем следующую формулу массива:

Примечание:

Данная формула была записана в ячейку B7, поскольку формулы массивов не могут выполняться в объединенных ячейках. Аналогично сгенерируем шифры для остальных паролей:

Недостаток метода состоит в том, что для дешифрации (обратного преобразования) требуются ключи, указывающие на количество цифр, соответствующих числовому обозначению символа в Юникод.

Особенности кодирования с использованием функции UNICODE в Excel

Функция имеет следующую синтаксическую запись:

=UNICODE(текст)

Единственным аргументом, обязательным для заполнения, является текст – текстовая строка или один символ, для которого требуется определить соответствующее числовое значение кодировки Юникод.

Примечания:

1. Несмотря на то, что данная функция принимает на вход текстовую строку, фактически числовое представление определяется для первого символа строки. То есть, результаты выполнения функций =UNICODE(«слово») и =UNICODE(«с») будут эквивалентны, поскольку числовое представление определяется для символа «с» — 1089.
2. При работе с данными логического типа выполняется промежуточное преобразование к текстовым данным. Например, функции =UNICODE(ИСТИНА) и =UNICODE(«ИСТИНА») вернут одинаковое значение 1048, поскольку строчная «И» представлена числовым кодом 1048.
3. При вводе имен будет возвращен код ошибки #ИМЯ? (например, =UNICODE(табл1)).

27.11.2012 Калькуляторы, шаблоны, форматы, Практикум

Сегодня мы с вами будем заниматься шифрованием в Excel, в частности шифрованием паролей. Если в прошлый раз, мы просто генерировали пароль заданной длины и сложности при помощи встроенного генератора случайных чисел, и получали каждый раз разный пароль, то сегодня мы будем рассматривать пример, в котором при вводе исходного текста и ключевого слова будет всегда получаться одна и та же последовательность символов. Пароль можно сделать достаточно длинным и сложным, и использовать в повседневной практике. Нужно будет лишь помнить исходный текст и ключ, что согласитесь, запомнить гораздо проще, чем длинную последовательность ничего не значащих символов.

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

Шифр Виженера достаточно прост для понимания и основывается на шифре Цезаря. Напомню, что в шифре Цезаря зашифрованное сообщение получается путем сдвига алфавита на какое-то заданное число. Например, если это число 5, то буква А становиться  буквой Д, буква Б становиться буквой Е и т.д. Это один из самых простых алгоритмов шифрования.

Шифр Виженера состоит в последовательном применении нескольких шифров Цезаря, с различными сдвигами, определяемыми ключевым словом. Так если, ключевое слово «ЗАРЯ», то последовательность сдвигов будет следующая «9; 1; 18; 33» т.е. соответствующая порядковым номерам букв ключевого слова в алфавите. Эта последовательность сдвигов применяется к исходному тексту: первый символ смещается на 9 знаков, второй символ исходного текста – на 1 знак, третий – на 18, четвертый – на 33, пятый – снова на 9 и т.д. по кругу. Это уже более сложный алгоритм шифрования, вполне нам подходящий.

После краткого экскурса в криптографию приступим к созданию нашей шифровальной системы в Excel.

По сути дела, нам надо будет укрупненно решить следующие задачи:

Во-первых, обеспечить необходимую длину пароля независимо от исходного текста. В нашем случае пусть это будет 8 и 16 символов.

Во-вторых, получить последовательность символов с учетом сдвигов по ключевому слову соответствующую русским буквам алфавита;

В-третьих, перевести русские буквы алфавита в символы соответствующие латинским буквам, цифрам и специальным знакам;

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

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

Основой нашей разработки, как вы уже догадались, будут являться текстовые функции Excel.

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

Для удобства, присвоим имена «пароль» и «ключ» ячейкам, в которые будем вводить соответствующие данные.

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

Для этого мы будем использовать функцию ПСТР, возвращающую заданное число знаков из текстовой строки, начиная с указанной позиции и имеющую следующий синтаксис:

=ПСТР(текст; начальная_позиция; количество_знаков)

Где:

текст – исходный текст. В нашем случае это будет текст из ячеек с именами «пароль» и «ключ»;

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

количество_знаков – соответственно то количество знаков, которое нам надо получить. В нашем случае, поскольку мы будем «растаскивать» слово по буквам, то это будет 1.

Справа от ячеек, в которые будем вводить данные, у нас будет располагаться два диапазона по 16 ячеек расположенных в двух строках для «растаскивания» исходного пароля и ключа на одиночные символы (буквы). В нашем примере, это будет диапазон G6:V6 для растаскивания исходного пароля и G7:V7 для растаскивания ключа. Над этими диапазонами, в ячейках  G5:V5 разместим порядковые номера символов от 1 до 16.

С учетом этого, формула для «растаскивания» будет выглядеть следующим образом:

Для исходного пароля =ПСТР(пароль;G5;1)

Для ключа =ПСТР(ключ;G5;1)

Это для выделения первых символов из исходного пароля и ключа (в примере G5=1).

Скопировав эти формулы построчно в каждую из 16 ячеек, мы в каждой ячейке получим по одному символу, из которых состоит исходный пароль и ключ.

Однако, мы наверняка столкнемся с такой ситуацией, когда исходный пароль или ключ состоит менее чем из 16 знаков. В нашем примере исходный пароль «глобус» имеет 6 знаков, а ключевое слово «заря» — 4 знака.

В этом случае, нам на помощь приходит функция ПОВТОР, повторяющая текст заданное число раз и имеющая следующий синтаксис:

=ПОВТОР(текст; число_повторений)

Где:

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

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

Можно было бы установить число повторений равным 16, что гарантировало бы результат, однако, мы поступим более изящно. Для определения необходимого количества повторов, мы используем функцию ДЛСТР, которая возвращает количество знаков в текстовой строке, и имеющая интуитивно понятный синтаксис:

=ДЛСТР(текст)

Применительно к исходному паролю функция ДЛСТР должна вернуть значение 6, а применительно ключу – значение 4.

Далее совсем просто. Делим 16 на значение возвращаемое функцией ДЛСТР и получаем необходимое число повторов.

16/ДЛСТР(пароль)=2,66666(6)

16/ДЛСТР(ключ)=4

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

=ОКРУГЛВВЕРХ(число; число_разрядов).

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

Для пароля  =ПСТР(ПОВТОР(пароль;ОКРУГЛВВЕРХ(16/ДЛСТР(пароль);0));G5;1)

Для ключа: =ПСТР(ПОВТОР(ключ;ОКРУГЛВВЕРХ(16/ДЛСТР(ключ);0));G5;1)

На рисунке представлены результаты применения описанных выше формул.

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

В этом нам поможет текстовая функция ПОДСТАВИТЬ, которая позволяет произвести замену старого текста на новый в текстовой строке, и имеющая синтаксис:

=ПОДСТАВИТЬ(текст; стар_текст; нов_текст; [(номер вхождения)])

Где:

текст – текстовая строка, в которой будет осуществляться замена. В нашем случае, это ячейки с именами «Пароль» и «Ключ»;

стар_текст – старый текст, который подлежит замене. В нашем случае это пробел (в формуле указывается в кавычках   » «);

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

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

Функция замены пробела будет выглядеть следующим образом:

=ПОДСТАВИТЬ(пароль;» «;»_») для пароля и =ПОДСТАВИТЬ(ключ;» «;»_») для ключа.

С учетом этого, после того, как мы подставим эту формулу в нашу общую комбинацию, итоговая формула примет вид:

=ПСТР(ПОВТОР(ПОДСТАВИТЬ(пароль;» «;»_»);ОКРУГЛВВЕРХ(16/ДЛСТР(пароль);0));G5;1) для пароля и,

=ПСТР(ПОВТОР(ПОДСТАВИТЬ(ключ;» «;»_»);ОКРУГЛВВЕРХ(16/ДЛСТР(ключ);0));G5;1) для ключа.

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

Для этого мы можем воспользоваться функцией СТРОЧН, которая делает все буквы в текстовой строке строчными и имеет интуитивно понятный синтаксис:

=СТРОЧН(текст), где:

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

После подстановки, наша комбинация функций примет вид:

=ПСТР(ПОВТОР(ПОДСТАВИТЬ(СТРОЧН(пароль);» «;»_»);ОКРУГЛВВЕРХ(16/ДЛСТР(пароль);0));G5;1) для пароля и,

=ПСТР(ПОВТОР(ПОДСТАВИТЬ(СТРОЧН(ключ);» «;»_»);ОКРУГЛВВЕРХ(16/ДЛСТР(ключ);0));G5;1) для ключа.

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

Для перевода строчных букв русского алфавита в числовые значения, мы могли бы воспользоваться функцией КОДСИМВ(текст), возвращающую числовой код символа по таблице знаков ANSI.

Поскольку строчные буквы русского алфавита имеют код с 224 по 255 (32 буквы без «ё»), то получить порядковые номера букв лежащих в более привычном диапазоне от 1 до 33 (32), мы можем, отняв от кода символа число 223.

Формула будет иметь вид:

=КОДСИМВ(текст)-223;

Для пароля =КОДСИМВ(ПСТР(ПОВТОР(ПОДСТАВИТЬ(СТРОЧН(пароль);» «;»_»);ОКРУГЛВВЕРХ(16/ДЛСТР(пароль);0));G5;1))-223

Для ключа =КОДСИМВ(ПСТР(ПОВТОР(ПОДСТАВИТЬ(СТРОЧН(ключ);» «;»_»);ОКРУГЛВВЕРХ(16/ДЛСТР(ключ);0));G5;1))-223

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

Для этого будем использовать функцию ПОИСКПОЗ, которая возвращает относительную позицию элемента в массиве, и имеющая синтксис:

=ПОИСКПОЗ(искомое_значение; просматриваемый_массив; [тип_сопоставления]),

Где:

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

просматриваемый_массив – массив со всеми значениями элементов, в котором будет осуществляться поиск. В нашем случае, мы создадим массив состоящий из всех букв русского алфавита и знака подчеркивания в диапазоне ячеек А1:А34. Присвоим данному диапазону имя «кирилица»;

тип_сопоставления – принимает значения 1-меньше, 0-точное совпадение, -1-больше. В нашем случае мы будем искать точное сопоставление.

С учетом того, что символы русского алфавита соответствующие паролю и ключу у нас находятся в диапазонах G6:V6 и G7:V7 соответственно, то формула для перевода букв в числа будет выглядеть следующим образом:

Для первого символа пароля =ПОИСКПОЗ(G6;кирилица;0) и =ПОИСКПОЗ(G7;кирилица;0) для ключа.

Результат можно посмотреть на рисунке.

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

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

Для перевода будем использовать функцию ИНДЕКС которая  возвращает значение элемента таблицы или массива, заданного номером строки и номером столбца.

=ИНДЕКС(массив; номер_строки; [номер столбца])

И созданную нами  одномерную таблицу символов из одного столбца и количеством строк по числу используемых нами символов. Таблица символов будет состоять из строчных и заглавных латинских букв, чисел от 1 до 9 и спецсимволов.

Таблица должна иметь 68 строк и 67 символов, со 2 строки по 68. Связано это с тем, что наша исходная таблица с буквами русского алфавита имеет 34 символа, после перевода пароля и ключа в порядковые номера строк этой таблицы и их попарного сложения, мы можем получить числовой ряд с номерами минимум 2 (1+1) и максимум 68 (34+34).

Создаем таблицу символов в диапазоне В1:В68, присваиваем ей имя «латиница», и заполняем символами латинских заглавных и строчных букв, цифр и спецсимволов. Можно заполнять подряд, можно вперемешку. В нашем примере таблица собрана таким образом: заглавная и строчная латинская буква подряд по алфавиту и через каждые девять символов идет цифра (АаВbСс1DdEeFf2…) и в конце спецсимволы. Сделано это для распределения вероятности попадания заглавных строчных букв и цифр.

Таблицу символов можно периодически менять. Это по сути дела еще один этап шифрования.

С учетом того, что числовой ряд, который мы будем кодировать находиться у нас в диапазоне G12:V12, формула для перевода чисел в символы будет иметь вид:

=ИНДЕКС(латиница;G12;1) для первого числа.

Скопировав ее во все 16 ячеек нашего диапазона, мы получим последовательность ничего не значащих символов, чего собственно говоря, мы и добивались.

Теперь остается их только соединить вместе и поместить в одну ячейку.

Сделать это можно при помощи функции СЦЕПИТЬ.

=СЦЕПИТЬ(G14;H14;I14;J14;K14;L14;M14;N14;O14;P14;Q14;R14;S14;T14;U14;V14).

Поскольку мы решили сделать в нашем примере возможность выбора между 8 и 16 символами, то добавляем соответствующий элемент управления – «Переключатель».

Добавить Переключатель на лист можно через вкладку Разработчик→ группа Элементы управления→ команда Вставить→ Элементы управления формы → Переключатель.

Настраиваем переключатель следующим образом:

Правой кнопкой мыши кликаем на переключателе и в появившемся контекстном меню выбираем пункт Формат объекта. В появившемся диалоговом окне Формат элемента управления на вкладке Элемент управления  в поле Связь с ячейкой устанавливаем связь с ячейкой D12.

Теперь, поскольку у нас всего два переключателя, то когда установлен первый, в ячейке D12 будет значение 1, когда будет установлен второй – значение 2.

Этого достаточно, чтобы использовать логическую функцию ЕСЛИ и в одном случае сцепить 8 символов, а в другом сцепить 16 символов.

С учетом наличия переключателей, наша формула будет иметь вид:

=ЕСЛИ(D12=1;СЦЕПИТЬ(G14;H14;I14;J14;K14;L14;M14;N14);СЦЕПИТЬ(G14;H14;I14;J14;K14;L14;M14;N14;O14;P14;Q14;R14;S14;T14;U14;V14))

Наш чудо-шифратор пароля готов.

Как обычно выполняем некоторые организационные мероприятия для защиты листа и придания ему надлежащего вида. С этой целью, мы устанавливаем защиту ячеек на всем листе, кроме ячеек D12 (там где меняется параметр после установки переключателя). Чтобы ничего не нарушить случайным образом скрываем столбец D. Скрываем все вспомогательные данные на листе, устанавливая формат данных «;;;» и защищаем лист.

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

Скачать

Пароль, шифрование

Лабораторная
работа № 5

Методы
защиты информации. Шифр Цезаря.

Цель
работы:
Освоить технологию шифрования и
дешифрования

информации
в среде Excel с использованием шифра
Цезаря.

5.1.
Теоретическая
часть

Шифр
Цезаря является частным случаем шифра
простой замены (одно алфавитной
подстановки). Свое название этот шифр
получил по имени римского

императора
Гая Юлия Цезаря, который использовал
этот шифр при переписке.

При
шифровании исходного текста каждая
буква заменяется другой буквой того же
алфавита по следующему правилу. Заменяющая
буква определяется путем смещения по
алфавиту к концу от исходной буквы на
k
букв.
При достижении конца алфавита выполняется
циклический переход к его началу.

Например:
пусть A
–
используемый алфавит:

A={a1,
a2,…,am,…,aN},

где
a1,
a2,…,am,…,aN
–
символы
алфавита;
N ширина
алфавита.

Пусть
k
–
число позиций сдвига символов алфавита
при шифровании,

0<k<N.
При шифровании каждый символ алфавита
с номером m
из
кодируемого текста заменяется на символ
этого же алфавита с номером m+k.
Если m+k>N,
номер символа в алфавите A
определяется
как m+k—N.

Для
дешифрования текстовой информации
номер позиции символа восстанавливаемого
текста определяется как m—k.
Если m—k<0,
то вычисление этого

номера
производится как m—k+N.

Достоинством
этой системы является простота шифрования
и

дешифрования.
К недостаткам системы Цезаря следует
отнести:

—
подстановки, выполняемые в соответствии
с системой Цезаря, не маскируют частот
появления различных букв исходного и
отрытого текста;

—
сохраняется алфавитный порядок в
последовательности заменяющих букв;

при
изменении значения k
изменяются
только начальные позиции такой
последовательности;

—
число возможных ключей k
мало;

—
шифр Цезаря легко вскрывается на основе
анализа частот появления букв в

шифре.

5.2.
Порядок выполнения лабораторной работы

1.
Войти в среду Excel. Создать новый документ,
перейти на второй лист этого

документа.
Начиная с ячейки A1
до A40
набрать алфавит, как показано на рисунке
5.1”а”. Выделить весь диапазон алфавита
и назначить ему имя “ABC”.

2.
На первом листе документа в ячейке B1
набрать текст, который необходимо

зашифровать,
например: Гай
Юлий Цезарь:”Пришел,
увидел,
победил!”
При

наборе
текста необходимо использовать только
те символы, которые входят в

алфавит.

3.
В ячейке B3 записать формулу «=ПРОПИСН(B1)»,
функция ПРОПИСН переводит буквенные
символы в строке в прописные буквы.

4.
В ячейке D3 записать формулу «=ДЛСТР(B3)»,
функция ДЛСТР позволяет

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

5.
В ячейку D4 записать значение k,
например, 5.

6.
В столбце А, начиная с ячейки А6,
пронумеровать ячейки числами
последовательного ряда от 1 до N, где N –
число символов в тексте, включая пробелы.
N рассчитано в ячейке D3.

7.
В ячейку B6, записать формулу
“=ПСТР(B$3;A6;1)”, которая разделяет
кодируемый текст на отдельные символы.
Скопировать эту формулу в ячейки В7-

В48.

8.
В ячейку C6 записать формулу
“=ПОИСКПОЗ(B6;ABC;0)”. Функция ПОИСКПОЗ
производит поиск индекса (номера позиции)
символа в массиве ABC, который был определен
на листе 2. Скопировать содержимое ячейки
C6 в ячейки

C7-C48.

9.
Получив номер символа в алфавите ABC,
произвести сдвиг нумерации алфавита
для кодируемой последовательности
символов. В ячейку D6 записать

формулу:

“=ЕСЛИ(ПОИСКПОЗ(B6;ABC;0)+$D$4>40;ПОИСКПОЗ(B6;ABC;0)+$D$4-

40;ПОИСКПОЗ(B6;ABC;0)+$D$4)”.
(5.1)

Эта
формула производит сдвиг номеров
символов алфавита на величину k
и

определяет
номер заменяющего символа из алфавита
ABC. Содержимое D6

скопировать
в область D7-D48.

10.
Выбрать символы из алфавита ABC в
соответствии с новыми номерами. В

ячейку
E6 записать формулу “=ИНДЕКС(ABC;D6)”.
Скопировать содержимое

ячейки
E6 в область E7-E48.

11.
Для получения строки закодированного
текста необходимо в ячейку F6 записать
“=E6”, в ячейку F7 соответственно –
“=F6&E7”. Далее скопировать содержимое
ячейки F7, в область F8-F48. В ячейке F48
прочитать зашифрованный

текст.

12.
Для проверки шифрования произвести
дешифрование полученного текста и

сравнить
его с исходным. На третьем листе выполнить
дешифрование аналогично пунктам 2-11
лабораторной работы. При этом необходимо
учесть следующие особенности:

в
п. 2 набрать зашифрованный текст;

в
п. 9 в ячейку D6 записать формулу:

=ЕСЛИ(ПОИСКПОЗ(B6;ABC;0)-$D$4<0;ПОИСКПОЗ(B6;ABC;0)-

$D$4+40;ПОИСКПОЗ(B6;ABC;0)-$D$4).
(5.2)

Получение
исходного текста в ячейке F48 третьей
страницы свидетельствует

о
корректном выполнении лабораторной
работы.

5.3.
Контрольные
вопросы:

1.
В чем заключается принцип защиты
информации с использованием шифра
Цезаря?

2.
Объяснить формулы (5.1) и (5.2).

3.
Можно ли использовать программы настоящей
лабораторной работы для

шифрования
другой информации? Если да, то, каким
образом?

4.
В чем достоинства и недостатки шифра
Цезаря?

5.
Какие еще методы защиты информации Вам
известны?

Рис.
5.1. — Фрагменты документов Excel по
лабораторной работе № 5:

а)
алфавит символов шифра Цезаря; б)
начальная часть документа

шифрования;
в) и г) начальная и конечная часть
документа дешифрования__

Соседние файлы в папке Excel

• #

  16.03.20163.82 Mб317Phones.xls

• #
• #

  16.03.20163.82 Mб75Копия Phones.xls

• #
• #
• #