qq2009显ip复活版_Excel复活节日期和IP地址

qq2009显ip复活版

As you know, Excel can do almost anything! Recently, I changed the server that my site and blog are on, and Excel helped with the IP Addresses. And since it's that time of year, let's see how Excel can calculate Easter dates for us.

如您所知，Excel几乎可以执行任何操作！ 最近，我更改了站点和博客所在的服务器，而Excel帮助了IP地址。 既然是一年中的那个时候，让我们看看Excel如何为我们计算复活节日期。

复活节日期计算 (Easter Dates Calculation)

It's Easter this Sunday, but if you'd like to verify that, or find out when Easter will be next year, use one of the Easter date calculations that Jerry Latham shared here, a few years ago.

这个星期日是复活节，但如果您想验证一下，或者想知道明年什么时候复活节，请使用几年前杰里·莱瑟姆(Jerry Latham)在此处分享的复活节日期计算方法之一。

• There are several worksheet formulas, and notes on their limitations.
  有几个工作表公式，并对其限制进行了说明。
• There are also four User Defined Functions, that use different methods for calculating the Easter date.
  还有四个用户定义函数，它们使用不同的方法来计算复活节日期。

In the screen shot below, the following formula is in cell B4:

在下面的屏幕快照中，B4单元格中包含以下公式：

=DOLLAR(("4/"&B2)/7+MOD(19*MOD(B2,19)-7,30)*14%,)*7-6

= DOLLAR((“” 4 /“＆B2)/ 7 + MOD(19 * MOD(B2,19)-7,30)* 14％，)* 7-6

To download a workbook with the code and formulas, to to my Contextures website.

要将包含代码和公式的工作簿下载到我的Contextures网站 。

Excel和IP地址 (Excel and IP Addresses)

As I mentioned earlier, I recently had my Contextures site and this blog moved to a different server. I hope that it is faster, and has minimal down time.

如前所述，我最近有了Contextures网站，并且此博客移至其他服务器。 我希望它速度更快，并且停机时间最少。

The only casualty that I've found so far is the comment section on this blog. Unfortunately, comments that were posted during the loooong transition time (March 14th to March 25th), were lost in the shuffle.

到目前为止，我发现的唯一人员伤亡是此博客上的评论部分。 不幸的是，在改版时间(3月14日至3月25日)中发布的评论在混洗中丢失了。

Other than that, things seem to be okay, but please let me know if you notice anything missing, or broken.

除此之外，一切似乎还可以，但是，如果您发现任何丢失或损坏的东西，请告诉我。

IP地址转换 (IP Address Conversion)

One of the key steps for moving to a new servers was to create DNS records for the them.

迁移到新服务器的关键步骤之一就是为它们创建DNS记录。

The form had boxes for the IPv4 address and the IPv6 address. Uh-oh!

该表格具有用于IPv4地址和IPv6地址的框。 哦！

Fortunately, I found examples of the different formats.

幸运的是，我找到了不同格式的示例。

• IPv4: 192.168.99.1

  IPv4 ：192.168.99.1

• IPv6: 0:0:0:0:0:ffff:c0a8:6301

  IPv6 ：0：0：0：0：0：ffff：c0a8：6301

To get that IPv6 address, I used an online converter.

为了获得该IPv6地址，我使用了一个在线转换器 。

IP如何转换？ (How Are IPs Converted?)

Looking at those two IP addresses, I couldn't see how one was converted to the other. More Googling took me to this page that explains the conversion.

查看这两个IP地址，我看不到一个如何转换为另一个。 更多Google搜索使我转到了解释转换的页面 。

• Each chunk of the IPv4 address is converted from decimal to hex, to get the IPv6 version.
  IPv4地址的每个块都从十进制转换为十六进制，以获得IPv6版本。
• The hex version each converted chunk becomes 2 characters
  每个转换的块的十六进制版本变为2个字符
• Those 8 characters are at the end of the IPv6 address.
  这8个字符位于IPv6地址的末尾。

When I used the online conversion tool, all the IPv6 addresses started with the same string:

当我使用在线转换工具时，所有IPv6地址都以相同的字符串开头：

• 0:0:0:0:0:ffff:

  0：0：0：0：0：ffff：

According to Wikipedia, that is the prefix used when PIv4 is mapped to an IPv6 address

根据Wikipedia的说法，这是 PIv4映射到IPv6地址时使用的前缀。

在Excel中转​​换IP地址 (Convert the IP Addresses in Excel)

Fortunately, my registrar said that the IPv6 version wasn't required, so I just entered my IPv4 addresses.

幸运的是，我的注册服务商说不需要IPv6版本，所以我只输入了IPv4地址。

But even though I didn't need one, why not build an IPv4 to IPv6 converter in Excel? It can convert Decimal to Hex, and it's better than an online tool! Am I right?

