5 ms·

Finally! Every. Single. Time. I have been forced to use VLOOKUPs or INDEX/MATCH I wonder why nobody on the Excel team had still not built a new easier interface

Finally! Every. Single. Time. I have been forced to use VLOOKUPs or INDEX/MATCH I wonder why nobody on the Excel team had still not built a new easier interface. I wish I could only use Pandas but excel is the lingua franca of business.

z3c0 7y ago
It's almost impressive how long it's taken for Excel to comfortably mimic "SELECT Prefix FROM Whatever WHERE Country = 'Brazil'" I have no doubts why it's the de facto data tool, but this is laughably overdue.
fzumstein 7y ago
You can use Pandas via the xlwings package (https://www.xlwings.org https://www.xlwings.org)
cm2187 7y ago
Because nobody in the Excel team uses Excel and it shows.
- intern4tional 7y ago
  No, they just don't use it for your specific scenario. Disclaimer: Work at MS. We use Excel plenty, we just tend to do things differently than many other businesses as things that shouldn't be done in Excel we don't do in Excel (since we have other tooling available). The challenge is that in reality, people are diverse and will use Excel as long as they can as they don't want to buy other software, lack skills to implement it differently, or for some other reason need to keep data in Excel. Software like Excel has been around for so long and has so many edge cases in how it is used that the dev teams could spend 100% of their time adding features and still probably not touch some potentially large user base ones.
  - cm2187 7y ago
    I'd like to believe you but some of the beefs I have are not edge case at all. Top of my head, a few examples (in addition of the index/match): 1. the fx "Insert function" window, with a search feature that just doesn't work, a listbox that shows only 7 results and is not resizable (when the built-in functions alone is a huge list, plus all custom udf). Same thing for the Function Arguments window. 2. excel incapable of preserving the default for calculation mode (automatic/manual) between sessions 3. excel recalculating the whole sessions when opening a csv file even when calcs are on manual 4. terrible default formats (serious people don't want thick lines, 3d charts and all that crap) 5. Recent regression: can't copy a table from excel to powerpoint while preserving the format, formatting a table in powerpoint is a pain in the butt, default format are just bad 6. No way to link a powerpoint to a spreadsheet. Think all the bankers, consultants, engineers, who must present the result of some analysis, the data is in excel, the deliverable is a powerpoint deck, how do you link the two. Only ways I know are awefully manual and/or unstable and/or result in tables displayed as images in powerpoint 7. VBA not developped or improved since the late 90s (there was an interesting attempt at one point, VSTA, but it was canned) I could go into more edge case things but these are mass usage features.
    - dragonwriter 7y ago
      > VBA not developped or improved since the late 90s (there was an interesting attempt at one point, VSTA, but it was canned) VSTA was not canned [0], but core Office apps never supported it. IIRC, the only Office app that ever used (uses?) VSTA instead of VBA is InfoPath. VSTA was more of a replacement for VBA as an offering for other people (VBA, as well as being used in office, was available for others to use in their apps), who didn't have Office’s backward compatibility constraints. [0] VSTA 2019 SDK is available here: https://www.microsoft.com/en-us/download/details.aspx?id=58316&WT.mc_id=DX_MVP4025064 https://www.microsoft.com/en-us/download/details.aspx?id=583...
      - cm2187 7y ago
        I believe at that time there were talks about VSTA being made available in other parts of office, living alongside VBA. It never happened but I thought that was a good way forward to phase out VBA.
  - Bostonian 7y ago
    Why does LINEST in Excel return regression coefficients in the OPPOSITE order of the columns of data? Could you at least add an optional boolean argument that returns them in the same order as the columns?
sin7 7y ago
pandas is inconsistent and verbose. Isn't SQL a better alternative? Or even dplyr? Heck, I would learn one of those array languages such as J or APL rather than use pandas.
- skrebbel 7y ago
  > pandas is inconsistent and verbose. Isn't SQL a better alternative? Wait, there's a thing that's more inconsistent and verbose than SQL?
  - ramraj07 7y ago
    How is SQL inconsistent?
    - jasode 7y ago
      The syntax and grammar of SQL commands do not have an elegant symmetry. Examples: The ordering is not always name of table and then names of columns... Write data: UPDATE customer SET membership_plan='PLATINUM' Read data (incorrect syntax if specifying table name then column name): SELECT customer GET membership_plan -- incorrect Read data (correct syntax of columns then table): SELECT membership_plan FROM customer For UPDATE, the column names are adjacent to the value with the '=' equals sign. However the INSERT statement splits column names away from the values; all column names as one delimited list in parentheses and then all the values are another delimited list: UPDATE customer SET membership_plan='PLATINUM', timezone='UTC' INSERT INTO customer (membership_plan,timezone) values ('PLATINUM','UTC') Prepositions... Prepositions before table name: SELECT FROM t, INSERT INTO t, DELETE FROM t No preposition before table name: UPDATE t For syntax consistency, the UPDATE would also have had a preposition such as "UPDATE TO t", or "UPDATE ON t", or "UPDATE ONTO t" ... or ... the other 3 commands would have removed the need for "FROM/INTO". Either way, all 4 SUDI commands could have looked more alike. Yes, one eventually gets used to the cosmetic inconsistencies but it's annoying for beginners because memorizing 1 of the 4 commands doesn't really reward you with the ability to predict the grammar of the other 3 commands. You have to learn the different quirks of the 4 commands. Maybe we should be grateful that SELECT/UPDATE/DELETE at least all happen to share the same "WHERE" clause. It seems like SQL's inconsistent syntax design should have tortured us with memorizing "WHERE" for one command and totally different synonyms such as "FILTER" and "CONDITION" for the others! [To downvoters, I'm willing to be persuaded that I'm wrong and that SQL syntax is actually "consistent" so please reply with your counterargument.]

z3c0 7y ago

It's almost impressive how long it's taken for Excel to comfortably mimic "SELECT Prefix FROM Whatever WHERE Country = 'Brazil'" I have no doubts why it's the de facto data tool, but this is laughably overdue.

fzumstein 7y ago

You can use Pandas via the xlwings package (https://www.xlwings.org https://www.xlwings.org)

cm2187 7y ago

Because nobody in the Excel team uses Excel and it shows.

intern4tional 7y ago
No, they just don't use it for your specific scenario. Disclaimer: Work at MS. We use Excel plenty, we just tend to do things differently than many other businesses as things that shouldn't be done in Excel we don't do in Excel (since we have other tooling available). The challenge is that in reality, people are diverse and will use Excel as long as they can as they don't want to buy other software, lack skills to implement it differently, or for some other reason need to keep data in Excel. Software like Excel has been around for so long and has so many edge cases in how it is used that the dev teams could spend 100% of their time adding features and still probably not touch some potentially large user base ones.
- cm2187 7y ago
  I'd like to believe you but some of the beefs I have are not edge case at all. Top of my head, a few examples (in addition of the index/match): 1. the fx "Insert function" window, with a search feature that just doesn't work, a listbox that shows only 7 results and is not resizable (when the built-in functions alone is a huge list, plus all custom udf). Same thing for the Function Arguments window. 2. excel incapable of preserving the default for calculation mode (automatic/manual) between sessions 3. excel recalculating the whole sessions when opening a csv file even when calcs are on manual 4. terrible default formats (serious people don't want thick lines, 3d charts and all that crap) 5. Recent regression: can't copy a table from excel to powerpoint while preserving the format, formatting a table in powerpoint is a pain in the butt, default format are just bad 6. No way to link a powerpoint to a spreadsheet. Think all the bankers, consultants, engineers, who must present the result of some analysis, the data is in excel, the deliverable is a powerpoint deck, how do you link the two. Only ways I know are awefully manual and/or unstable and/or result in tables displayed as images in powerpoint 7. VBA not developped or improved since the late 90s (there was an interesting attempt at one point, VSTA, but it was canned) I could go into more edge case things but these are mass usage features.
  - dragonwriter 7y ago
    > VBA not developped or improved since the late 90s (there was an interesting attempt at one point, VSTA, but it was canned) VSTA was not canned [0], but core Office apps never supported it. IIRC, the only Office app that ever used (uses?) VSTA instead of VBA is InfoPath. VSTA was more of a replacement for VBA as an offering for other people (VBA, as well as being used in office, was available for others to use in their apps), who didn't have Office’s backward compatibility constraints. [0] VSTA 2019 SDK is available here: https://www.microsoft.com/en-us/download/details.aspx?id=58316&WT.mc_id=DX_MVP4025064 https://www.microsoft.com/en-us/download/details.aspx?id=583...
    - cm2187 7y ago
      I believe at that time there were talks about VSTA being made available in other parts of office, living alongside VBA. It never happened but I thought that was a good way forward to phase out VBA.
- Bostonian 7y ago
  Why does LINEST in Excel return regression coefficients in the OPPOSITE order of the columns of data? Could you at least add an optional boolean argument that returns them in the same order as the columns?

sin7 7y ago

pandas is inconsistent and verbose. Isn't SQL a better alternative? Or even dplyr? Heck, I would learn one of those array languages such as J or APL rather than use pandas.

skrebbel 7y ago
> pandas is inconsistent and verbose. Isn't SQL a better alternative? Wait, there's a thing that's more inconsistent and verbose than SQL?
- ramraj07 7y ago
  How is SQL inconsistent?
  - jasode 7y ago
    The syntax and grammar of SQL commands do not have an elegant symmetry. Examples: The ordering is not always name of table and then names of columns... Write data: UPDATE customer SET membership_plan='PLATINUM' Read data (incorrect syntax if specifying table name then column name): SELECT customer GET membership_plan -- incorrect Read data (correct syntax of columns then table): SELECT membership_plan FROM customer For UPDATE, the column names are adjacent to the value with the '=' equals sign. However the INSERT statement splits column names away from the values; all column names as one delimited list in parentheses and then all the values are another delimited list: UPDATE customer SET membership_plan='PLATINUM', timezone='UTC' INSERT INTO customer (membership_plan,timezone) values ('PLATINUM','UTC') Prepositions... Prepositions before table name: SELECT FROM t, INSERT INTO t, DELETE FROM t No preposition before table name: UPDATE t For syntax consistency, the UPDATE would also have had a preposition such as "UPDATE TO t", or "UPDATE ON t", or "UPDATE ONTO t" ... or ... the other 3 commands would have removed the need for "FROM/INTO". Either way, all 4 SUDI commands could have looked more alike. Yes, one eventually gets used to the cosmetic inconsistencies but it's annoying for beginners because memorizing 1 of the 4 commands doesn't really reward you with the ability to predict the grammar of the other 3 commands. You have to learn the different quirks of the 4 commands. Maybe we should be grateful that SELECT/UPDATE/DELETE at least all happen to share the same "WHERE" clause. It seems like SQL's inconsistent syntax design should have tortured us with memorizing "WHERE" for one command and totally different synonyms such as "FILTER" and "CONDITION" for the others! [To downvoters, I'm willing to be persuaded that I'm wrong and that SQL syntax is actually "consistent" so please reply with your counterargument.]