چالش کاربران قدرت اکسل

مدرس Oz du Soleil

انتشار 2020/10/05

به‌روز‌رسانی 2025/09/22

مدت زمان 9h 9m

سطح پیشرفته

فایل تمرینی دارد

آموزش Excel Power User Challenge

آیا راه خود را در اکسل می شناسید؟ آیا می خواهید مهارت های خود را آزمایش کنید؟ این مجموعه دو هفته‌ای پیشرفته اکسل با Excel MVP Oz du Soleil تمرین‌های پیچیده‌ای را ارائه می‌دهد که می‌تواند شما را به یک کاربر قدرتمند با حسن نیت تبدیل کند. چالش ها از تجربه دنیای واقعی اوز به عنوان یک کاربر قدیمی اکسل، مربی اکسل و مشاور هوش تجاری استخراج شده است. موضوعات شامل فرمول ها و توابع، قالب بندی شرطی، جداول محوری، Power Query، عیب یابی، و موارد دیگر است.nn

Data Analysis Microsoft Excel

Welcome

Excel Power User Challenge ()

Excel Power User Challenge

Teaching Unpivot ()

Payday and holidays ()

Calculate who's late ()

Three friends, one vacation ()

Can this data be useful? ()

Compare attendance lists ()

Remove no-shows ()

Sandwich shop combo ()

Customer graph ()

Reimbursement limit ()

Expense summary ()

Stop the clopen ()

Isolate errant expenses ()

Identify odd-numbered teams ()

Filter and stack flight data ()

Fix the FILTER formula ()

Thinking about Venns and joins ()

Tally wins and losses across worksheets ()

Conditional formatting and paid fees ()

Sort moons ()

Bottom-up XLOOKUP ()

Match pizza items ()

Assign rooms ()

Match Zodiac Signs ()

Errant choices ()

Group and count payments ()

Diving school payment ()

Who bought none? ()

Partner advances ()

Hours and ratings ()

PIVOTBY and filter school data ()

Find the side-by-side duplicates ()

Create a drop-down list with a warning ()

Two source drop-down ()

Pick from the available songs ()

Build a histogram ()

Set up a Power Query transformation table ()

Desired jersey numbers ()

Look here or look there ()

Match the airport with its city ()

Create a dual axis graph ()

Determine the longest winning streak ()

Execute an anti-join ()

Count based on cell color ()

How many outfits can be made? ()

Find the break-even point ()

Calculate revenue for a sold-out show ()

Extract email domains ()

Identify the months that require two payments ()

Capture the essence of anti-joins ()

Custom sorting for work shifts ()

Set up a workbook to split data onto different sheets ()

List the missing numbers ()

Sort the teams by team name ()

Retrieve from a partial string ()

Splitting and merging codes using PowerQuery and TEXTJOIN ()

Calculate the area of a stage for dancers ()

How many states has Elsie been to? ()

Import from a broken file ()

Which of the pairs are anagrams? ()

Calculate the nth mile ()

Highlight every fifth person in the list ()

How many trios are in this band? ()

Separate the names from professional designations ()

Calculate the next Monday after a given date ()

Identify cells that have strikethrough ()

Which room is bigger? ()

Running totals, splits, and times ()

What percentage of your home is the home office? ()

Correct the Conditional Formatting ()

Assign the team leads ()

Annual gala ticket sales ()

Format cells to show degrees in Celsius and Fahrenheit ()

List every Tuesday in 2022 except for holidays ()

Find and follow the instructions ()

Assign transactions to months ()

Extract from the stack ()

Round times to the nearest 20 minutes ()

Power Query split and pivot ()

Which words end in vowels? ()

Find any duplicate or missing names ()

Filter addresses with the FILTER function ()

Create a dynamic drop-down list and suppress 0 values ()

Graph the population and format large numbers ()

Look up customer data with XLOOKUP ()

Retrieve school mascots from the column ()

Convert records from a matrix to a single column ()

Convert a report so it can be sorted and filtered ()

Combine a dataset with a Power Query conversion table ()

The strange case of copy and paste from Word to Excel ()

Reformat this list ()

This query used to work—what's wrong? ()

Pair each student with every other student ()

Unstack this list so that it's useful ()

Three ways to retrieve data ()

Extract the competition winners ()

Two data sets, one relationship, one PivotTable ()

Unwind this report ()

Sort titles and ignore A, An, The ()