但是，即使我不需要一个，为什么不在Excel中构建IPv4到IPv6转换器呢？ 它可以将Decimal转换为Hex，并且比在线工具更好！ 我对吗？

Here's a screen shot of the first few columns on the conversion sheet.

这是转换表前几列的屏幕截图。

NOTE: This project was just for fun, and might not be accurate for what you need. If you need an IPv4 address converted to IPv6 format, check with your registrar or your hosting company.

注意 ：此项目只是为了娱乐，可能与您的需求不符。 如果您需要将IPv4地址转换为IPv6格式，请与您的注册商或托管公司联系 。

转换公式 (Conversion Formulas)

There are 3 sets of formulas for the conversion, and a final formula to pull the pieces together.

有3套用于转换的公式，以及一个将各个部分组合在一起的最终公式。

点 (Dots)

These formulas locate the 3 dots in the IPv4 address

这些公式可定位IPv4地址中的3个点

1. =FIND(".",[@IPv4])
   = FIND(“。”，[@ IPv4])
2. =FIND(".",[@IPv4],[@Dot01]+1)
   = FIND(“。”，[@ IPv4]，[@ Dot01] +1)
3. =FIND(".",[@IPv4],[@Dot02]+1)
   = FIND(“。”，[@ IPv4]，[@ Dot02] +1)

IP号码 (IP Numbers)

These formulas to pull out the decimal numbers, between the dots

这些公式可以拉出点之间的十进制数字

1. =--LEFT([@IPv4],[@Dot01]-1)
   =-LEFT([@ IPv4]，[@ Dot01] -1)
2. =--MID([@IPv4],[@Dot01]+1,[@Dot02]-[@Dot01]-1)
   =-MID([@ IPv4]，[@ Dot01] +1，[@ Dot02]-[@ Dot01] -1)
3. =--MID([@IPv4],[@Dot02]+1,[@Dot03]-[@Dot02]-1)
   =-MID([@ IPv4]，[@ Dot02] +1，[@ Dot03]-[@ Dot02] -1)
4. =--REPLACE([@IPv4],1,[@Dot03],"")
   =-REPLACE([@ IPv4]，1，[@ Dot03]，“”)

十六进制 (Hex)

These formulas convert each decimal number to hex

这些公式将每个十进制数转换为十六进制

1. =TEXT(DEC2HEX([@IP01]),"00")
   = TEXT(DEC2HEX([@ IP01])，“ 00”)
2. =TEXT(DEC2HEX([@IP02]),"00")
   = TEXT(DEC2HEX([@ IP02])，“ 00”)
3. =TEXT(DEC2HEX([@IP03]),"00")
   = TEXT(DEC2HEX([@ IP03])，“ 00”)
4. =TEXT(DEC2HEX([@IP04]),"00")
   = TEXT(DEC2HEX([@ IP04])，“ 00”)

IPv6地址 (IPv6 Address)

The formula in column B combines all the pieces, and starts with the mapping prefix (MapPre). The result is changed to lower case

B列中的公式将所有部分组合在一起，并以映射前缀(MapPre)开头。 结果更改为小写

1. =LOWER(MapPre &[Hex01]&[Hex02]&":"&[Hex03]&[Hex04])
   = LOWER(MapPre＆[Hex01]＆[Hex02]＆“：”＆[Hex03]＆[Hex04])

从IPv6转换为IPv4 (Convert from IPv6 to IPv4)

Then, because why not, I made another set of formulas to convert IPv6 addresses to IPv4.

然后，因为为什么不这样做，我制定了另一组公式将IPv6地址转换为IPv4。

This was easier, because each chunk is equal length – there's no need to find the location of each chunk.

这很容易，因为每个块的长度相等-无需查找每个块的位置。

These formulas use the HEX2DEC function, to convert the 2-digit hex codes to decimal numbers. You can download the sample file, to see the formulas.

这些公式使用HEX2DEC函数将2位十六进制代码转换为十进制数字。 您可以下载示例文件，以查看公式。

获取IP地址样本文件 (Get the IP Addresses Sample File)

To see all the formulas for the IP address conversion, go to the Excel Sample Files page on my Contextures site.

若要查看IP地址转换的所有公式，请转到Contextures网站上的“ Excel示例文件”页面 。

In the Functions section, look for FN0054 – Convert IP Addresses IPv4 to IPv6. The zipped file is in xlsx format, and does not contain any macros.

在“ 功能”部分中 ，查找FN0054 –将IP地址IPv4转换为IPv6 。 压缩文件为xlsx格式，不包含任何宏。

翻译自: https://contexturesblog.com/archives/2018/03/29/excel-easter-dates-and-ip-addresses/

qq2009显ip复活版