Cube
Excel 2007
◉ 5 views
=CUBESET()
Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Excel.
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016Excel for iPadExcel Web AppExcel for iPhoneExcel for Android tabletsExcel for Android phones
=CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])
Hover argument names in the table for details.
Output typeMixed
Popularity20 / 100
VolatileNo
Dynamic ArrayNo
📥 Download sample .xlsx — Coming soon
▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =CUBESET() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
🧠 🧠 Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
connection |
Text | Required | A text string of the name of the connection to the cube. |
set_expression |
Text | Required | A text string of a set expression that results in a set of members or tuples. Set_expression can also be a cell reference to an Excel range that contains one or more members, tuples, or sets included in the set. |
caption |
Text | Optional | A text string that is displayed in the cell instead of the caption, if one is defined, from the cube. |
sort_order |
Number | Optional | The type of sort, if any, to perform and can be one of the following: |
the |
Number | Optional | default value is 0. An alpha sort for a set of tuples sorts on the last element in each tuple. For more information on these different sort orders, see the Microsoft Office SQL Analysis Services help system. |
sort_by |
Text | Optional | A text string of the value by which to sort. For example, to get the city with the highest sales, set_expression would be a set of cities, and sort_by would be the sales measure. Or, to get the city with the highest population, set_expression would be a set of cities, and sort_by would be the population measure. If sort_order requires sort_by, and sort_by is omitted, CUBESET returns the #VALUE! error message. |
▣ Examples
مثال مجموعه مرتب‌شده محصولات
=CUBESET("Finance","Order([Product].[Product].[Product Category].Members,[Measures].[Unit Sales],ASC)","Products")
مثال مجموعه فرزندان همه محصولات با مرتب‌سازی بر اساس مبلغ فروش
=CUBESET("Sales","[Product].[All Products].Children","Products",1,"[Measures].[Sales Amount]")
✦ Tips
- When the CUBESET function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
⚠ Common Errors
#NAME?
If the connection name is not a valid workbook connection stored in the workbook, CUBESET returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBESET returns a #NAME? error value.
Fix: Check the connection name in the Connections section
#N/A
If the set_expression syntax is incorrect or the set contains at least one member with a different dimension than the other members, CUBESET returns a #N/A error value. CUBESET may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)
Fix: نحو عبارت مجموعه و سازگاری ابعاد را بررسی کنید و از ارجاع به اشیای مبتنی بر جلسه خودداری کنید.
#VALUE!
If set_expression is longer than 255 characters, which is the limit for an argument to a function, CUBESET returns a #VALUE! error value. If sort_order requires sort_by, and sort_by is omitted, CUBESET returns the #VALUE! error message.
Fix: برای رشته‌های طولانی از مرجع سلول استفاده کنید و در صورت نیاز به مرتب‌سازی، مقدار sort_by را وارد کنید.
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!