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.

6 Siyaabaha Looga Saaro Nuqullada Google-ka

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.

Diiwaanka "Ajit" waxa la galaa 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.

Guji liiska ku calaamadsan "Data".  Guji ka saar nuqullada si aad u tirtirto diiwaannada nuqulka ah

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 ) .

Guji badhanka ku calaamadsan "Ka saar nuqullada"

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) .

Xaashiyaha Google ayaa kugula socodsiin doona tirada diiwaannada nuqulka ah ee la tirtiray

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.

Xaashiyaha Google 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.)

Xaashiyaha Google-ku waxa ay liis garayn doonaan diiwaannada gaarka ah ee aad ku qortay qaaciddada

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.

Halka unugga C2, geli caanaha

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.)

Isla marka aad ku dhufatid furaha Gelida, waxay muujin doontaa natiijada inay tahay BEEN

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) .

Xaashiyaha Google ayaa si toos ah u koobi doona qaacidada unugyada hadhay

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.)

Laga soo bilaabo liiska qaabka, hoos u yara dhaadhac si aad u doorato qaabeynta shuruuda ah

2. Guji unugyada qaabka haddii…(Format cells if…) sanduuqa hoos-u-dhaca, oo dooro ikhtiyaarka Qaanuunka Gaarka(Custom Formula ) ah.

Guji unugyada Qaabka haddii… sanduuqa hoos u dhaca

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 .

Dooro qaacidada gaarka ah oo geli qaacidada sida COUNTIF(A$2:A2, A2)>1

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).)

Xaashiyaha Google ayaa iftiimin doona gelitaannada soo noqnoqda ( nuqul ka mid ah)

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 .

Gudaha Google Sheets, ka hel liiska lagu magacaabo "Add-ons" oo dhagsii ikhtiyaarada "Hel add-ons"

2. Dooro astaanta Launch (oo lagu iftiimiyay shaashadda) si aad u bilowdo G-Suite Marketplace .

Dooro astaanta Launch (oo lagu iftiimiyay sawirka) si aad u bilowdo G-Suite Marketplace

3. Hadda raadi Add-on-ka(Add-on) aad u baahan tahay oo ku dheji.

Raadi add-on aad u baahan tahay oo guji

4. U gudub sharraxaadda add-onka haddii aad rabto ka dibna dhagsii( click on the Install) ikhtiyaarka Ku rakib.

Guji ikhtiyaarka lagu rakibo

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:)

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.



About the author

Waxaan ahay injineer software iyo sahamiye. Waxaan khibrad u leeyahay labada Microsoft Xbox 360 iyo Google Explorer. Waxaan awoodaa inaan bixiyo talooyinka khabiirada ee qalabyada horumarinta software-ka qaarkood, iyo sidoo kale inaan caawiyo dadka inay cilad-saaraan khaladaadka Explorer ee caadiga ah.



Related posts