excel项目计划_使用Excel计划您的聚会座位

excel项目计划

If you're having a party this weekend, you can plan your party seating with Excel. Get this sample Excel seating workbook, enter the guest names on the Lists sheet, then fill the tables by selecting names from data validation drop down lists. After you've assigned a guest to a table, that guest's name disappears from the drop down lists, so you can't accidentally assign a guest to two different seats.

如果您本周末要参加派对，则可以使用Excel计划派对座位。 获取此示例Excel座位工作簿，在“列表”表上输入宾客姓名，然后通过从数据验证下拉列表中选择姓名来填写表格。 在将客人分配到一张桌子后，该客人的名字将从下拉列表中消失，因此您不会意外地将一个客人分配给两个不同的座位。

NOTE: There is a newer seating plan here: Excel Seating Plan with Charts

注意 ：这里有一个更新的座位图： 带图表的Excel座位图

Excel座位表 (Excel Seating Plan Tables)

Here's what a seating plan table looks like, after the guest names are selected.

这是选择来宾姓名后的座位计划表的外观。

输入来宾姓名 (Enter the Guest Names)

On a worksheet named Lists, the guest names are typed in column B. Sort the names in alphabetical order, so the names will be easier to find in the data validation drop down lists.

在名为Lists的工作表上，来宾名称在B列中键入。按字母顺序对名称进行排序，因此将更容易在数据验证下拉列表中找到这些名称。

In the sample workbook, you can clear out those names, and enter the names of your guests.

在示例工作簿中，您可以清除这些名称，然后输入来宾的名称。

画桌子和椅子 (Draw the Tables and Chairs)

On a sheet named TablePlan, use the Excel drawing tools to create the tables and chairs. In the sample workbook, there are 3 tables, with 8 chairs at each table.

在名为TablePlan的工作表上，使用Excel绘图工具创建桌子和椅子。 在示例工作簿中，有3张桌子，每张桌子有8张椅子。

Tip: To draw a circle, hold the Shift key while you use the Oval shape drawing tool.

提示：要绘制圆，请在使用椭圆形图形绘制工具时按住Shift键。

You can number and colour code the tables, to make it easier to keep track of things.

您可以对表进行编号和颜色编码，以便更轻松地跟踪事物。

列出桌子和座位 (List the Tables and Seats)

To the left of the table diagram, there is a list of the tables and seat numbers. The tables have the same colour coding as the tables, but that isn't required -- it just makes them easier to match up.

表格图的左侧有表格和座位号的列表。 表格具有与表格相同的颜色编码，但这不是必需的-只是使它们更易于匹配。

In the next column, there are drop down lists where you'll select the guest names. The drop downs were created with data validation.

在下一列中，有一个下拉列表，您可以在其中选择来宾姓名。 下拉列表是通过数据验证创建的。

座位图如何运作 (How the Seating Plan Works)

If you're interested in how the seating plan works, here are the details on the formulas and drop down lists.

如果您对座位计划的工作方式感兴趣，请参考以下公式和下拉列表的详细信息。

来宾清单检查 (Guest List Check)

On the Lists worksheet, there is a formula to check if the name in that row has been assigned a seat. The first name is in cell B1, and this formula is in cell C1:

在“列表”工作表上，有一个公式可以检查该行中的名称是否已分配座位。 名字在单元格B1中，此公式在单元格C1中：

=IF(COUNTIF(TablePlan!$D$2:$D$25,B1),"",ROW())

= IF(COUNTIF(TablePlan！$ D $ 2：$ D $ 25，B1)，“”，ROW())

That formula was copied down to the last name.

该公式被复制为姓氏。

If a guest has been assigned to a seat, their name will appear in column D on the TablePlan sheet. In that case, this formula will return an empty string. If a seat hasn't been assigned, the formula will return the row number.

如果已将客人分配给座位，则他们的名字将出现在TablePlan表的D列中。 在这种情况下，此公式将返回一个空字符串。 如果尚未分配座位，则公式将返回行号。

未分配客人名单 (List of Unassigned Guests)

In column D, there is an array formula to list the unassigned names. This list is used for the data validation drop down lists.

在D列中，有一个数组公式列出未分配的名称。 该列表用于数据验证下拉列表。

To create this array formula, cells D1:D24 were selected. Then, this formula was typed, and array entered (press Ctrl+Shift+Enter)

为了创建此数组公式，选择了单元格D1：D24。 然后，键入此公式，并输入数组(按Ctrl + Shift + Enter)

