වගු දෙකක් සංසන්දනය කිරීම

අපට වගු දෙකක් ඇත (උදාහරණයක් ලෙස, මිල ලැයිස්තුවේ පැරණි සහ නව අනුවාද), ඒවා සංසන්දනය කර ඉක්මනින් වෙනස්කම් සොයා ගත යුතුය:

නව මිල ලැයිස්තුවට යමක් එකතු කර ඇති බව වහාම පැහැදිලි වේ (දින, සුදුළූණු ...), යමක් අතුරුදහන් වී ඇත (බ්ලැක්බෙරි, රාස්ප්බෙරි ...), සමහර භාණ්ඩ සඳහා මිල වෙනස් වී ඇත (අත්තික්කා, කොමඩු ...). ඔබ මෙම සියලු වෙනස්කම් ඉක්මනින් සොයා ප්රදර්ශනය කළ යුතුය.

එක්සෙල් හි ඕනෑම කාර්යයක් සඳහා, සෑම විටම පාහේ විසඳුම් එකකට වඩා තිබේ (සාමාන්‍යයෙන් 4-5). අපගේ ගැටළුව සඳහා, විවිධ ප්රවේශයන් භාවිතා කළ හැකිය:

• ක්රියාව VPR (VLOOKUP) - පැරණි එකේ නව මිල ලැයිස්තුවෙන් නිෂ්පාදන නම් සොයන්න සහ නව මිල අසල පැරණි මිල පෙන්වන්න, ඉන්පසු වෙනස්කම් අල්ලා ගන්න
• ලැයිස්තු දෙකක් එකකට ඒකාබද්ධ කර එය මත පදනම්ව විවර්තන වගුවක් සාදන්න, එහිදී වෙනස්කම් පැහැදිලිව පෙනෙනු ඇත
• Excel සඳහා Power Query Add-in භාවිතා කරන්න

අපි ඒවා සියල්ලම පිළිවෙලට ගනිමු.

ක්රමය 1. VLOOKUP ශ්රිතය සමඟ වගු සංසන්දනය කිරීම

ඔබ මෙම අපූරු විශේෂාංගය ගැන සම්පූර්ණයෙන්ම නුහුරු නුපුරුදු නම්, පළමුව මෙහි බලන්න සහ ඒ පිළිබඳ වීඩියෝ නිබන්ධනයක් කියවන්න හෝ බලන්න - ඔබේ ජීවිතය වසර දෙකක කාලයක් ඉතිරි කර ගන්න.

සාමාන්‍යයෙන්, මෙම ශ්‍රිතය යම් පොදු පරාමිතියක් ගැලපීමෙන් එක් වගුවකින් තවත් වගුවකට දත්ත ඇද ගැනීමට භාවිතා කරයි. මෙම අවස්ථාවේදී, අපි පැරණි මිල නව මිලට තල්ලු කිරීමට එය භාවිතා කරන්නෙමු:

#N/A දෝෂයට එරෙහිව එම නිෂ්පාදන, පැරණි ලැයිස්තුවේ නැත, එනම් එකතු කරන ලදී. මිල වෙනස්වීම් ද පැහැදිලිව දැකගත හැකිය.

කිහිප දෙනකුගේ මෙම ක්‍රමය: ඔවුන් පවසන පරිදි සරල සහ පැහැදිලි, "ප්‍රභේදයේ සම්භාව්‍ය". එක්සෙල් හි ඕනෑම අනුවාදයක ක්‍රියා කරයි.

අවාසි එහි ද වේ. නව මිල ලැයිස්තුවට එකතු කරන ලද නිෂ්පාදන සෙවීම සඳහා, ඔබට එම ක්‍රියා පටිපාටියම ප්‍රතිවිරුද්ධ දිශාවට සිදු කිරීමට සිදුවනු ඇත, එනම් VLOOKUP ආධාරයෙන් පැරණි මිලට නව මිල ගණන් අදින්න. හෙට මේසවල ප්‍රමාණය වෙනස් වුවහොත් සූත්‍ර සකස් කිරීමට සිදුවනු ඇත. හොඳයි, සහ ඇත්ත වශයෙන්ම විශාල මේස මත (> පේළි 100), මේ සියලු සතුට යහපත් ලෙස මන්දගාමී වනු ඇත.

ක්රමය 2: විවර්තනයක් භාවිතයෙන් වගු සංසන්දනය කිරීම

අපි අපේ වගු එකකට යටින් පිටපත් කරමු, මිල ලැයිස්තුවේ නම සහිත තීරුවක් එකතු කරන්න, එවිට ඔබට පසුව තේරුම් ගත හැක්කේ කුමන ලැයිස්තුවෙන් කුමන පේළියක්ද යන්නයි:

දැන්, සාදන ලද වගුව මත පදනම්ව, අපි සාරාංශයක් සාදන්නෙමු ඇතුල් කරන්න - විවර්තන වගුව (ඇතුළු කරන්න - විවර්තන වගුව). අපි පිට්ටනියක් විසි කරමු නිෂ්පාදන රේඛා ප්රදේශයට, ක්ෂේත්රයට මිල තීරු ප්රදේශයට සහ ක්ෂේත්රයට Цena පරාසය තුළට:

