不使用VBA的excel中的MD5哈希函数 [英] MD5 Hash function in excel without using VBA

问题描述

我需要一个函数，它可以在 Excel 中获取整个单元格值，并将该值转换为新单元格中的 MD5 哈希等效值.excel中有没有公式可以做到这一点?我需要不使用 VBA 的解决方案.可能吗?

I need a function that will take an entire cell value in Excel and convert the value into its MD5 hash equivalent in a new cell. Is there a formula in excel that does that? I need solution without using VBA. Is it possible?

推荐答案

我做到了.在这里，您可以在没有 VBA 的情况下以纯 Excel 格式下载 MD5. 仅适用于 Office 2013.

I did this. Here you can download MD5 in pure Excel without VBA. Office 2013 ONLY.

https://tzamtzis.gr/2017/web-analytics/excel-function-md5-hashing-without-vba/https://tzamtzis.gr/tzamtziswp/wp-content/uploads/2017/05/MD5.xlsx

Office 2013 带有用于按位运算的方便函数，例如 BITAND()、BITOR()、BITXOR()、BITR[L]SHIFT().我认为在以前的 Office 版本中实现 MD5 是技术上可能的，但这将是彻底的地狱，因为您必须将值转换为二进制，然后将其转换为字符串，然后进行一些字符替换与算术加法/减法等.加上这个 15 位最大数字精度(这意味着即使处理 16 位二进制值也很难).

Office 2013 comes with handy functions for bitwise operations like BITAND(), BITOR(), BITXOR(), BITR[L]SHIFT(). I think it's technically possible to implement MD5 in previous versions of Office, but it will be utter hell because you will have to convert values to binary, then convert this to strings, then do some character replacement mixed with arithmetical addition/subtraction etc. etc. Add to this 15-digit maximum number precision (which means you will have hard time even processing 16-bit binary values).

我的工作簿可以处理长达 1024 个 ASCII 字符的字符串.这是为了减小文件大小(已经是 185 kb).如果您需要处理更长的消息，您可以在表格底部添加计算块(单个计算块由 64 行组成).但我认为这足以证明纯 Excel 中的 MD5 并不是一个好主意.

My workbook can process strings up to 1024 ASCII characters long. This is to reduce the file size (which is already 185 kb). If you need to process longer messages you can add calculation blocks to the bottom of the table (a single calculation block consists of 64 rows). But I think this is pretty much enough to demonstrate that MD5 in pure Excel isn't really a good idea.

这篇关于不使用VBA的excel中的MD5哈希函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！