excel里程dk格式_具有总距离的Excel里程查询

excel里程dk格式

If you're planning a vacation trip, Excel can help. It's a great place to keep your packing lists, and you can track your vacation spending too (if you really want to know the total!). I've just uploaded a new sample file that will show how far you'll travel. Select cities, and formulas do a mileage lookup, with total distance from start to end.

如果您打算度假旅行，Excel可以为您提供帮助。 这是保存装箱单的好地方，您也可以跟踪自己的度假支出(如果您真的想知道总数！)。 我刚刚上传了一个新的示例文件，该文件将显示您将旅行多远。 选择城市，然后公式会进行里程查询，并计算从开始到结束的总距离。

两城市里程查询 (Two City Mileage Lookup)

The new workbook is based on a previous one, which showed the distance between two cities. Select a city name in each green cell, and see the distance between those two cities, in miles.

新的工作簿基于上一个工作簿，该工作簿显示了两个城市之间的距离 。 在每个绿色单元格中选择一个城市名称，然后查看两个城市之间的距离(以英里为单位)。

The distances come from a lookup table, shown below. Find the intersection of the two selected city names, and that is the distance between them.

距离来自查找表，如下所示。 找到两个选定城市名称的交集，即它们之间的距离。

The data in that table is from the Florida Dept. of Transportation.

该表中的数据来自佛罗里达交通运输部 。

计划一次假期旅行 (Plan a Vacation Trip)

The old workbook was handy if you were going from one city to another, and then straight back home. But what about a longer journey, with stops at multiple cities?

如果您要从一个城市到另一个城市，然后直接回到家，旧的工作簿就很方便。 但是，在多个城市停靠的更长的旅程呢？

Maybe you'd like to plan a trip to a few vacation spots in Florida, and see how far you'll travel. I've put arrows on a Florida map, to show our imaginary vacation route. The trip starts from Gainesville, gets down to Sarasota, and then back home.

也许您想计划去佛罗里达州的几个度假胜地旅行，看看您能走多远。 我在佛罗里达州地图上放了一些箭头，以显示我们想象中的度假路线。 旅程从盖恩斯维尔(Gainesville)开始，到达萨拉索塔(Sarasota)，然后回到家。

NOTE: I found a copyright-free map on The National Map site, which is run by the U.S. Geological Survey. Do not visit that site if you are easily distracted – there are lots of fascinating data collections and maps there. You have been warned!

注意 ：我在由美国地质调查局运营的国家地图站点上找到了无版权的地图。 如果您很容易分心，请不要访问该站点-那里有许多有趣的数据收集和地图。 你被警告了！

度假旅行的总距离 (Total Distance for a Vacation Trip)

In the new workbook, there are data validation drop down lists where you can choose up to 6 cities.

在新工作簿中，有“数据验证” 下拉列表 ，您可以在其中选择最多6个城市。

There are formulas in the next column, to do a lookup from the mileage table, and another formula shows a grand total.

在下一列中有一些公式，可以从里程表中查找数据，而另一个公式则显示总计。

查找公式 (The Lookup Formula)

To do the mileage lookup, the "Miles" column has an INDEX/MATCH/MATCH formula in each row. Read more about INDEX and MATCH on my website.

要进行里程查询，“ Miles”列中的每一行都有一个INDEX / MATCH / MATCH公式。 在我的网站上了解有关INDEX和MATCH的更多信息。

Here is the formula in cell C5:

这是单元格C5中的公式：

=IFERROR(INDEX($H$4:$Q$13, MATCH(B4,$G$4:$G$13,0), MATCH(B5,$H$3:$Q$3,0)),"")

= IFERROR(INDEX($ H $ 4：$ Q $ 13，MATCH(B4，$ G $ 4：$ G $ 13,0)，MATCH(B5，$ H $ 3：$ Q $ 3,0))，“”)

• The INDEX function returns a value from H4:Q13 (outlined in blue)
  INDEX函数从H4：Q13返回一个值(蓝色轮廓线)
• The first MATCH function returns the row of the first city (in B4), from the vertical list of cities in G4:G13 (purple)
  第一个MATCH函数从G4：G13(紫色)的垂直城市列表中返回第一个城市的行(在B4中)
• The second MATCH function returns the column of the second city (in B5), from the horizontal list of cities in H3:Q3 (red)
  第二个MATCH函数从H3：Q3中的城市水平列表(红色)返回第二个城市的列(在B5中)

NOTE: The IFERROR function puts an empty string in the Miles cell, if it can't calculate the distance.

注意 ：如果无法计算距离，则IFERROR函数会在Miles单元格中放置一个空字符串。

The distance from Gainesville to Jacksonville is 68 miles.

盖恩斯维尔到杰克逊维尔的距离是68英里。

获取总距离 (Get the Total Distance)

The lookup formula from cell C5 is copied down to C9, to calculate the distance for each leg of the trip.

将来自单元格C5的查找公式向下复制到C9，以计算行程的每个行程的距离。

Then, in cell C11, there is a SUM function, to calculate the total miles for the trip.

然后，在单元格C11中，存在一个SUM函数 ，以计算旅程的总里程。

=SUM(C5:C9)

= SUM(C5：C9)

获取里程查询工作簿 (Get the Mileage Lookup Workbook)

To get the Mileage Lookup with Total Distance workbook, go to the Excel Sample Files page on my Contextures site. In the Functions section, look for FN0055 -Total Travel Distance Mileage Chart

若要获取带有“总距离的里程查询”工作簿，请转到Contextures网站上的“ Excel示例文件”页面。 在“功能”部分中，查找FN0055-总行驶距离里程表

The zipped file is in xlsx format, and does not contain macros.

压缩文件为xlsx格式，不包含宏。

翻译自: https://contexturesblog.com/archives/2018/06/14/excel-mileage-lookup-with-total-distance/

excel里程dk格式