ඔබට පෙනෙන පරිදි, විවර්තන වගුව ස්වයංක්‍රීයව පැරණි සහ නව මිල ලැයිස්තු වලින් සියලුම නිෂ්පාදනවල සාමාන්‍ය ලැයිස්තුවක් ජනනය කරයි (පුනරාවර්තන නැත!) සහ නිෂ්පාදන අකාරාදී පිළිවෙලට වර්ග කරයි. එකතු කරන ලද නිෂ්පාදන (ඒවායේ පැරණි මිල නැත), ඉවත් කරන ලද නිෂ්පාදන (ඒවායේ නව මිල නැත) සහ මිල වෙනස්වීම් තිබේ නම් ඔබට පැහැදිලිව දැකගත හැකිය.

එවැනි වගුවක විශාල එකතුව අර්ථවත් නොවන අතර, ඒවා ටැබ් එක මත අක්රිය කළ හැකිය ඉදිකිරීම්කරු - විශාල එකතුව - පේළි සහ තීරු සඳහා අබල කරන්න (නිර්මාණය - විශාල එකතුව).

මිල වෙනස් වන්නේ නම් (නමුත් භාණ්ඩ ප්‍රමාණය නොවේ!), එවිට එය මත දකුණු-ක්ලික් කිරීමෙන් සාදන ලද සාරාංශය යාවත්කාලීන කිරීම ප්‍රමාණවත් වේ - නැවුම් කරන්න.

කිහිප දෙනකුගේ: මෙම ප්‍රවේශය VLOOKUP ට වඩා විශාල වගු සමග වේගවත් විශාලත්වයේ අනුපිළිවෙලකි. 

අවාසි: ඔබ එකිනෙකා යටතේ ඇති දත්ත අතින් පිටපත් කර මිල ලැයිස්තුවේ නම සහිත තීරුවක් එක් කළ යුතුය. වගු වල ප්‍රමාණය වෙනස් වුවහොත්, ඔබ සියල්ල නැවත කළ යුතුය.

ක්රමය 3: බල විමසුම සමඟ වගු සංසන්දනය කිරීම

Power Query යනු Microsoft Excel සඳහා නොමිලේ ලැබෙන ඇඩෝනයකි, එය ඔබට ඕනෑම මූලාශ්‍රයකින් පාහේ Excel වෙත දත්ත පැටවීමට සහ මෙම දත්ත ඕනෑම ආකාරයකට පරිවර්තනය කිරීමට ඉඩ සලසයි. Excel 2016 හි, මෙම ඇඩෝනය දැනටමත් ටැබය මත පෙරනිමියෙන් ගොඩනගා ඇත දත්ත (දත්ත), සහ Excel 2010-2013 සඳහා ඔබ එය Microsoft වෙබ් අඩවියෙන් වෙන වෙනම බාගත කර ස්ථාපනය කළ යුතුය - නව ටැබ් එකක් ලබා ගන්න බල විමසුම.

අපගේ මිල ලැයිස්තු Power Query වෙත පූරණය කිරීමට පෙර, ඒවා ප්‍රථමයෙන් ස්මාර්ට් වගු බවට පරිවර්තනය කළ යුතුය. මෙය සිදු කිරීම සඳහා, දත්ත සමඟ පරාසය තෝරන්න සහ යතුරු පුවරුවේ සංයෝජනය ඔබන්න Ctrl+T හෝ පීත්ත පටිය මත ටැබ් එක තෝරන්න නිවස - මේසයක් ලෙස හැඩතල ගැන්වීම (මුල් පිටුව - වගුවක් ලෙස ආකෘතිය). සාදන ලද වගු වල නම් ටැබ් එක මත නිවැරදි කළ හැක ඉදිකිරීම්කරු (මම සම්මතයෙන් ඉවත් වන්නෙමි වගුව 1 и වගුව 2, පෙරනිමියෙන් ලබා ගන්නා).

බොත්තම භාවිතයෙන් Power Query හි පැරණි මිල පූරණය කරන්න වගුව/පරාසයෙන් (වගුව/පරාසයෙන්) ටැබ් එකෙන් දත්ත (දිනය) හෝ ටැබ් එකෙන් බල විමසුම (Excel අනුවාදය අනුව). පූරණය කිරීමෙන් පසුව, අපි විධානය සමඟ Power Query වෙතින් නැවත Excel වෙත ආපසු යමු වසන්න සහ පූරණය කරන්න - වසා දමා පූරණය කරන්න... (වසන්න සහ පූරණය කරන්න - වසන්න සහ පූරණය කරන්න...):

… සහ දිස්වන කවුළුව තුළ තෝරන්න සම්බන්ධතාවයක් සාදන්න (සම්බන්ධතාවය පමණි).

නව මිල ලැයිස්තුව සමඟ එයම නැවත කරන්න. 

දැන් අපි තුන්වන විමසුමක් සාදන්නෙමු එය පෙර දෙකේ දත්ත ඒකාබද්ධ කර සංසන්දනය කරයි. මෙය සිදු කිරීම සඳහා, ටැබය මත Excel තෝරන්න දත්ත - දත්ත ලබා ගන්න - ඉල්ලීම් ඒකාබද්ධ කරන්න - ඒකාබද්ධ කරන්න (දත්ත - දත්ත ලබා ගන්න - විමසුම් ඒකාබද්ධ කරන්න - ඒකාබද්ධ කරන්න) හෝ බොත්තම ඔබන්න සංයුක්ත (ඒකාබද්ධ) ටැබ් බල විමසුම.

සම්බන්ධ වීමේ කවුළුව තුළ, පතන ලැයිස්තුවේ අපගේ වගු තෝරන්න, ඒවායේ ඇති භාණ්ඩවල නම් සහිත තීරු තෝරන්න, සහ පහළින්, සම්බන්ධ වීමේ ක්‍රමය සකසන්න - සම්පූර්ණ බාහිර (සම්පූර්ණ පිටත):

ක්ලික් කිරීමෙන් පසු OK තීරු තුනක වගුවක් දිස්විය යුතු අතර, තුන්වන තීරුවේ ශීර්ෂයේ ඇති ද්විත්ව ඊතලය භාවිතයෙන් කැදැලි වගු වල අන්තර්ගතය පුළුල් කළ යුතුය:

එහි ප්‍රතිඵලයක් වශයෙන්, අපි වගු දෙකෙන්ම දත්ත ඒකාබද්ධ කිරීම ලබා ගනිමු:

ඇත්ත වශයෙන්ම, වඩාත් තේරුම්ගත හැකි ඒවා මත දෙවරක් ක්ලික් කිරීමෙන් ශීර්ෂයේ තීරු නම් නැවත නම් කිරීම වඩා හොඳය:

දැන් වඩාත්ම සිත්ගන්නා සුළුය. ටැබ් එකට යන්න තීරුව එක් කරන්න (තීරුව එක් කරන්න) සහ බොත්තම මත ක්ලික් කරන්න කොන්දේසි සහිත තීරුව (කොන්දේසි සහිත තීරුව). ඉන්පසු විවෘත වන කවුළුව තුළ, ඒවායේ අනුරූප නිමැවුම් අගයන් සමඟ පරීක්ෂණ කොන්දේසි කිහිපයක් ඇතුළත් කරන්න:

එය ක්ලික් කිරීමට ඉතිරිව ඇත OK සහ එම බොත්තම භාවිතයෙන් ලැබෙන වාර්තාව Excel වෙත උඩුගත කරන්න වසා බාගත කරන්න (වසන්න සහ පූරණය කරන්න) ටැබ් මුල් පිටුව (නිවස):

අලංකාරය.

එපමණක් නොව, අනාගතයේදී මිල ලැයිස්තුවේ යම් වෙනසක් සිදු වුවහොත් (රේඛා එකතු කර හෝ මකා දමනු ලැබේ, මිල වෙනස් වේ, ආදිය), එවිට අපගේ ඉල්ලීම් යතුරුපුවරු කෙටිමඟක් සමඟ යාවත්කාලීන කිරීම පමණක් ප්‍රමාණවත් වේ. Ctrl+alt+F5 හෝ බොත්තම මගින් සියල්ල නැවුම් කරන්න (සියල්ල නැවුම් කරන්න) ටැබ් දත්ත (දිනය).

කිහිප දෙනකුගේ: සමහර විට සියල්ලටම වඩා ලස්සන හා පහසුම මාර්ගය. විශාල වගු සමඟ දක්ෂ ලෙස ක්රියා කරයි. වගු ප්‍රමාණය වෙනස් කිරීමේදී අතින් සංස්කරණය කිරීම් අවශ්‍ය නොවේ.

අවාසි: Power Query add-in (Excel 2010-2013 හි) හෝ Excel 2016 ස්ථාපනය කිරීම අවශ්‍ය වේ. මූලාශ්‍ර දත්තවල තීරු නාම වෙනස් නොකළ යුතුය, එසේ නොමැතිනම් අපට “එවැනි සහ එවැනි තීරුවක් හමු නොවීය!” යන දෝෂය ලැබෙනු ඇත. විමසුම යාවත්කාලීන කිරීමට උත්සාහ කරන විට.

• Power Query භාවිතයෙන් ලබා දී ඇති ෆෝල්ඩරයක ඇති සියලුම Excel ගොනු වලින් දත්ත රැස් කරන්නේ කෙසේද
• Excel හි ලැයිස්තු දෙකක් අතර ගැලපීම් සොයා ගන්නේ කෙසේද
• අනුපිටපත් නොමැතිව ලැයිස්තු දෙකක් ඒකාබද්ධ කිරීම