可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

I'm trying to get Excel to work out if a cell contains a certain courier company (DHL, DPD etc) and I want it to give out the number of miles to the nearest drop-off point. It works but it gives me a text answer instead of a number, this means autosum won't work. How do I change the code to give a numeric answer? Cheers Brian

    =IF(
        ISNUMBER(SEARCH("collect",Q2,1))=TRUE,"1", 
        IF(ISNUMBER(SEARCH("dhl",Q2,1))=TRUE,"2.6", 
        IF(ISNUMBER(SEARCH("DPD",Q2,1))=TRUE,"1", 
        IF(ISNUMBER(SEARCH("inpost",Q2,1))=TRUE,"1.2", 
        IF(ISNUMBER(SEARCH("hermes",Q2,1))=TRUE,"0.6",  
        IF(ISNUMBER(SEARCH("royal",Q2,1))=TRUE,"0.5", 
        IF(ISNUMBER(SEARCH("force",Q2,1))=TRUE,"0.5", 
        IF(ISNUMBER(SEARCH("ups",Q2,1))=TRUE,"0.6",  
        IF(ISNUMBER(SEARCH("yodel",Q2,1))=TRUE,"0", 
        IF(ISNUMBER(SEARCH("collection",Q2,1))=TRUE,"0",
    "No"))

回答1:

=IF
  (ISNUMBER(SEARCH("collection",Q2,0))=TRUE,1, 
  IF(ISNUMBER(SEARCH("dhl",Q2,1))=TRUE,2.6, 
  IF(ISNUMBER(SEARCH("DPD",Q2,1))=TRUE,1, 
  IF(ISNUMBER(SEARCH("inpost",Q2,1))=TRUE,1.2, 
  IF(ISNUMBER(SEARCH("hermes",Q2,1))=TRUE,0.6,  
  IF(ISNUMBER(SEARCH("royal",Q2,1))=TRUE,0.5, 
  IF(ISNUMBER(SEARCH("force",Q2,1))=TRUE,0.5, 
  IF(ISNUMBER(SEARCH("ups",Q2,1))=TRUE,0.6,  
  IF(ISNUMBER(SEARCH("yodel",Q2,1))=TRUE,0, 
0))))))))

回答2:

If I followed your question correctly, this should work. It has the advantage that you can extend the list easily to add other transporters and update distances.

You could use index() with match() in cell B3 instead of vlookup if you wish:

=IFERROR(INDEX($E$5:$E$13,MATCH(A3,$D$5:$D$13,0)),"not in list")

I used data validation to pick from the list in cell A3.

回答3:

I just copied your formula in accordance with the comments, and removed =TRUE:

=IF
  (ISNUMBER(SEARCH("collection",Q2,1)),1, 
  IF(ISNUMBER(SEARCH("dhl",Q2,1)),2.6, 
  IF(ISNUMBER(SEARCH("DPD",Q2,1)),1, 
  IF(ISNUMBER(SEARCH("inpost",Q2,1)),1.2, 
  IF(ISNUMBER(SEARCH("hermes",Q2,1)),0.6,  
  IF(ISNUMBER(SEARCH("royal",Q2,1)),0.5, 
  IF(ISNUMBER(SEARCH("force",Q2,1)),0.5, 
  IF(ISNUMBER(SEARCH("ups",Q2,1)),0.6,  
  IF(ISNUMBER(SEARCH("yodel",Q2,1)),0, 
0))))))))