Содержание
- Проверка вводимых значений в Excel
- Проверка данных в Excel
- Окно контрольного значения в Excel
- Excel Store
- Обеспечение целостности файлов средствами Excel
- Существует ли функция Excel для создания хэш-значения?
- 6 ответов
- Формула суммы по условию критерия выборки значений в Excel
- Примеры формулы для суммы диапазонов с условием отбора в Excel
- Пример логического выражения в формуле для суммы с условием
- Суммирование по неточному совпадению в условии критерия отбора
Проверка вводимых значений в Excel
Программа Excel обладает эффективными инструментами для удобного контроля ввода больших объемов данных. Так же присутствуют средства для одновременного экспонирования нескольких значений ячеек, которые не соответствуют определенным требованиям.
Проверка данных в Excel
Приготовьте лист доходов и расходов как показано на рисунке:
Прибыль – это естественно доход минус расход. Допустим нам нужно проверить, в какие дни прибыль падала ниже 40$. Решение следующее:
- Выделите данные в диапазоне D2:D6 и выберите инструмент: «Данные»-«Работа с данными»-«Проверка данных».
- В появившемся окне: «Проверка вводимых значений» установите такие же настройки как показано на рисунке. И нажмите ОК.
- Теперь выберите инструмент из выпадающего списка: «Данные»-«Проверка данных»-«Обвести неверные данные». И обратите внимание на результат:
- При необходимости можете удалить красные обводки, выбрав инструмент «Удалить обводку неверных данных».
Примечание. Обведение несоответствующих критериям данных работают только в тех диапазонах, в которых установлены эти же критерии инструментом «Проверка данных».
Окно контрольного значения в Excel
Смоделируем следующую ситуацию. Нужно закупить расходные материалы для небольшой фирмы. Требуется приобрести канцелярские принадлежности, потребительские принадлежности и моющие средства. Списки закупаемых позиций записываем отдельно на каждом листе, разделяя их по типам товаров.
Наперед не известно, сколько позиций будет в каждой группе товаров.
На листе ИТОГО должно быть посчитано, сколько денег нужно для приобретения каждой группы товаров по отдельности. И какая общая сумма расходов.
Дописывая каждую новую позицию, пользователь должен иметь возможность следить за общей суммой расходов. Решение:
- Создайте книгу с листами, так как указано выше на рисунке и на каждом листе добавьте по несколько позиций разным количеством.
- На листе: «ИТОГО» поставьте формулы как указано выше на рисунке и перейдите в ячейку B4.
- Перейдите на закладку «Формулы» и выберите инструмент «Окно контрольного значения»
- В появившемся окне нажмите кнопку «Добавить контрольное значение» и в нем указываем адрес ячейки, за значением которого мы будем следить: =ИТОГО!$B$4. Жмите добавить.
Теперь у Вас есть возможность избежать бесконтрольных расходов при заполнении листов новыми товарами.
Не важно, на каком листе Вы находитесь. Поверх всех окон отображено окно контрольного значения, а в нем видно общую сумму расходов. Несложно догадаться, как сделать так, чтобы в этом окне отображались и значения общей суммы каждой группы по отдельности.
Полезный совет. Если взять курсором мышки за заголовок «Окно контрольного значения» и навести на строку состояния окна программы (в область ярлычков с названиями листов), то оно удобно зафиксируется в нижней части главного окна.
Источник
Excel Store
Обеспечение целостности файлов средствами Excel
Краткое описание
Макрос для формирования hash-суммы по указанному файлу. Поддерживаются алгоритмы: MD2, MD5, MD4, SHA1, SHA256, SHA384 и SHA512. Сформированный hash сохраняется в буфере обмена для быстрого использования в дальнейшей работе.
Подробное описание
Как часто вы передаете или получаете по работе какие-либо файлы? Я например, очень часто что-то кому-то отправляю и также часто получаю. В ряде случаев бывает важно знать, тот ли файл вам прислали и те ли данные вы используете, которые планировалось. Сохранность и целостность передаваемых (получаемых) файлов порой бывают очень важны.
Кто работал с торрентами, наверное замечали на странице загрузки рядом с файлом, как правило, пишут его MD5 код. Задумывались зачем? Основных причин, как правило, две:
1. чтобы после загрузки файла проверить, весь ли файл был загружен
2. чтобы перед запуском файла убедиться, тот ли файл был загружен
Данный принцип применим в работе с любыми файлами, в том числе и с файлами из пакета MS Office (Excel, Access, Word и др).
Представим ситуацию.
Вы получили письмо с файлом эксель от вашего коллеги. Но по чистой случайности, коллега отправил вам старую версию файла. Хорошо, если несоответствия в данных были замечены сразу. Но могло случиться и так, что вы не заметили разницы и отправили данные дальше наверх, тем самым (сами того не желая) дезинформировав руководство.
Другой пример.
Вы скачали файл из интернета, но при запуске он не открывается. Пробуете открыть аналогичный файл у коллеги — все работает. Можно конечно загрузить файл снова, но как проверить, что и в этом случае копирование будет полноценным?
Еще пример из практики.
Ваши подчиненные (или коллеги) обязаны в определенный срок выкладывать свои отчеты (фото, excel, txt и тп. файлы) в сетевую папку. Здорово, когда можно доверять людям. Но еще лучше знать, где и на чем вас могут обмануть. Так и в этом случае. Отчет сотрудник, например, выложил вовремя, но спустя n-нное количество дней он его отредактировал (изменив дату и время редактирования файла на исходные). Визуально к файлу не подкопаться — дата создания и сохранения теже, что и раньше. Только вот сведения в нем уже другие.
MD5, равно как и MD2, MD4, SHA1, SHA2 — разновидности алгоритмов шифрования. С их помощью можно сделать, так называемый, снимок (отпечаток) файла или иначе говоря, получить hash-сумму файла. Любое изменение файла будет вести к изменению его hash-суммы.
В приложенном выше excel-шаблоне вы найдете простой пример, как получить MD5 код указанного файла. Работа шаблона основана на vba-классе clsHash, который выложен здесь. Данный класс также поддерживает алгоритмы: MD2, MD4, SHA1, SHA256, SHA384 и SHA512.
Так как весь код написан на vba, то адаптировать шаблон под свои задачи может любой желающий. При возникновении вопросов, пишите — буду рад помочь.
Источник
Существует ли функция Excel для создания хэш-значения?
Я работаю с несколькими списками данных, которые имеют ключ по имени документа. Имена документов, хотя и очень описательные, довольно громоздки, если мне нужно их просмотреть (до 256 байт-это много недвижимости), и мне бы хотелось создать меньшее ключевое поле, которое легко воспроизводится в случае, если мне нужно сделать VLOOKUP с другой workseet или книги.
Я думаю хэш из названия, который был бы уникальным и воспроизводимым на каждое название было бы больше всего соответствующий. Есть ли функция, или я смотрю на разработку собственного алгоритма?
есть мысли или идеи по той или иной стратегии?
6 ответов
вам не нужно писать свою собственную функцию — другие уже сделали это за вас.
Например, я собрал и сравнил пять хэш-функций VBA на этом ответе на StackOverflow
лично я использую эту функцию VBA
- ее назвали с =BASE64SHA1(A1) в Excel после копирования макроса в VBA модуль
- требуется .NET, так как он использует библиотеку » Microsoft MSXML «(с опозданием обязательный)
настройка длины хэша
- хэш-изначально это 28 символов Unicode строку (регистр + спецсимволы)
- настройки длины хэш-кода со строки: Const cutoff As Integer = 5
- 4 цифры hash = 36 коллизий в 6895 линиях = 0.5 % частота коллизий
- 5 цифр хэш = 0 коллизий в 6895 строках = 0% коллизий
есть также хэш функции (все три функции CRC16), который не требует .NET и не использует внешние библиотеки. Но хэш длиннее и создает больше коллизий.
вы также можете просто скачать это пример книги и поиграйте со всеми 5 реализациями хэша. Как видите, на первом листе хорошее сравнение
Я не очень забочусь о столкновениях, но мне нужен слабый псевдорандомайзер строки, основанные на строковом поле переменной длины. Вот одно безумное решение, которое хорошо работает:
здесь Z2 — это ячейка, содержащая строку, которую вы хотите хэш.
«MOD» S там предотвратить переполнить к научной нотации. 1009 является простым, может использовать что-нибудь X, так что X*255 max_int_size . 10 произвольно; используйте что угодно. Значения «Else» являются произвольными (цифры pi сюда!); используйте что угодно. Расположение символов (1,3,5,7,9) произвольное; используйте что угодно.
для достаточно небольшого списка вы можете создать скремблер (хэш-функцию бедного человека), используя встроенные функции Excel.
здесь A1 и B1 содержат случайную начальную букву и длину строки.
немного возиться и проверки, и в большинстве случаев вы можете получить работоспособный уникальный идентификатор довольно быстро.
как это работает: формула использует первую букву строки и фиксированную букву, взятую из середины строки, и использует LEN () как «функция раздувания», чтобы уменьшить вероятность столкновений.
предостережение: это не хэш, но когда вам нужно что-то сделать быстро, и можете проверить результаты, чтобы увидеть, что нет никаких столкновений, он работает довольно хорошо.
изменить: Если ваши строки должны иметь переменную длину (например, полные имена), но извлекаются из записи базы данных с полями фиксированной ширины, вам нужно сделать это так это:
Источник
Формула суммы по условию критерия выборки значений в Excel
Простые логические функции такие как ЕСЛИ обычно предназначены для работы с одним столбцом или одной ячейкой. Excel также предлагает несколько других логических функций служащих для агрегирования данных. Например, функция СУММЕСЛИ для выборочного суммирования диапазона значений по условию.
Примеры формулы для суммы диапазонов с условием отбора в Excel
Ниже на рисунке представлен в таблице список счетов вместе с состоянием по каждому счету в виде положительных или отрицательных чисел. Допустим нам необходимо посчитать сумму всех отрицательных чисел для расчета суммарного расхода по движению финансовых средств. Этот результат будет позже сравниваться вместе с сумой положительных чисел с целью верификации и вывода балансового сальдо. Узнаем одинаковые ли суммы доходов и расходов – сойдется ли у нас дебит с кредитом. Для суммирования числовых значений по условию в Excel применяется логическая функция =СУММЕСЛИ():
Функция СУММЕСЛИ анализирует каждое значение ячейки в диапазоне B2:B12 и проверяет соответствует ли оно заданному условию (указанному во втором аргументе функции). Если значение меньше чем 0, тогда условие выполнено и данное число учитывается в общей итоговой сумме. Числовые значения больше или равно нулю игнорируются функцией. Проигнорированы также текстовые значения и пустые ячейки.
В приведенном примере сначала проверяется значения ячейки B2 и так как оно больше чем 0 – будет проигнорировано. Далее проверяется ячейка B3. В ней числовое значение меньше нуля, значит условие выполнено, поэтому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В результате его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а остальные ячейки не учитываются в итоговой сумме.
Обратите внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие между ними — это обратный оператор сравнения во втором аргументе где указывается условие для суммирования – вместо строки » 0″ (больше чем ноль). Теперь мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.
Пример логического выражения в формуле для суммы с условием
Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно ( =1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:
Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!
Внимание! В первом примере нулевые значения нам необязательно было учитывать, так как на балансовое сальдо это никак не повлияло бы, но во втором случаи нужно составлять критерий условия суммирования иначе, чтобы не допустить ошибочных просчетов.
Второй аргумент функции СУММЕСЛИ, то есть условие, которое должно быть выполнено, записывается между двойными кавычками. В данном примере используется символ сравнения – «меньше» ( ) меньше ( ), больше или равно (>=), меньше или равно ( Таблица правил составления критериев условий:
| Чтобы создать условие | Примените правило | Пример |
| Значение равно заданному числу или ячейке с данным адресом. | Не используйте знак равенства и двойных кавычек. | =СУММЕСЛИ(B1:B10;3) |
| Значение равно текстовой строке. | Не используйте знак равенства, но используйте двойные кавычки по краям. | =СУММЕСЛИ(B1:B10;»Клиент5″) |
| Значение отличается от заданного числа. | Поместите оператор и число в двойные кавычки. | =СУММЕСЛИ(B1:B10;»>=50″) |
| Значение отличается от текстовой строки. | Поместите оператор и число в двойные кавычки. | =СУММЕСЛИ(B1:B10;»<>выплата») |
| Значение отличается от ячейки по указанному адресу или от результата вычисления формулы. | Поместите оператор сравнения в двойные кавычки и соедините его символом амперсант (&) вместе со ссылкой на ячейку или с формулой. | =СУММЕСЛИ(A1:A10;» «&СЕГОДНЯ()) |
| Значение содержит фрагмент строки | Используйте операторы многозначных символов и поместите их в двойные кавычки | =СУММЕСЛИ(A1:A10;»*кг*»;B1:B10) |
Во втором аргументе критериев условий можно использовать разные функции и формулы. Ниже на рисунке изображен список дат и присвоенных им значений. Важно отметить что сегодня на момент написания статьи дата – «03.11.2018». Чтобы суммировать числовые значения только по сегодняшней дате используйте формулу:
Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):
=»&СЕГОДНЯ();B2:B10)/B11′ > 
Суммирование по неточному совпадению в условии критерия отбора
Во втором логическом аргументе критериев условий функции СУММЕСЛИ можно применять многозначные символы – (?)и(*) для составления относительных неточных запросов. Знак вопроса (?) – следует читать как любой символ, а звездочка (*) – это строка из любого количества любых символов или пустая строка. Например, нам необходимо просуммировать только защитные краски-лаки с кодом 3 английские буквы в начале наименования:
Суммируются все значения ячеек в диапазоне B2:B16 в соответствии со значениями в ячейках диапазона A2:A16, в которых после третьего символа фрагмент строки «-защита».
Таким образом удалось суммировать только определенную группу товаров в общем списке отчета по складу. Данный фрагмент наименования товара должен встречаться в определенном месте – 3 символа от начала строки. Нет необходимости использовать сложные формулы с функцией =ЛЕВСИМВ() и т.д. Достаточно лишь воспользоваться операторами многозначных символов чтобы сформулировать простой и лаконичный запрос к базе данных с минимальными нагрузками на системные ресурсы.
Источник
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 |
/// <summary>Implements a 32-bits cyclic redundancy check (CRC) hash algorithm.</summary> /// <remarks>This class is not intended to be used for security purposes. For security applications use MD5, SHA1, SHA256, SHA384, /// or SHA512 in the System.Security.Cryptography namespace.</remarks> public class CRC32 : HashAlgorithm { #region CONSTRUCTORS static CRC32() { _crc32TablesCache = Hashtable.Synchronized(new Hashtable()); _defaultCRC = new CRC32(); } /// <summary>Creates a CRC32 object using the <see cref="DefaultPolynomial"/>.</summary> public CRC32() : this(DefaultPolynomial) { } /// <summary>Creates a CRC32 object using the specified polynomial.</summary> /// <remarks>The polynomical should be supplied in its bit-reflected form. <see cref="DefaultPolynomial"/>.</remarks> public CRC32(uint polynomial) { HashSizeValue = 32; _crc32Table = (uint[]) _crc32TablesCache[polynomial]; if (_crc32Table == null) { _crc32Table = _buildCRC32Table(polynomial); _crc32TablesCache.Add(polynomial, _crc32Table); } Initialize(); } // static constructor #endregion #region PROPERTIES /// <summary>Gets the default polynomial (used in WinZip, Ethernet, etc.)</summary> /// <remarks>The default polynomial is a bit-reflected version of the standard polynomial 0x04C11DB7 used by WinZip, Ethernet, etc.</remarks> public static readonly uint DefaultPolynomial = 0xEDB88320; // Bitwise reflection of 0x04C11DB7; #endregion #region METHODS /// <summary>Initializes an implementation of HashAlgorithm.</summary> public override sealed void Initialize() { _crc = _allOnes; } /// <summary>Routes data written to the object into the hash algorithm for computing the hash.</summary> protected override void HashCore(byte[] buffer, int offset, int count) { for (int i = offset; i < count; i++) { ulong ptr = (_crc & 0xFF) ^ buffer[i]; _crc >>= 8; _crc ^= _crc32Table[ptr]; } } /// <summary>Finalizes the hash computation after the last data is processed by the cryptographic stream object.</summary> protected override byte[] HashFinal() { var finalHash = new byte[4]; ulong finalCRC = _crc ^ _allOnes; finalHash[0] = (byte) ((finalCRC >> 0) & 0xFF); finalHash[1] = (byte) ((finalCRC >> 8) & 0xFF); finalHash[2] = (byte) ((finalCRC >> 16) & 0xFF); finalHash[3] = (byte) ((finalCRC >> 24) & 0xFF); return finalHash; } /// <summary>Computes the CRC32 value for the given ASCII string using the <see cref="DefaultPolynomial"/>.</summary> public static int Compute(string asciiString) { _defaultCRC.Initialize(); return ToInt32(_defaultCRC.ComputeHash(asciiString)); } /// <summary>Computes the CRC32 value for the given input stream using the <see cref="DefaultPolynomial"/>.</summary> public static int Compute(Stream inputStream) { _defaultCRC.Initialize(); return ToInt32(_defaultCRC.ComputeHash(inputStream)); } /// <summary>Computes the CRC32 value for the input data using the <see cref="DefaultPolynomial"/>.</summary> public static int Compute(byte[] buffer) { _defaultCRC.Initialize(); return ToInt32(_defaultCRC.ComputeHash(buffer)); } /// <summary>Computes the hash value for the input data using the <see cref="DefaultPolynomial"/>.</summary> public static int Compute(byte[] buffer, int offset, int count) { _defaultCRC.Initialize(); return ToInt32(_defaultCRC.ComputeHash(buffer, offset, count)); } /// <summary>Computes the hash value for the given ASCII string.</summary> /// <remarks>The computation preserves the internal state between the calls, so it can be used for computation of a stream data.</remarks> public byte[] ComputeHash(string asciiString) { byte[] rawBytes = Encoding.ASCII.GetBytes(asciiString); return ComputeHash(rawBytes); } /// <summary>Computes the hash value for the given input stream.</summary> /// <remarks>The computation preserves the internal state between the calls, so it can be used for computation of a stream data.</remarks> public new byte[] ComputeHash(Stream inputStream) { var buffer = new byte[4096]; int bytesRead; while ((bytesRead = inputStream.Read(buffer, 0, 4096)) > 0) { HashCore(buffer, 0, bytesRead); } return HashFinal(); } /// <summary>Computes the hash value for the input data.</summary> /// <remarks>The computation preserves the internal state between the calls, so it can be used for computation of a stream data.</remarks> public new byte[] ComputeHash(byte[] buffer) { return ComputeHash(buffer, 0, buffer.Length); } /// <summary>Computes the hash value for the input data.</summary> /// <remarks>The computation preserves the internal state between the calls, so it can be used for computation of a stream data.</remarks> public new byte[] ComputeHash(byte[] buffer, int offset, int count) { HashCore(buffer, offset, count); return HashFinal(); } #endregion #region PRIVATE SECTION private const uint _allOnes = 0xffffffff; private static readonly CRC32 _defaultCRC; private static readonly Hashtable _crc32TablesCache; private readonly uint[] _crc32Table; private uint _crc; // Builds a crc32 table given a polynomial private static uint[] _buildCRC32Table(uint polynomial) { var table = new uint[256]; // 256 values representing ASCII character codes. for (int i = 0; i < 256; i++) { var crc = (uint) i; for (int j = 8; j > 0; j--) { if ((crc & 1) == 1) crc = (crc >> 1) ^ polynomial; else crc >>= 1; } table[i] = crc; } return table; } private static int ToInt32(byte[] buffer) { return BitConverter.ToInt32(buffer, 0); } #endregion } |
У меня следующий сценарий:
- у меня есть таблица а с большой таблицей.
- Я открываю таблицу B и копирую в нее таблицу из A.
- Я хочу проверить, правильно ли я скопировал таблицу из A в B.
чтобы сделать шаг 3, я обычно суммирую (и среднее, и т. д.) все числа в таблице проверьте, если я получаю тот же номер, что и для исходной таблицы.
однако, я хотел бы иметь функцию контрольной суммы как checksum(<2D RANGE>), которая возвращает контрольную сумму (например, MD5) таблицы в заданном диапазоне. Я бы использовал эту функцию checkusum в таблице A, а затем в таблице B и сравнил полученные значения, чтобы узнать, соответствует ли скопированная таблица исходной.
источник
Краткое описание
Макрос для формирования hash-суммы по указанному файлу. Поддерживаются алгоритмы: MD2, MD5, MD4, SHA1, SHA256, SHA384 и SHA512. Сформированный hash сохраняется в буфере обмена для быстрого использования в дальнейшей работе.
Подробное описание
Как часто вы передаете или получаете по работе какие-либо файлы? Я например, очень часто что-то кому-то отправляю и также часто получаю. В ряде случаев бывает важно знать, тот ли файл вам прислали и те ли данные вы используете, которые планировалось. Сохранность и целостность передаваемых (получаемых) файлов порой бывают очень важны.
Кто работал с торрентами, наверное замечали на странице загрузки рядом с файлом, как правило, пишут его MD5 код. Задумывались зачем? Основных причин, как правило, две:
1. чтобы после загрузки файла проверить, весь ли файл был загружен
2. чтобы перед запуском файла убедиться, тот ли файл был загружен
Данный принцип применим в работе с любыми файлами, в том числе и с файлами из пакета MS Office (Excel, Access, Word и др).
Представим ситуацию.
Вы получили письмо с файлом эксель от вашего коллеги. Но по чистой случайности, коллега отправил вам старую версию файла. Хорошо, если несоответствия в данных были замечены сразу. Но могло случиться и так, что вы не заметили разницы и отправили данные дальше наверх, тем самым (сами того не желая) дезинформировав руководство.
Другой пример.
Вы скачали файл из интернета, но при запуске он не открывается. Пробуете открыть аналогичный файл у коллеги — все работает. Можно конечно загрузить файл снова, но как проверить, что и в этом случае копирование будет полноценным?
Еще пример из практики.
Ваши подчиненные (или коллеги) обязаны в определенный срок выкладывать свои отчеты (фото, excel, txt и тп. файлы) в сетевую папку. Здорово, когда можно доверять людям. Но еще лучше знать, где и на чем вас могут обмануть. Так и в этом случае. Отчет сотрудник, например, выложил вовремя, но спустя n-нное количество дней он его отредактировал (изменив дату и время редактирования файла на исходные). Визуально к файлу не подкопаться — дата создания и сохранения теже, что и раньше. Только вот сведения в нем уже другие.
MD5, равно как и MD2, MD4, SHA1, SHA2 — разновидности алгоритмов шифрования. С их помощью можно сделать, так называемый, снимок (отпечаток) файла или иначе говоря, получить hash-сумму файла. Любое изменение файла будет вести к изменению его hash-суммы.
В приложенном выше excel-шаблоне вы найдете простой пример, как получить MD5 код указанного файла. Работа шаблона основана на vba-классе clsHash, который выложен здесь. Данный класс также поддерживает алгоритмы: MD2, MD4, SHA1, SHA256, SHA384 и SHA512.
Так как весь код написан на vba, то адаптировать шаблон под свои задачи может любой желающий. При возникновении вопросов, пишите — буду рад помочь.
Для быстрого анализа данных протокола SPI (показания магнитометра погрузчика Jungenricht) из Salea logic, импортировал их в Excel. Для вычисления CRC понадобилось реализовать функцию вычисления (прототип взят из сети и доработан).
Сервис->Макрос->Редактор Visual Basic, встаем на проект Insert->Module.
Вставляем следующий код:
Public Function CRC8_8H2F(ByVal AppID As String) As Variant
Dim table() As Variant
table = Array(&H0, &H2F, &H5E, &H71, &HBC, &H93, &HE2, &HCD, &H57, &H78, &H9, &H26, &HEB, &HC4, &HB5, &H9A, _
&HAE, &H81, &HF0, &HDF, &H12, &H3D, &H4C, &H63, &HF9, &HD6, &HA7, &H88, &H45, &H6A, &H1B, &H34, _
&H73, &H5C, &H2D, &H2, &HCF, &HE0, &H91, &HBE, &H24, &HB, &H7A, &H55, &H98, &HB7, &HC6, &HE9, _
&HDD, &HF2, &H83, &HAC, &H61, &H4E, &H3F, &H10, &H8A, &HA5, &HD4, &HFB, &H36, &H19, &H68, &H47, _
&HE6, &HC9, &HB8, &H97, &H5A, &H75, &H4, &H2B, &HB1, &H9E, &HEF, &HC0, &HD, &H22, &H53, &H7C, _
&H48, &H67, &H16, &H39, &HF4, &HDB, &HAA, &H85, &H1F, &H30, &H41, &H6E, &HA3, &H8C, &HFD, &HD2, _
&H95, &HBA, &HCB, &HE4, &H29, &H6, &H77, &H58, &HC2, &HED, &H9C, &HB3, &H7E, &H51, &H20, &HF, _
&H3B, &H14, &H65, &H4A, &H87, &HA8, &HD9, &HF6, &H6C, &H43, &H32, &H1D, &HD0, &HFF, &H8E, &HA1, _
&HE3, &HCC, &HBD, &H92, &H5F, &H70, &H1, &H2E, &HB4, &H9B, &HEA, &HC5, &H8, &H27, &H56, &H79, _
&H4D, &H62, &H13, &H3C, &HF1, &HDE, &HAF, &H80, &H1A, &H35, &H44, &H6B, &HA6, &H89, &HF8, &HD7, _
&H90, &HBF, &HCE, &HE1, &H2C, &H3, &H72, &H5D, &HC7, &HE8, &H99, &HB6, &H7B, &H54, &H25, &HA, _
&H3E, &H11, &H60, &H4F, &H82, &HAD, &HDC, &HF3, &H69, &H46, &H37, &H18, &HD5, &HFA, &H8B, &HA4, _
&H5, &H2A, &H5B, &H74, &HB9, &H96, &HE7, &HC8, &H52, &H7D, &HC, &H23, &HEE, &HC1, &HB0, &H9F, _
&HAB, &H84, &HF5, &HDA, &H17, &H38, &H49, &H66, &HFC, &HD3, &HA2, &H8D, &H40, &H6F, &H1E, &H31, _
&H76, &H59, &H28, &H7, &HCA, &HE5, &H94, &HBB, &H21, &HE, &H7F, &H50, &H9D, &HB2, &HC3, &HEC, _
&HD8, &HF7, &H86, &HA9, &H64, &H4B, &H3A, &H15, &H8F, &HA0, &HD1, &HFE, &H33, &H1C, &H6D, &H42)
Dim crc As Byte Dim AppIDarray() As Byte Dim pos As Byte AppIDarray = HexToByte(AppID) aidLength = UBound(AppIDarray) crc = &HFF For j = 0 To aidLength pos = AppIDarray(j) Xor crc crc = table(pos) Next j crc = crc Xor &HFF CRC8_8H2F = Hex(crc) 'CRC8_8H2F = AppIDarray(1) End Function
Public Function HexToByte(strHex As String) As Byte()
Dim i As Integer
Dim tempByte As Byte
Dim outBytes() As Byte
ReDim outBytes(Len(strHex) 2 - 1)
For i = 0 To Len(strHex) 2 - 1
For j = 0 To 1
char = Mid(strHex, i * 2 + j + 1, 1)
Select Case char
Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9":
tempByte = tempByte Or (Asc(char) - 48)
Case "A", "B", "C", "D", "E", "F":
tempByte = tempByte Or (Asc(char) - 55)
Case "a", "b", "c", "d", "e", "f":
tempByte = tempByte Or (Asc(char) - 87)
End Select
If j = 0 Then
tempByte = tempByte * 2 ^ 4
Else
outBytes(i) = tempByte
tempByte = 0
End If
Next
Next
HexToByte = outBytes
End Function
Вызов из любой ячейки следующим образом.
=CRC8_8H2F(«12345678abcdef»).
При скачивании ISO образов и архивов больших размеров всегда есть вероятность получить «битый» файл. Во времена Dial-UP такое было сплошь и рядом. И хотя сейчас такое случается намного реже, чтобы убедиться, что перед вами «оригинальный» файл придумали контрольные суммы, которые вычисляются на основе содержимого и позволяют заметить несоответствие даже одного байта.
То есть, если вы измените один байт в проверяемом файле, то и контрольная сумма такого файла так же изменится.
Для чего нужны контрольные суммы
У контрольных сумм две задачи:
- Убедиться, что файл скачался корректно.
- Убедиться, что файл не был изменен злоумышленниками.
Зная контрольную сумму оригинала, можно проверить является ли ваша копия подлинной.
Как вычислить контрольную сумму он-лайн
Контрольную сумму можно проверить он-лайн. Но я не буду рекомендовать этот способ, так как если размер вашего файла несколько ГигаБайт, то это займет много времени и всегда есть вероятность ошибки при передаче файла. Кроме того делиться своими файлами со сторонними сервисами не правильно.
Как узнать контрольную сумму файла в Windows
Разумнее вычислить контрольную сумму локально на своем компьютере. Это быстро и конфиденциально. В этой статье я опишу несколько способов получения контрольных сумм, как с помощью сторонних программ, так и непосредственно с помощью самой операционной системы Виндовс.
Файловый менеджер Total Commander
Total Commander — это популярный файловый менеджер, работающий на платформах Microsoft Windows и Android. В нем есть встроенная функция вычисления контрольных сумм.
После чего вы можете выбрать один из алгоритмом вычисления контрольных сумм.
По-умолчанию Total Commander создает файл с именем проверяемого и с расширением по имени выбранного алгоритма расчета контрольной суммы.
Файловый архиватор 7-Zip
7-Zip — свободный, бесплатный файловый архиватор с высокой степенью сжатия данных. Он поддерживает несколько алгоритмов сжатия и множество форматов данных, включая собственный формат 7z c высокоэффективным алгоритмом сжатия LZMA.
Этот архиватор имеет встроенную функцию вычисления контрольных сумм. Запустить ее можно прямо из контекстного меню Windows:
Если выбрать «звездочку», то программа подсчитает сразу несколько контрольных сумм:
Полученные данные можно выделить и скопировать в текстовый документ.
Как подсчитать контрольную сумму файла из консоли Windows
Чтобы посчитать контрольную сумму совсем не обязательно устанавливать специальные программы. И если вы не пользуетесь упомянутыми выше, то можете рассчитать контрольную сумму прямо из командной строки операционной системы.
Например, чтобы посчитать контрольную сумму SHA1 с помощью утилиты CertUtil нужно запустить командную строку Windows 10, 8 или Windows 7 и ввести следующую команду:
certutil -hashfile путь_к_файлу алгоритм
Вот пример ее работы через несколько минут:
Считаем контрольную сумму в PowerShell
PowerShell — это средство автоматизации от Microsoft, с интерфейсом командной строки и языка сценариев, работает и включена в состав Windows 8 и новее.
Чтобы вычислить контрольную сумму файла необходимо выполнить команду Get-FileHash указав через пробел имя файла и алгоритм вычисления контрольной суммы:
Get-FileHash "Disk:Full Path to fileFile Name.Extension" -Algorithm SHA1
Обратите внимание, что полный путь и имя файла лучше заключить в двойные кавычки.
По-умолчанию, если не указать тип контрольной суммы, то будет посчитана SHA-256.
Для алгоритмов вычисления контрольной суммы в Windows PowerShell поддерживаются следующие значения:
- SHA1
- SHA256 (по умолчанию)
- SHA384
- SHA512
- MD5
Для оформления вывода в виде списка можно использовать параметр | Format-List. Например:
Get-FileHash "Disk:Full Path to fileFile Name.Extension" | Format-List
Тогда результат работы будет выглядеть так:
Подробнее об использовании команды Get-FileHash можно прочитать на официальном сайте Microsoft — https://docs.microsoft.com/ru-ru/powershell/module/microsoft.powershell.utility/get-filehash
Какой алгоритм вычисления контрольных сумм самый правильный
MD5, SHA-1, SHA-256 и прочие – это разные алгоритмы хеш-функции. Хэши являются результатом работы криптографических алгоритмов, и представляют собой строку символов. Часто эти строки имеют фиксированную длину, независимо от размера входных данных.
MD5 самый быстрый, считается устаревшим, а SHA-256 имеет наименьшую вероятность коллизии, когда два разных файла имеют одинаковую контрольную сумму.
Для проверки целостности файла вам следует использовать тот, который предоставляет издатель. Если у вас на выбор есть несколько контрольных сумм, то лучше выбрать в следующей последовательности MD5, SHA-1, SHA-256, последний вариант является более предпочтительным.
Выводы
Если вы сомневаетесь в целостности скаченных файлов из интернета, особенно когда это касается оригинальных образов операционных систем, то проверьте их контрольную сумму. Сделать это можно как с помощью уже имеющихся у вас программ, так и воспользовавшись встроенными средствами операционной системы Windows.
Самый простой и удобный способ проверить контрольную сумму файла
Контрольная сумма позволяет в первую очередь определить подлинность (оригинальность) файла. Контрольная сумма вычисляется особым образом на основе содержимого файла, в результате можно будет определить изменения или несоответствие даже одного байта. В некоторых случаях это престо необходимо. Проверить контрольную сумму файла можно разными способами, существует очень много программ, расширений и различных сервисов. Результат у всех будет одинаковый, вопрос только в удобстве их использования. Сегодня хочу поделиться с Вами самым удобным и простым вариантом проверки и сравнения контрольной суммы любого файла.
Проверка целостности и подлинности файлов посредством вычисления контрольной суммы
Для проверки контрольной суммы Я использую программу «HashTab». Это своего рода расширение стандартного проводника Windows. После установки в стандартное окно «Свойства» добавляется вкладка «Хеш-суммы файлов»
hashtab.ru
В котором вычисляются контрольные суммы по выбранным алгоритмам хеширования.
Их список можно изменить через настройки.
Для того чтобы сравнить суммы (проверить оригинальность файла) нужно воспользоваться пунктом «Сравнить файл», выбрать его и программа покажет одинаковые контрольные суммы или нет.
Если файл был изменен то контрольные суммы будут отличаться.
Это самый простой и удобный способ проверки контрольной суммы и сравнения файлов. Но если Вы знаете способ еще проще и удобной то обязательно поделитесь им.