Stack and summarize vendor data ()

Create data validation that only accepts pairs ()

Who has an assignment and who doesn't? ()

Stack this data using Power Query ()

Pizza party calculation ()

Sort dates by day and month to find the birthday with the most people ()

Who owes what for the beach house vacation? ()

How many full and partial orders can be filled from this inventory? ()

Create a histogram from the donation data ()

Import from folder and tally attendances ()

Restructure the data and write formulas to calculate bonuses ()

Add spin buttons to compare products ()

Beautify form with images and print settings ()

Write a SUMIFS formula for three criteria ()

Parse and summarize data to tally votes ()

Format cells to create ID numbers ()

Write a formula for a two-way lookup ()

Set up a document to show low-level alerts ()

Check this data quality for results that don't fit requirements ()

Troubleshoot this query that has a peculiar result ()

Deliveries and PivotTables ()

01_02_Please_fix_this_query.zip (21 KB)

01_01_Deliveries_and_Pivot_Tables.zip (1.1 MB)

01_05_2_Way_Lookup.zip (10 KB)

01_03_Check_data_quality.zip (11 KB)

01_04_Create_Low_Level_Alerts.zip (12 KB)

01_06_Format_cells_to_create_ID_Numbers.zip (9 KB)

02_04_Add_spin_buttons.zip (9 KB)

02_01_Count_Votes.zip (12 KB)

02_03_Beautify_form_with_images.zip (1.4 MB)

02_02_SUMIFS_3_criteria.zip (10 KB)

02_05_Calculate_Bonuses.zip (10 KB)

02_06_Import_from_a_folder.zip (10 KB)

02_07_Attendance.zip (51 KB)

03_01_Donation.zip (10 KB)

03_02_Orders.zip (621 KB)

03_03_Beach.zip (14 KB)

03_04_Birthday.zip (13 KB)

03_05_Pizza.zip (2.3 MB)

03_06_Stack.zip (809 KB)

04_01_Assignment.zip (531 KB)

04_02_Data_Validation.zip (8 KB)

04_03_Append.zip (10 KB)

04_04_Sort_Titles.zip (9 KB)

04_05_Unwind.zip (9 KB)

04_06_Relationships.zip (60 KB)

05_01_Retrieve_the_competition_winners.zip (96 KB)

05_02_3_ways_to_retrieve_data.zip (13 KB)

05_03_Unstack.zip (9 KB)

05_04_Pair_these_students.zip (12 KB)

05_05_Fix_this_carpark_query.zip (26 KB)

05_06_Reformat_this_list.zip (12 KB)

06_01_Discussion_groups.zip (34 KB)

06_02_Reference_table_assignments.zip (18 KB)

06_03_Flatten_this_table.zip (57 KB)

06_04_Stack_this_list_of_schools.zip (264 KB)

06_05_Retrieve_data_from_this_column.zip (458 KB)

06_06_Customers_latest_visit.zip (976 KB)

07_01_Graph_the_population_over_70_years.zip (47 KB)

07_02_Dynamic_dropdown_lists_for_buildings.zip (51 KB)

07_03_FILTER_addresses.zip (15 KB)

07_04_Find_duplicate_names.zip (709 KB)

07_05_Word_counts.zip (152 KB)

12_01_Sort_by_Mascot.zip (559 KB)

12_02_List_the_missing_numbers.zip (367 KB)

12_03_Disaggregate_the_Data.zip (67 KB)

12_04_Custom_Sort.zip (111 KB)

12_05_Anti_joins.zip (31 KB)

12_06_Monthly_Payments.zip (22 KB)

13_01_Extract_email_domains.zip (34 KB)

13_02_Calculate_ticket_revenue.zip (34 KB)

13_03_Find_the_break_even_point.zip (31 KB)

11_06_Retrieve_from_partial_string_of_text.zip (29 KB)

13_04_Create_outfit_combinations.zip (62 KB)

13_06_Execute_an_anti_join.zip (9 KB)

14_01_Winning_Streaks.zip (7 KB)

14_02_Dual_Axis.zip (49 KB)

14_03_Airports.zip (78 KB)

14_04_Look_Here_Or_There.zip (56 KB)

14_05_Two_Source_Dropdown.zip (11 KB)

14_06_Jersey_Numbers.zip (41 KB)

15_01_Transformation_Table.zip (363 KB)