=IF(ROW(B1:B24)-ROW(B1)+1>COUNT(C1:C24),"", INDEX(B:B, SMALL(C1:C24,ROW(INDIRECT("1:"& ROWS(B1:B24))))))

= IF(ROW(B1：B24)-ROW(B1)+1> COUNT(C1：C24)，“”，INDEX(B：B，SMALL(C1：C24，ROW(INDIRECT(“ 1：”＆ROWS( B1：B24))))))

This is a multi-cell array formula (by DanielM.) that moves blank cells to the bottom of the list. For more information on this formula, see Excel Data Validation – Hide Previously Used Items.

这是一个多单元格数组公式(由DanielM。提出)，将空白单元格移到列表的底部。 有关此公式的更多信息，请参见Excel数据验证–隐藏先前使用的项目 。

In the screenshot below, some of the guest have been assigned to seats, and their names don't appear in column D.

在下面的屏幕截图中，一些来宾已被分配到座位，他们的名字没有出现在D列中。

命名范围 (Named Range)

Next, a dynamic named range was created for the unassigned guests list -- it will automatically grow or shrink as guest names are assigned to tables. Here are the steps for creating that named range

接下来，为未分配的来宾列表创建了一个动态的命名范围-当来宾名称分配给表时，它将自动增大或缩小。 以下是创建该命名范围的步骤

1. On the Excel Ribbon, click the Formulas tab
   在Excel功能区上，单击“公式”选项卡
2. Click Define Name
   单击定义名称
3. In the New Name dialog box, type NameCheck as the name
   在“新名称”对话框中，键入NameCheck作为名称
4. In the Refers to box, type this OFFSET formula, then click OK
   在“引用”框中，键入此OFFSET公式，然后单击“确定”。

=OFFSET(Lists!$D$1,0,0,COUNTA(Lists!$D$1:$D$24) -COUNTBLANK(Lists!$D$1:$D$24),1)

= OFFSET(清单！$ D $ 1,0,0，COUNTA(清单！$ D $ 1：$ D $ 24)-COUNTBLANK(清单！$ D $ 1：$ D $ 24)，1)

数据验证下拉列表 (Data Validation Drop Down Lists)

Next, the drop down lists were created, with these steps:

接下来，使用以下步骤创建下拉列表：

1. On the TablePlan sheet, select the cells for Guest names, cells D2:D25 in this example.
   在TablePlan表上，选择来宾名称的单元格，在此示例中为单元格D2：D25。
2. On the Excel Ribbon, click the Data tab
   在Excel功能区上，单击“数据”选项卡
3. Click Data Validation
   点击数据验证
4. In the Data Validation dialog box, from the Allow drop down, select List
   在“数据验证”对话框中，从“允许”下拉列表中，选择“列表”
5. In the Source box, type:  =NameCheck
   在“源”框中，键入：= NameCheck
6. Click OK
   点击确定

After the drop down lists are added to the cells, you can select a guest name.

将下拉列表添加到单元格后，您可以选择一个来宾名称。

将座位链接到客人 (Link Seats to Guests)

To show the guest names on the assigned seats, the Seat shapes were each linked to one of the Guest name selection cells. Here are the steps for that:Click on the shape for Seat 1 at Table 1.

为了在分配的座位上显示来宾姓名，将“座位”形状分别链接到“来宾姓名”选择单元格之一。 为此，请执行以下步骤：单击表1上座椅1的形状。

1. Click in the Formula Bar
   在编辑栏中单击
2. Type an equal sign, then click on the guest list, where the Table 1 Seat 1 name will be entered
   输入等号，然后单击来宾列表，在其中输入表1座位1的名称
3. Press Enter to complete the link
   按Enter键完成链接

Repeat these steps to link all the seats to the guest link cells.

重复这些步骤，将所有座位链接到访客链接单元。

分配客人座位 (Assign Guest Seats)

Now you can select guest names from the drop down lists on the TablePlan sheet, and get your party organized. Keep all those arguing relatives at separate tables, and everything should go well.

现在，您可以从TablePlan工作表的下拉列表中选择来宾姓名，并组织聚会。 将所有那些争吵的亲戚放在单独的桌子上，一切都会顺利进行。

下载样本文件 (Download the Sample File)

If you're planning your own party, you can download the sample file with an Excel seating arrangement example.

如果您要计划自己的聚会，则可以下载示例文件以及Excel座位安排示例。

NOTE: There is a newer seating plan here: Excel Seating Plan with Charts __________

注意 ：这里有一个更新的座位图： 带图表的Excel座位图 __________

翻译自: https://contexturesblog.com/archives/2010/03/05/plan-your-party-seating-with-excel/

excel项目计划