Design of spreadsheet functions for working with tables of data

• US 7,698,287 B2
• Filed: 09/30/2004
• Issued: 04/13/2010
• Est. Priority Date: 09/30/2004
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Niemisto, Juha P., Chirilov, Joseph M., Becker, Andrew J., Collie, Robert C., Ellis, Charles D., Androski, Matthew J., Peyton-Jones, Simon, Hawking, Robert G.
• Primary Examiner(s): Alam; Shahid A
• Assistant Examiner(s): Bullock; Joshua
• Application Number:
  US10/957,502
• Publication Number:
  US 20060075328A1
• Time in Patent Office: 2,021 Days
• Field of Search: 707/101, 707/3, 707/7, 707/503, 715/503
• US Class Current: 1/1
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

The present invention provides several database formulas that can manipulate large sets of data. Each database formula is customizable and repeatable without user intervention. Thus, users may enter numerous options that can cover the user'"'"'s desire for data output in certain forms or formats, select a set of input data, and consistently receive a set of output data. In some embodiments, the user may refer to the set of data semantically instead of specifying a range of cells to use in the database formula. Referring to the data semantically allows the database formulas to adjust to changes in the range of cells new data may occupy.

Claims

1. A computer-implemented method for manipulating a table of data in a spreadsheet application, the method comprising:

receiving one or more database functions within a cell of the spreadsheet application;

receiving a selection for input data comprising one or more cells of the table of data, wherein the input data resides within the spreadsheet application;

manipulating the input data according to the one or more database functions, wherein manipulating comprises;

determining that the one or more database functions can be applied row-by-row;

and

applying the determined database functions to each row separately, wherein prior to applying the determined database functions, the determined database functions are ordered based on processing efficiency; and

presenting output data in the spreadsheet application, wherein the output data is anchored in the cell in which the one or more database functions are received, wherein the output data is presented as an array of data comprising more than one cell of the spreadsheet application, and wherein the array is separate from the table of data.

2. A method according to claim 1, wherein one or more of the database functions includes one or more arguments.

3. A method according to claim 2, wherein the one or more arguments include a semantic reference.

4. A method according to claim 2, wherein the user provides the one or more arguments.

5. A method according to claim 1, wherein the input data is a table of data.

6. A method according to claim 1, wherein the output data is a table of data.

7. A method according to claim 1, wherein presenting the output data causes one more or more items of pre-existing data to move.

8. A method according to claim 1, wherein the database function is one of a filter function, a sort function, a remove duplicates function, a table append function, or a add column function.

9. A method according to claim 8, wherein the filter function further comprises:

comparing at least one item of data to a filter argument; and

returning output data without any item from the input data that meets a filter argument.

10. A method according to claim 8, wherein the sort function further comprises:

returning an organized set of output data according to one or more sort arguments.

11. A method according to claim 8, wherein the duplicate removal function further comprises:

determining that two items of data in the input data, designated in an argument, are the same;

returning output data, wherein one of the two items of like data are removed.

12. A method according to claim 8, wherein the table append function further comprises:

retrieving two sets of input data;

returning output data, wherein at least a portion of the first set of input data is appended to at least a portion of the second set of input data.

13. A method according to claim 8, wherein the column add function further comprises:

creating a column of data from the input data according to one or more arguments;

returning output data, wherein the output data includes the input data and the column of data.

14. A method according to claim 1, further comprising receiving one or more arguments with the one or more database functions and wherein manipulating the input data further comprises:

determining that the one or more arguments can be applied row-by-row;

applying the determined arguments to each row separately, wherein prior to applying the determined arguments, the determined arguments are ordered based on processing efficiency; and

applying any remaining arguments or database functions.

15. A method according to claim 14, wherein a first intermediate set of data is created from applying the arguments and database functions to each row separately.

16. A method according to claim 15, wherein one or more subsequent intermediate sets of data are created because of each remaining argument or database function applied to the first intermediate set of data or to one or more of the subsequent intermediate sets of data.

17. A computer storage medium having computer-executable instructions for performing steps comprising:

receiving one or more database functions within a cell of a spreadsheet application;

receiving a selection for input data comprising one or more cells of the table of data,

wherein the input data resides within the spreadsheet application;

manipulating the input data according to the one or more database functions, wherein manipulating comprises;

determining that the one or more database functions can be applied row-by-row; and

applying the determined database functions to each row separately, wherein prior to applying the determined database functions, the determined

database functions are ordered based on processing efficiency; and

presenting output data in the spreadsheet application, wherein the output data is anchored in the cell in which the one or more database functions are received, wherein the output data is presented as an array of data comprising more than one cell of the spreadsheet application, and wherein the array is separate from the table of data.

18. A computer storage medium according to claim 17, wherein one or more of the database functions includes one or more arguments.

19. A computer storage medium according to claim 18, wherein the one or more arguments include a semantic reference.

20. A computer storage medium according to claim 18, wherein the user provides the one or more arguments.

21. A computer storage medium according to claim 17, wherein the input data is a table of data.

22. A computer storage medium according to claim 17, wherein the output data is a table of data.

23. A computer storage medium according to claim 17, wherein presenting the output data causes one more or more items of pre-existing data to move.

24. A computer storage medium according to claim 17, wherein the database function is one of a filter function, a sort function, a remove duplicates function, a table append function, or a add column function.

25. A computer storage medium according to claim 24, wherein the filter function further comprises:

comparing at least one item of data to a filter argument; and

returning output data without any item from the input data that meets a filter argument.

26. A computer storage medium according to claim 24, wherein the sort function further comprises:

returning an organized set of output data according to one or more sort arguments.

27. A computer storage medium according to claim 24, wherein the duplicate removal function further comprises:

determining that two items of data in the input data, designated in an argument, are the same;

returning output data, wherein one of the two items of like data are removed.

28. A computer storage medium according to claim 24, wherein the table append function further comprises:

retrieving two sets of input data;

returning output data, wherein at least a portion of the first set of input data is appended to at least a portion of the second set of input data.

29. A computer storage medium according to claim 24, wherein the column add function further comprises:

creating a column of data from the input data according to one or more arguments;

returning output data, wherein the output data includes the input data and the column of data.

30. A computer storage medium according to claim 17, further comprising receiving one or more arguments with the one or more database functions and

wherein manipulating the input data further comprises:

determining that the one or more arguments can be applied row-by-row;

applying the determined arguments to each row separately, wherein prior to applying the determined arguments, the determined arguments are ordered based on processing efficiency; and

applying any remaining arguments or database functions.

31. A computer storage medium according to claim 30, wherein a first intermediate set of data is created from applying the arguments and database functions to each row separately.

32. A computer storage medium according to claim 31, wherein one or more subsequent intermediate sets of data are created because of each remaining argument or database function applied to the first intermediate set of data or to one or more of the subsequent intermediate sets of data.

33. A system for manipulating a table of data in a spreadsheet application, comprising:

a processor;

a memory, communicatively coupled to the processor and including instructions that, when executed by the processor, perform the following method;

receiving a selection of one of one or more database functions, including one or more arguments, wherein the one or more database functions are received within a cell of the spreadsheet application;

receiving a selection of input data comprising one or more cells of the table of data, wherein the input data resides within the spreadsheet application;

parsing out the one or more arguments and the one or more database functions;

determining that one or more arguments or one or more database functions can be applied row-by-row;

applying the determined arguments and database functions to each row separately, wherein prior to applying the arguments or database functions, the arguments or functions are ordered based on processing efficiency;

applying any remaining arguments or database functions; and

presenting output data in the spreadsheet application, wherein the output data is anchored in the cell in which the one or more database functions are received, wherein the output data is presented as an array of data comprising more than one cell of the spreadsheet application, and wherein the array is separate from the table of data.

34. A system according to claim 33, wherein a first intermediate set of data is created from applying the arguments and database functions to each row separately.

35. A system according to claim 34, wherein one or more subsequent intermediate sets of data are created because of each remaining argument or database function applied to the first intermediate set of data or to one or more of the subsequent intermediate sets of data.

36. A computer storage medium having computer-executable instructions for performing steps comprising:

receiving a selection of one of one or more database functions, including one or more arguments, wherein the one or more database functions are received within a cell of the spreadsheet application;

receiving a selection of input data comprising one or more cells of the spreadsheet application, wherein the input data resides within the spreadsheet application;

parsing out the one or more arguments and the one or more database functions;

determining that one or more arguments can be applied row-by-row to the selection of input data;

applying the determined arguments to each row separately, wherein prior to applying the determined arguments, the determined arguments are ordered based on processing efficiency;

applying any remaining arguments; and

presenting the output data in the spreadsheet, wherein the output data is anchored in the cell in which the one or more database functions are received, wherein the output data is presented as an array of data comprising more than one cell of the spreadsheet application, and wherein the array is separate from the table of data.

37. A computer storage medium according to claim 36, wherein a first intermediate set of data is created from applying the arguments and database functions to each row separately.

38. A computer storage medium according to claim 37, wherein one or more subsequent intermediate sets of data are created because of each remaining argument or database function applied to the first intermediate set of data or to one or more of the subsequent intermediate sets of data.

39. In a computer system having a graphical user interface including a display device and a user interface selection device, a computer-implemented method of receiving one or more database functions to apply to a set of input data, comprising the steps of:

receiving a selection of input data comprising one or more cells of the spreadsheet application, wherein the input data resides within a spreadsheet application;

receiving one or more database functions to apply to the selection of input data, wherein the one or more database function are received in one or more cells of the spreadsheet application;

determining that the one or more database functions can be applied row-by-row to the selection of input data; and

separately applying the one or more database functions to each row of the selection of input data, wherein prior to separately applying the one or more database functions, the one or more database functions are ordered based on processing efficiency; and

displaying output data after the one or more database functions have been applied to the input data, wherein the output data is displayed in a spreadsheet application and anchored in the one or more cells of the spreadsheet application that received the one or more database functions, wherein the output data is presented as an array of data comprising more than one cell of the spreadsheet application, and wherein the array is separate from the table of data.

40. A method according to claim 39, further comprising receiving one or more arguments with the one or more database functions, wherein the arguments adjust how the database functions are applied.

41. A method according to claim 40, wherein one or more of the arguments includes a column token.

42. A method according to claim 40, wherein one or more of the arguments includes a mathematical expression.

43. A method according to claim 40, wherein one or more of the arguments is semantic.

44. A method according to claim 39, wherein two or more of the database functions are received as embedded database formulas.

45. A method according to claim 39, wherein the output data occupies two or more cells in the spreadsheet.