15_02_Dropdown_That_Warns_But_Doesnt_Prevent.zip (8 KB)

13_05_Count_volunteers.zip (113 KB)

11_05_Split_the_codes.zip (82 KB)

11_04_The_band_needs_dancers.zip (56 KB)

11_03_Where_Elsie_has_been.zip (51 KB)

07_06_Power_Query_Split_and_Pivot.zip (200 KB)

08_01_Round_to_20_minutes.zip (865 KB)

08_02_Extract_from_the_stack.zip (256 KB)

08_03_Match_entries_and_months.zip (26 KB)

08_04_Find_the_instructions_and_execute.zip (562 KB)

08_05_List_every_Tuesday.zip (294 KB)

08_06_Celsius_and_fahrenheit.zip (140 KB)

09_01_Tickets_Sales.zip (79 KB)

09_02_Pick_the_winner.zip (9 KB)

09_03_Correct_the_Conditional_Formatting.zip (8 KB)

09_04_Home_Office.zip (12 KB)

09_05_Raqeemas_10_mile_run.zip (124 KB)

09_06_Find_the_largest_rooms.zip (66 KB)

10_01_Count_the_strikethroughs.zip (25 KB)

10_02_Find_the_next_Monday.zip (183 KB)

10_03_Separate_names_and_designations.zip (45 KB)

10_04_How_many_trios.zip (57 KB)

10_05_Highlight_every_5th_name.zip (147 KB)

10_06_Calculate_the_nth_mile.zip (32 KB)

11_01_Anagrams.zip (207 KB)

11_02_Import_from_broken_file.zip (57 KB)

15_03_Build_a_Histogram.zip (24 KB)

15_04_Pick_The_Available_Song.zip (470 KB)

15_05_Find_Side_By_Side_Duplicates.zip (145 KB)

15_06_PIVOTBY_Schools.zip (27 KB)

Hours_and_Ratings.zip (57 KB)

Partner_Advances.zip (10 KB)

Who_Bought_None.zip (281 KB)

Driving_School_Payments.zip (212 KB)

Group_and_Count_Payments.zip (211 KB)

Errant_Choices.zip (172 KB)

17_01_Match_Signs.zip (9 KB)

17_02_Assign_Rooms.zip (181 KB)

17_03_Match_Pizza_Items.zip (147 KB)

17_04_XLOOKUP_Bottom_Up.zip (97 KB)

17_05_Custom_Sort_Moons.zip (89 KB)

17_06_CF_Paid_Fees.zip (96 KB)

18_03_UniqueToppings.zip (117 KB)

18_04_PreferenceMismatch.zip (3.0 MB)

18_05_CountTeamMembers.zip (187 KB)

18_06_Reimbursements.zip (60 KB)

19_01_Stop_the_Clopen.zip (204 KB)

18_01_HomeGames.zip (167 KB)

19_02_Expense_Summary.zip (29 KB)

19_03_Reimbursement_Limit.zip (345 KB)

19_04_Customer_Graph.zip (612 KB)

19_05_Sandwich_Shop_Combo.zip (606 KB)

19_06_Remove_No_Shows.zip (24 KB)

20_01_Compare_Attendance_Lists.zip (58 KB)

20_02_Dirty_Data_Use.zip (508 KB)

20_03_Group_Vacation.zip (176 KB)

20_04_Late_Arrivals.zip (249 KB)

20_05_Paydays_and_Holidays.zip (243 KB)

20_06_Unpivot_Lesson.zip (183 KB)

دانلود فایل فشرده

با توجه به امکانات آموزش و همچنین امکانات بسته انتخاب شده لینک دانلود فایل فشرده آماده خواهد شد. با در نظر داشتن این شرایط لطفا بسته مورد نظر خود را انتخاب کرده و روی دکمه درخواست لینک دانلود کلیک کنید

در حال به روزرسانی اطلاعات

درخواست لینک دانلود

در حال به روزرسانی اطلاعات

لطفا قبل از فعالسازی لینک دانلود به موارد زیر توجه کنید:

پسورد فایل‌های فشرده است.
لینک‌های آماده شده تا 8 روز پس از فعالسازی منقضی خواهند شد.
حجم فایل‌ها تخمینی هستند.
در صورتی که لینک دانلود تا 15دقیقه پس از درخواست آماده نشد، از بخش پشتیبانی پیگیری نمایید.