Как в excel сделать расшифровку ячейки
Иногда бывает необходимо с помощью формул узнать о какой-либо ячейке подробную информацию и параметры, чтобы использовать это в расчетах. Например, выяснить число или текст в ячейке или какой числовой формат в ней установлен. Сделать это можно, используя функцию ЯЧЕЙКА (CELL) .
Синтаксис у функции следующий:
=ЯЧЕЙКА( Параметр ; Адрес )
где Адрес - это, понятное дело, ссылка на нужную ячейку, а вот Параметр - это кодовое слово, определяющее какую именно информацию о ячейке мы хотим получить. У этого аргумента несколько возможных значений:
Давайте рассмотрим пару трюков с применением этой функции на практике.
Например, можно получить имя текущего листа формулой, используя функцию ЯЧЕЙКА с параметром "имяфайла" и извлекающей все символы правее закрывающей квадратной скобки:
Или подсветить условным форматированием все незащищенные ячейки в заданном диапазоне:
Ссылки по теме
А я нашел на соседнем форуме другое решение отображения имени листа в конкретной ячейке
=--ПОДСТАВИТЬ(ПРАВБ(ЯЧЕЙКА("filename";A1);2);"]";)
Данное решение было нужно для отображения в ячейке L1 таблицы названия листа. Листы были переименованы от 1 до 53 по возможному количеству недель по ISO. На первом листе в ячейке J3 внесено начало конкретного года. Теперь внеся допустим в ячейку G4 формулу
=$J$3+L1*7-6 имеем дату начала конкретной недели, указанной в ячейке L1, а в любой другой формулой =G4+4 имеем дату окончания 5 дневной рабочей недели.
Очень удобно для составления еженедельных докладов, отчетов и тд.
А что такое ПРАВБ ? Имелось ввиду ПРАВСИМВ ?
И это будет работать только с числовыми именами листов, кстати.
Добрый день Николай! Да, изначально требовалось, чтобы имена листов имели цифровые наименования недель по ISO. Просто созданные отчетные файлы абсолютно идентичны, отличаются только годом и соответственно конкретными датами начала - окончания недель, при 5 дневной рабочей неделе.
Заметьте, если в формуле =G4+4 прибавить не 4, а 5 то получаем 6 дневную рабочую неделю, так как исходя из отображенного названия листа в виде цифры в ячейке G4 высчитывается дата начала недели (имеется в виду понедельник).
А функция ПРАВБ возвращает последний символ или несколько последних символов текстовой строки на основе заданного числа байтов.
Функция ПРАВСИМВ предназначена для языков с однобайтовой кодировкой, а ПРАВБ — для языков с двухбайтовой кодировкой.
Не буду спорить, что конкретно лучше. Но дорога была ложка к обеду.
Ага, ясно. ПРАВБ вместо ПРАВСИМВ тут исключительно для сокращения длины формулы - не использовал ее ни разу на практике, признаюсь
Николай! Добрый день!
А если есть некоторое количество ячеек, условным форматированием отображенных шрифтом с красным цветом, их можно как то подсчитать?
Прошу прощения, решил вопрос на форуме.
Николай, очень красивое решение с подсветкой незащищенных ячеек через «Условное форматирование»!
Беру на практику. Спасибо!
Еще в Excel есть функция с похожим интерфейсом ИНФОРМ
Максимум, что интересного она умеет (на мой взгляд):
=ИНФОРМ("ВЕРСИЯ") – какая версия Excel?
=ИНФОРМ("ПЕРЕВЫЧИСЛИТЬ") -- как происходим пересчет листа?
Определение версии очень полезный метод при разработке решений в Excel..
Например, сейчас в Excel 2016 появилось много аналитических инструментов уровня Self-Service BI (это надстройки Power (Query / Pivot / View / Map) которые не работают полностью или частично в предыдущих версиях Excel.
Парольная защита листов в Microsoft Excel давно стала притчей во языцех. В том плане, что ее, по-сути, нет. С регулярностью примерно раз в месяц я получаю вопросы по почте на тему "как мне защитить мои данные на листе Excel от просмотра/изменения?" и каждый раз не знаю что ответить. Можно, конечно, дать ссылочку на статью с подробным описанием всех способов защиты ячеек и листов в Excel, но такая защита остановит только начинающего. В сети можно найти кучу платных и бесплатных программ для взлома такой защиты тупым перебором за считанные минуты.
В какой-то момент мне это надоело и я стал искать способы более надежной защиты данных в Excel собственными силами. Самым простым и удобным оказался шифр Виженера.
Принцип шифра Виженера
Символы в конце алфавита (Э, Ю, Я), соответственно, будут превращаться его начало (А, Б, В).
Допустим, мы хотим зашифровать фразу "КЛАД ЗАРЫТ В САДУ" используя слово ЗИМА в качестве ключа. Запишем это слово подряд несколько раз под исходной фразой:
Для удобства шифрования используем так называемый "квадрат Виженера" - таблицу, где в каждой строке алфавит сдвигается на одну позицию вправо:
Реализация формулами по квадрату Виженера
Если использовать готовый квадрат Виженера как в примере выше, то реализовать шифрование можно одной формулой с помощью функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) , как это было описано в статье про двумерный поиск в таблице. Выглядеть это может примерно так:
Логика этой формулы следующая:
Реализация формулами по кодам символов
Легко сообразить, что в реальной жизни в документах могут использоваться не только буквы русского языка, но и латиница, цифры, знаки препинания и т.д. Делать квадрат Виженера с участием всех этих символов - та еще эпопея, но есть другой, гораздо более простой способ.
Внутри компьютера и операционной системы каждый символ имеет свой числовой код от 0 до 255 (его еще называют ASCII-кодом). Microsoft Excel имеет в своем стандартном наборе две функции, которые умеют с ними работать:
- Функция КОДСИМВ (CODE) - выдает числовой код символа, указанного в качестве аргумента. Например КОДСИМВ("Ж") выдаст 198.
- Функция СИМВОЛ (CHAR) - выдает символ, соответствующий указанному в аргументе коду, т.е. наоборот СИМВОЛ(198) даст нам букву Ж.
Для применения шифра Виженера запишем наш исходный текст и ключ друг под другом как раньше и выведем коды каждой буквы с помощью функции КОДСИМВ:
Теперь сложим коды символов ключа и исходного текста, добавив функцию ОСТАТ (MOD) , чтобы при превышении максимально допустимого количества символов (256) остаться в пределах 0-255:
Само-собой, можно было бы обойтись и без дополнительных строк, уложив все функции в одну формулу для компактности:
Расшифровка производится совершенно аналогично, только знак "плюс" в формуле меняется на "минус":
Для шпионских игр шифрование такими спецсимволами, конечно, не очень удобно - так и представляю себе глаза радистки Кэт при попытке передать третий и пятый символы нашей шифровки :) Но нам их, отстреливаясь из именного ТТ во время погони, на бумажке не писать, так что для наших целей - сойдет.
Макросы для шифрования-дешифрования
Ну, а теперь самое интересное. Чтобы применить шифр Виженера в реальной жизни лучше будет воспользоваться простым макросом, который проводит все описанные в предыдущем пункте операции с каждой ячейкой текущего листа автоматически. Откройте редактор Visual Basic с помощью сочетания клавиш Alt+F11 или кнопкой Visual Basic на вкладке Разработчик (Developer) . Вставьте новый модуль с помощью команды меню Insert - Module и скопируйте туда текст наших макросов:
Первый макрос запрашивает у пользователя ключ и шифрует все ячейки текущего листа. Второй макрос производит обратную операцию дешифрования. Запустить получившиеся макросы можно с помощью сочетания клавиш Alt+F8 или кнопки Макросы (Macros) на вкладке Разработчик (Developer) . Выглядеть все это может примерно так:
Как показать ссылку на ячейку (как текст) вместо значения и наоборот в Excel?
Обычно в ячейках формулы отображаются результаты вычислений в Excel, и скучно щелкать каждую ячейку, чтобы просмотреть ссылку на нее. Здесь я расскажу о трех методах отображения всех ссылок на ячейки вместо значений и наоборот в текущем листе, а также для отображения указанных ссылок на ячейки в виде текста вместо значений и наоборот.
- Показывать всю ссылку на ячейку вместо значения и наоборот в указанном диапазоне
- Показывать всю ссылку на ячейку вместо значения и наоборот в активном листе
- Показывать все ссылки на ячейки вместо значений и наоборот на всех листах
Показывать все ссылки на ячейки вместо значений и наоборот с помощью функции Показать формулу
Мы можем быстро показать все ссылки на ячейки вместо вычисленных результатов формул, нажав Формулы > Показать формулу. Смотрите скриншот ниже:
Если вам нужно снова отобразить все рассчитанные результаты в ячейках, нажмите кнопку Показать формулу снова.
Показывать ссылку на ячейку в виде текста вместо значения и наоборот в указанном диапазоне
С другой стороны, некоторые пользователи могут захотеть отображать указанную ссылку на ячейку как текст вместо значений, но не все ссылки на все ячейки формулы. Здесь я рекомендую вам попробовать Kutools for Excel's Преобразовать формулу в текст полезности и Преобразовать текст в формулу утилита.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
Выделите ячейки формулы, в которых вы хотите отображать ссылки на ячейки в виде текста, а не результатов вычислений, и щелкните Кутулс > Content > Преобразовать формулу в текст.
Примечание: Напротив, для отображения вычисленных результатов вместо формул выберите эти формулы и нажмите Кутулс > Content > Преобразовать текст в формулу. Смотрите скриншот выше.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Показывать все ссылки на ячейки вместо значений и наоборот на всех листах
Как мы знаем, формулы> Показать формулы могут отображать / скрывать формулы только на активном листе. Чтобы показать или скрыть все ссылки на ячейки вместо значений или наоборот на всех листах, попробуйте Kutools for Excel's параметры просмотра функцию.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1, Нажмите Кутулс > Показать / Скрыть> Параметры просмотра. Смотрите скриншот ниже:
2. В открывшемся диалоговом окне «Параметры просмотра» установите флажок Формулы вариант, а затем щелкните Применить ко всем листам кнопки и Ok кнопку последовательно. Смотрите скриншот ниже:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демонстрация: показывать ссылку на ячейку (как текст) вместо значения и наоборот в Excel
В некоторых случаях вы можете захотеть зашифровать некоторые ячейки, чтобы предотвратить просмотр ячеек другими пользователями. Как это сделать быстро? В этой статье я расскажу о приемах быстрого шифрования и дешифрования содержимого выбранных ячеек в Excel.
Зашифровать или расшифровать выбранные ячейки с помощью Kutools for Excel
Зашифровать или расшифровать выбранные ячейки с помощью VBA
Вот код VBA, который может быстро зашифровать или расшифровать выбранные ячейки в Excel.
1. Нажмите Alt + F11 ключи для отображения Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте ниже код VBA в новый Модули окно.
VBA: зашифровать или расшифровать выбранные ячейки.
3. Затем нажмите F5 ключ для выполнения VBA, и появится диалоговое окно для выбора ячеек для шифрования. Смотрите скриншот:
4. Затем нажмите OK и введите пароль для зашифрованных ячеек в другом всплывающем диалоговом окне. Смотрите скриншот:
5. Нажмите OK, а затем в третьем диалоговом окне введите 1 чтобы зашифровать выбранные ячейки, если вы хотите расшифровать ячейки, введите 2 . Смотрите скриншот:
6. Нажмите OK, и выбранные ячейки зашифрованы. Смотрите скриншот:
Зашифровать или расшифровать выбранные ячейки с помощью Kutools for Excel
С помощью вышеуказанного метода шифрования или дешифрования ячеек немного сложно, но если у вас есть Kutools for Excel установлен, вы можете использовать Зашифровать ячейки и Расшифровать ячейки утилиты, чтобы быстро это сделать.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите ячейки, которые нужно зашифровать, и нажмите Кутулс Плюс > Зашифровать ячейки. Смотрите скриншот:
2. Затем введите и подтвердите пароль в Зашифровать ячейки диалоговом окне и выберите один тип шифрования в Маска раздел. Смотрите скриншот:
3. Нажмите Ok or Применить, и теперь выбранные ячейки зашифрованы.
Если вам нужно расшифровать ячейки, выделите их и нажмите Кутулс Плюс > Decrypt, и введите пароль для их расшифровки. Смотрите скриншот:
Обычно мы используем формулу = A1 для ссылки на ячейку A1 на другую ячейку на листе. Но это может ссылаться только на значение ячейки. Если вы хотите указать значение ячейки, а также ее формат, вам нужно попробовать другой метод. В этой статье представлены два метода достижения этой цели.
Справочный формат и значение из другой ячейки с вставкой связанного изображения
Предположим, вы хотите указать формат и значение из ячейки A1, для этого сделайте следующее.
1. Выберите ячейку (A1), на которую нужно сослаться, затем скопируйте ее, нажав Ctrl + C ключи.
2. Перейдите к ячейке, которую вы хотите связать со ссылочной ячейкой, щелкните ее правой кнопкой мыши и выберите > Специальная вставка > Связанное изображение. Смотрите скриншот:
Теперь формат и значение ячейки A1 привязаны к указанной ячейке. И формат и значение этих двух ячеек будут синхронными, как показано на скриншоте ниже.
Формат автоматической ссылки и значение из другой ячейки с VBA
Вы можете автоматически ссылаться на формат и значение из другой ячейки, запустив приведенный ниже сценарий VBA.
1. Щелкните правой кнопкой мыши вкладку листа, содержащую нужную ячейку, а затем щелкните Просмотреть код из контекстного меню.
2. Во всплывающем Microsoft Visual для приложений окна, скопируйте и вставьте приведенный ниже код VBA в окно кода.
Код VBA: формат ссылки и значение из другой ячейки
Внимание: В коде Sheet1 - это имя листа, содержащего ячейку, в которой необходимо указать как значение, так и формат. A1 и E2 означает, что ячейка A1 будет автоматически ссылаться на ячейку E2.
С этого момента, когда значение изменяется в ячейке A1 листа Sheet1, его значение и формат будут немедленно ссылаться на ячейку E2.
Читайте также: