跳到主要內容

如何在Microsoft Excel中對動態數據進行排序?

假設您正在Excel中管理固定商店的存儲數據,並且需要在存儲數據發生更改時自動對其進行排序。 如何在Excel中自動對動態存儲數據進行排序? 本文將向您展示一種巧妙的方式來對Excel中的動態數據進行排序,並在原始數據一次更改時自動保持排序更新。

使用公式對Excel中的綜合數據進行排序


使用公式對Excel中的綜合數據進行排序

1.在原始數據的開頭插入一個新列。 在這裡,我在原始數據之前插入列號,如下圖所示:

2.按照我們的示例,輸入公式 = RANK(C2,C $ 2:C $ 6) 在單元格A2中按存儲的原始產品進行排序,然後按 Enter 鍵。

3.繼續選擇單元格A2,拖動 填充手柄 向下到單元格A6,以獲取“編號”列中的所有其餘編號。

4:複製原始數據的標題,然後將其粘貼到原始表旁邊,例如E1:G1。 在“所需編號”列中,插入與編號順序相同的序列號,例如1、2…。 看截圖:

5.輸入公式 = VLOOKUP(E2,A $ 2:C $ 6,2,FALSE) 進入單元格F2,然後按 Enter 鍵。

此公式將查找所需NO的值。 在原始表格中,並在單元格中顯示相應的產品名稱。

備註:如果在“產品”列或“存儲”列中顯示重複或平局,則最好應用此功能 =IFERROR(VLOOKUP(E2,A$2:C$6,2,FALSE), VLOOKUP(E2,A$2:C$6,2,TRUE))

6.繼續選擇單元格F2,將填充手柄向下拖動到單元格F6以獲取所有產品名稱,並繼續選擇範圍F2:F6,向右拖動填充手柄至範圍G2:G6以獲取所有存儲號。

然後,您將獲得一個新的存儲表,按存儲順序降序排列,如下圖所示:

假設您的固定商店再購買145支筆,現在您總共有200支筆。 只需修改筆存儲的原始表,您就會在眨眼間看到新表已更新,請參見以下屏幕截圖:

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (49)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
hi,, what if there will be more than to numbers that were ties?
This comment was minimized by the moderator on the site
Wonderful....Thanks this helped me
This comment was minimized by the moderator on the site
I had lost the ability to sort several rows of information on one page of a spreadsheet with many pages. After trying to fix the situation by reworking formulas none of the information will sort now. Can I fix this without having to recreate page that is the problem. Same information needs to be transferred to another page that contains all information from all the pages; this too stops sort there. Big problem and don't know how to solve it.
This comment was minimized by the moderator on the site
i need to convert a number into another format.lets take an example - no =12394567 i need to convert it into 674501239. its simple (last 2 digit in first ,then 2nd last 2 digits then add a zero(0) then first four digits in the last. can you please guys tell me the formula for this?
This comment was minimized by the moderator on the site
=CONCATENATE(RIGHT(A1,2),MID(A1,5,2),"0",LEFT(A1,4)) will get you that order of digits.
This comment was minimized by the moderator on the site
you can use the CONCATENATE and then LEFT or RIGHT in excel formula. Its easy, Got it ?
This comment was minimized by the moderator on the site
Sure, mail me the detail and I will show you the finished product
This comment was minimized by the moderator on the site
Hi, no problem, email me and I will show hulu how the finished product works.
This comment was minimized by the moderator on the site
Hi Dear, I have a sheet with 100 items , each item has a specific cost that happens in one year .I want to create another sheet that shows only the item that has cost in one year automatically ? for example all of the item with item No. and description and cost in 2016 and then for 2017 and so on... thanks
This comment was minimized by the moderator on the site
how can i sort lowest to highest value (a to z) in your example i.e lowest storage to highest storage. i use your formulla '=RANK(C2,C$2:C $6) + COUNTIF(C$2:C2, "="&C2)-1'" & "=IFERROR(VLOOKUP(E2,A$2:C$6,2,FALSE), VLOOKUP(E2,A$2:C$6,2,TRUE))" . al is going well but i cannot sort lowest to highest.
This comment was minimized by the moderator on the site
Kedirech, you rock!! The COUNT thing eliminates ties!! I've had this problem for years but now this is the solution!! Thanks!!
This comment was minimized by the moderator on the site
Hi, I do it with if statements alone, how do I let you look at my file?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations