6 Siyaabaha Looga Saaro Nuqullada Google-ka
Xaashiyaha faafintu(Spreadsheet) waa wax aan ahayn dukumeenti u habeeya xogta qaab saf iyo tiirar ah. Xaashiyaha faafinta(Spreadsheet) waxaa adeegsada ku dhawaad urur kasta oo ganacsi si uu u ilaaliyo xogtiisa oo uu u fuliyo hawlaha xogtaas. Xataa dugsiyada iyo koleejyada waxay isticmaalaan software-ka xisaabaadka si ay u ilaashadaan xogtooda. Marka ay timaado software-ka fidsan, Microsoft Excel iyo xaashida Google waa kuwa ugu darajada sarreeya software ee ay dad badani isticmaalaan. Maalmahan, isticmaaleyaal badan ayaa Google Sheets ka dooranaya Microsoft Excel maadaama ay ku kaydinayso xaashida faafinta kaydka daruurtooda(Cloud Storage) , tusaale Google Drivekaas oo laga heli karo meel kasta. Waxa kaliya ee loo baahan yahay waa in kombayutarkaagu lagu xidho internetka(Internet) . Waxyaabo kale oo weyn oo ku saabsan Google Sheets waa inaad ka isticmaali karto daaqada browserka ee kombayutarkaaga.
Marka ay timaado ilaalinta xogta gelitaanka, mid ka mid ah arrimaha caadiga ah ee ay la kulmaan isticmaaleyaal badan ayaa ah nuqul ama nuqullo nuqul ah. Tusaale ahaan, qiyaas in aad hayso tafaasiisha dadka laga soo ururiyay sahan. Markaad ku qorto iyaga oo isticmaalaya software-kaaga xaashida ah sida Google Sheets , waxaa jira suurtagalnimada diiwaanno nuqul ah. Taasi waa, hal qof ayaa laga yaabaa inuu buuxiyey sahanka in ka badan hal jeer, oo markaa Google Sheets ayaa liis gareyn doonta gelitaanka laba jeer. Galitaanka noocan oo kale ah ayaa ka dhib badan marka ay timaado ganacsiyada. Bal qiyaas(Imagine)haddii macaamil lacag caddaan ah la geliyo diiwaanka in ka badan hal mar. Markaad ku xisaabiso wadarta kharashaadka xogtaas, waxay noqon doontaa arrin. Si looga fogaado xaaladahan oo kale, waa in la hubiyo in aysan jirin diiwaanno nuqul ah oo ku jira xaashida faafinta. Sidee lagu gaari karaa tan? Hagaag(Well) , hagahan, waxaad kaga hadli doontaa 6 siyaabood oo kala duwan oo aad uga saari karto nuqulada Google Sheets . Kaalay(Come) , annagoon horudhac kale lahayn, aynu dulmarno mawduuca.
Sidee looga saaraa nuqullada Google-ka ee xaashiyaha?(How to Remove Duplicates in Google Sheets?)
Diiwaanada nuqul ka mid ah runtii waa dhib marka la eego ilaalinta diiwaannada xogta. Laakin uma baahnid werwerin maadaama aad si fudud uga saari karto gelitaannada nuqul ka mid ah xaashidaada Google(Google Sheets) -ka . Aan aragno siyaabo aad uga takhalusi karto nuqul ka mid ah xaashida Google(Google Sheets) -ka .
Habka 1: Isticmaalka Ka saar Xulashada nuqullada(Method 1: Using the Remove Duplicates Option)
Xaashiyaha Google(Google Sheets) wuxuu leeyahay ikhtiyaar ku-dhisan si meesha looga saaro gelinta soo noqnoqota (gelitaanka nuqul ka mid ah). Si aad u isticmaasho doorashadaas, raac sawirka hoose.
1. Tusaale ahaan, bal tan eeg (eeg sawirka hoose). Halkan waxaad ku arki kartaa in rikoorka "Ajit" la geliyey laba jeer. Kani waa rikoodh nuqul ah.
2. Si aad meesha uga saarto gelida nuqulka ah, dooro ama muuji safafka iyo tiirarka.( select or highlight the rows and columns.)
3. Hadda guji ikhtiyaarka menu ee ku calaamadsan Xogta(Data) . Hoos u dhaadhac ka dibna riix ka saar(Remove duplicates ) xulashada nuqullada.
4. Sanduuqa soo booda ayaa soo baxaya, kaas oo weydiinaya tiirarka la falanqeynayo. Dooro ikhtiyaarada sida baahidaadu tahay ka dibna riix badhanka ka saar nuqullada(Remove duplicates ) .
5. Dhammaan diiwaanada nuqul ka mid ah waa la tirtiri doonaa, waxaana sii jiri doona waxyaabo gaar ah. Xaashiyaha Google(Google Sheets) -ka ayaa ku weydiin doona tirada diiwaannada nuqulka ah ee la tirtiray(number of duplicate records that were eliminated) .
6. Xaaladeena, kaliya hal nuqul oo nuqul ah ayaa laga saaray ( Ajit ). Waxaad arki kartaa in Google Sheets ay meesha ka saartay gelida nuqulka ah (tixraac sawirka soo socda).
Habka 2: Ka saar Nuqullada Foomamka(Method 2: Remove Duplicates with Formulae)
Formula 1: UNIQUE
Google Sheets waxa ay leedahay qaacido lagu magacaabo UNIQUE oo xajisanaysa diiwaanada gaarka ah oo ka tirtiri doonta dhammaan gelida nuqul ka mid ah xaashidaada.
Tusaale ahaan: =UNIQUE(A2:B7)
1. Tani waxay hubin doontaa galitaanka nuqul ka mid ah unugyada la cayimay (A2: B7)(specified range of cells (A2:B7)) .
2. Guji unug kasta oo maran xaashidaada(Click on any empty cell on your spreadsheet) oo geli qaacidada sare. Xaashiyaha Google(Google Sheets) ayaa muujin doona kala duwanaanta unugyada aad sheegtay.
3. Google Sheets ayaa liis garayn doona diiwaanada gaarka ah ee aad ku qortay qaaciddada. Waxaad markaa ku beddeli kartaa xogta hore diiwaannada gaarka ah.(You can then replace the old data with the unique records.)
Formula 2: COUNTIF
Waxaad isticmaali kartaa qaacidadan si aad u muujiso dhammaan gelida nuqul ka mid ah xaashidaada.
1. Tusaale: Tixgeli sawirka soo socda ee ka kooban hal nuqul oo nuqul ah.
2. Sawirka kore ee unugga C2, aan u galno qaacidada sida, =COUNTIF(A$2:A2, A2)>1
3. Hadda, marka la riixo furaha Enter , waxay muujin doontaa natiijada BEEN.(FALSE.)
4. Dhaqaaq tilmaame jiirka oo dul dhig afargeeska yar( small square) ee qaybta hoose ee unugga la doortay. Hadda waxaad arki doontaa calaamada dheeriga ah halkii aad ka arki lahayd jiirkaaga. Guji(Click) oo hay sanduuqa, ka dibna u jiid ilaa unugga meesha aad rabto inaad ka hesho gelinta nuqullada ah. Xaashiyaha Google ayaa (Google)si toos ah u koobi doona qaacidada unugyada hadhay(automatically copy the formula to the remaining cells) .
5. Google Sheet wuxuu si toos ah ugu dari doonaa " RUN(TRUE) " hortiisa gelitaan nuqul ah.
FIIRO GAAR AH(NOTE) : Xaaladdan oo kale, waxaan ku qeexnay sida >1 (ka weyn 1). Markaa, xaaladani waxay keenaysaa RUN(TRUE ) meelaha laga helo gelitaanka in ka badan hal mar. Dhammaan meelaha kale, natiijadu waa BEEN.(FALSE.)
Habka 3: Ka saar Galinta Nuqulka ah oo leh qaab shuruudaysan(Method 3: Remove Duplicate Entries with Conditional Formatting)
Waxa kale oo aad isticmaali kartaa qaabaynta shuruuda ah si aad u tirtirto diiwaanada nuqulka ah ee Google Sheets .
1. Marka hore(First) , dooro xogta dejisan ee aad rabto inaad ku sameyso qaabaynta shuruudaha. Markaas, Menu ka dooro Format oo hoos u dhaadhac ka dibna dooro formatting Conditional.(Conditional formatting.)
2. Guji unugyada qaabka haddii…(Format cells if…) sanduuqa hoos-u-dhaca, oo dooro ikhtiyaarka Qaanuunka Gaarka(Custom Formula ) ah.
3. Geli qaacidada sida =COUNTIF(A$2:A2, A2)>1
Fiiro gaar ah:(Note:) Waxaad u baahan tahay inaad beddesho xogta safka iyo tiirka si waafaqsan xaashida Google-(Google Sheet) ka .
4. Qaaciddadani waxay ka shaandhaysaa diiwaanada tiirka A.
5. Guji badhanka la sameeyay . (Done)Haddii tiirka A uu ka kooban yahay diiwaanno nuqul ah(duplicate records) , Google Sheets ayaa muujin doona gelitaannada soo noqnoqda ( nuqul ka mid ah).( Google Sheets will highlight the repeated entries (duplicates).)
6. Hadda waxaad si fudud u tirtiri kartaa diiwaanadan nuqul ka mid ah.
Habka 4: Ka saar Diiwaanada Kooban ee Miisaska Pivot(Method 4: Remove Duplicate Records with Pivot Tables)
Maaddaama miisaska pivot ay yihiin kuwo si degdeg ah loo isticmaalo oo dabacsan, waxaad u isticmaali kartaa si aad u hesho oo aad u tirtirto diiwaannada nuqul ka ah Google Sheet kaaga .
Marka hore, waa inaad ku muujisaa xogta Google Sheet . Marka xigta(Next) , samee miiska pivot oo mar labaad muuji xogtaada. Si aad u abuurto miis pivot oo leh kaydka xogtaada, u gudub Xogta(Data ) ku hoos jirta liiska Google Sheet oo guji ikhtiyaarka miiska Pivot . (Pivot table)Waxaa lagugu soo dayici doonaa sanduuq lagu waydiinayo in aad samaynayso miiska pivot xaashida jira ama xaashi cusub. Dooro ikhtiyaar ku habboon oo sii wad.
Miiskaaga pivot ayaa la abuuri doonaa. Laga soo bilaabo guddiga dhinaca midig, ka dooro batoonka ku dar (Add )safafka(Rows) u dhow si aad ugu darto safafka. Meel u dhow qiyamka, dooro inaad kudarto tiir si aad u hubiso nuqulka qiyamka. Jadwalka pivot-kaaga ayaa ku taxaya qiyamka tirintooda (ie tirada jeer ee qiimihiisu ku jiro xaashidaada). Waxaad u isticmaali kartaa tan si aad u hubiso nuqulka galitaanka Google Sheet . Haddii tiradu ay ka badan tahay hal, taasi waxay la macno tahay gelitaanka waxaa lagu soo celceliyay wax ka badan hal mar xaashidaada.
Habka 5: Isticmaalka Qoraalka Apps(Method 5: Using Apps Script)
Habka kale ee ugu wanaagsan ee lagu baabi'in karo nuqul ka mid ah dukumeentigaaga waa adigoo isticmaalaya Apps Script . Hoos waxaa ku qoran qoraalka-app-script si looga takhaluso gelinta nuqul ka mid ah xaashidaada:
/** * remove duplicate rows from Google Sheets data range */ function removeDupRows() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('Sheet1'); // change the row number of your header row var startRow = 7; // get the data var range = sheet.getRange(startRow,1,sheet.getLastRow(),sheet.getLastColumn()).getValues(); // remove duplicates with helper function var dedupRange = arrayUnique(range); Logger.log(dedupRange); // check if duplicate sheet exists already, if not create new one if (ss.getSheetByName('Sheet1 Duplicates Removed')) { // case when dedup sheet already exists var dedupSheet = ss.getSheetByName('Sheet1 Duplicates Removed'); var lastRow = Math.max(dedupSheet.getLastRow(),1); var lastColumn = Math.max(dedupSheet.getLastColumn(),1); // clear out any previous de-duplicate data dedupSheet.getRange(1,1,dedupSheet.getLastRow(),dedupSheet.getLastColumn()).clear(); // replace with new de-duplicated data dedupSheet.getRange(1,1,dedupRange.length,sheet.getLastColumn()).setValues(dedupRange); } else { // case when there is no dedup sheet var dedupSheet = ss.insertSheet('Sheet1 Duplicates Removed',0); dedupSheet.getRange(1,1,dedupRange.length,dedupRange[0].length).setValues(dedupRange); } // make the de-duplicate sheet the active one dedupSheet.activate(); } /** * helper function returns a unique array */ function arrayUnique(arr) { var tmp = []; // filter out duplicates return arr.filter(function(item, index){ // convert row arrays to strings for comparison var stringItem = item.toString(); // push string items into temporary arrays tmp.push(stringItem); // only return the first occurrence of the strings return tmp.indexOf(stringItem) >= index; }); } You can also use the below function to add a custom menu to Google Sheets to remove duplicates so that you can easily use it. /** * add a menu to run a function from Sheet */ function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Remove duplicates') .addItem('Highlight duplicate rows','highlightDupRows') .addItem('Remove duplicate rows','removeDupRows') .addToUi(); }
Habka 6: Isticmaal ku-darka si aad uga saarto nuqullada Google-ka(Method 6: Use Add-on to Remove Duplicates in Google Sheets)
Isticmaalka wax-ku-darka si aad u tirtirto geliyaasha nuqul ka mid ah xaashidaada ayaa faa'iido leh. Dhawr kordhin noocan oo kale ah ayaa soo noqda inay waxtar leeyihiin. Mid ka mid ah barnaamijyada ku-kordhinta noocan oo kale ah waa ku- darka Abblebits(Ablebits) ee loogu magac daray " Ka saar(Remove Duplicates) Nuqullada ".
1. Fur Google Sheets, ka dib liiska Add-ons dhagsii ikhtiyaarka Get add-ons .
2. Dooro astaanta Launch (oo lagu iftiimiyay shaashadda) si aad u bilowdo G-Suite Marketplace .
3. Hadda raadi Add-on-ka(Add-on) aad u baahan tahay oo ku dheji.
4. U gudub sharraxaadda add-onka haddii aad rabto ka dibna dhagsii( click on the Install) ikhtiyaarka Ku rakib.
Aqbal oggolaanshaha lagama maarmaanka ah si aad u rakibto wax-ku-darka. Waxaa laga yaabaa inaad ku gasho aqoonsigaaga akoonkaaga Google . Kadib markaad rakibto ku-darka, waxaad si fudud uga saari kartaa nuqullada Google(Google Sheets) -ka .
lagu taliyay:(Recommended:)
- Sida loo tirtiro nasashada qaybta Microsoft Word(How to Delete a Section Break in Microsoft Word)
- 5 siyaabood oo loo Geli karo Calaamada Xididada Square ee Erayga(5 Ways to Insert a Square Root Symbol in Word)
- Sidee si degdeg ah loogu duubaa qoraalka xaashida Google?(How to Quickly Wrap Text in Google Sheets?)
Waxaan rajeyneynaa in macluumaadkani uu ahaa mid waxtar leh oo aad awooday inaad si fudud uga saartid gelinta nuqullada Google Sheets. (easily remove duplicate entries from Google Sheets.)Haddii aad wax talo ah ama su'aalo ah maskaxdaada ku hayso, isticmaal qaybta faallooyinka si aad u weydiiso.
Related posts
2 siyaabood oo loo beddelo Margins gudaha Google Docs
4 siyaabood oo loo rogo sawirka Google Docs
3 siyood oo loo cusboonaysiiyo Google Play Store [Force Update]
3 siyaabood oo loo wadaago gelitaanka Wi-Fi iyada oo aan la muujin erayga sirta ah
3 Siyaabood Oo Looga Saarayo Sawir-qaadista Mareegaha Kasta
8 Siyaabood Oo Lagu Hagaajiyo Khatarta Roobka 2 Ciyaartoy Badan Oo Aan Shaqeyn
Sida magacaaga loogu badalo Google Meet
3 siyaabood oo loo soo dejiyo fiidiyowyada Facebook ee iPhone
7 siyaabood oo lagu hagaajiyo iimaylka ku dheggan Outbox of Gmail
Sidee si degdeg ah loogu duubaa qoraalka xaashida Google?
Sida loo hubiyo socodka khariidadaha Google
Shilalka Google Chrome? 8 Siyaabo Fudud oo lagu hagaajiyo!
6 siyaabood oo lagu saxo Netflix Code Error Code M7121-1331-P7
Ka bax Gmail ama Koontada Google si toos ah ( Sawiro)
Sida loo hagaajiyo wax kamarad ah oo laga helin Google Meet (2022)
2 siyaabood oo aad ku nadiifin karto taariikhdaada wax baadhista Amazon
3 siyaabood oo loo Beddelayo Sawirka Xogta ee Spotify (Hagaha Degdega ah)
Sideen ku helaa Google Cloud? (2022)
Sida Loo Damiyo Raadinta Badbaadada Google
4 siyaabood oo loo hubiyo FPS (Frames per second) ee ciyaaraha