Skip to main content

Conas luach a thabhairt ar ais i gcill eile má tá téacs áirithe in Excel i gcill?

Mar an sampla a thaispeántar thíos, nuair a bhíonn an luach “Tá” i gcill E6, cuirfear an luach “ceadú” isteach go huathoibríoch ar chill F6. Má athraíonn tú “Tá” go “Ní hea” nó “Neodracht” in E6, athrófar an luach in F6 go “Diúltaigh” nó “Athbhreithnigh” láithreach. Conas is féidir leat a dhéanamh chun é a bhaint amach? Bailíonn an t-alt seo roinnt modhanna úsáideacha chun cabhrú leat é a réiteach go héasca.


Luach ar ais i gcill eile má tá téacs áirithe i bhfoirmle le cill

Chun luach a fháil ar ais i gcill eile mura bhfuil ach téacs ar leith i gcill, bain triail as an bhfoirmle seo a leanas le do thoil. Mar shampla, má tá “Tá” i B5, ansin seol “Ceadaigh” in D5, ar shlí eile, seol “Níl cáiliú” ar ais. Déan mar a leanas le do thoil.

Roghnaigh D5 agus cóipeáil thíos an fhoirmle isteach agus brúigh an Iontráil eochair. Féach an pictiúr:

Foirmle: Luach ar ais i gcill eile má tá téacs áirithe i gcill

= MÁ (ISNUMBER (CUARDACH ("Is ea",D5)), "Ceadaigh'"Níl cáiliú")

Nótaí:

1. San fhoirmle, “Is ea", D5, "Cheadú"Agus"Níl cáiliúTabhair le fios, má tá téacs “Tá” i gcill B5, go mbeidh téacs “ceadaigh” sa chill shonraithe, ar shlí eile, líonfar í le “Níl cáiliú”. Is féidir leat iad a athrú bunaithe ar do chuid riachtanas.

2. Chun luach a fháil ar ais ó chealla eile (mar shampla K8 agus K9) bunaithe ar luach cille sonraithe, bain úsáid as an bhfoirmle seo le do thoil:

= MÁ (ISNUMBER (CUARDACH ("Is ea",D5)),K8,K9)

Roghnaigh sraitheanna iomlána nó sraitheanna iomlána go héasca sa roghnú bunaithe ar luach cille i gcolún áirithe:

An Roghnaigh Cealla Sonracha fóntais de Kutools le haghaidh Excel is féidir leis cabhrú leat sraitheanna iomlána nó sraitheanna iomlána a roghnú go tapa sa roghnú bunaithe ar luach sonrach cille i gcolún áirithe in Excel.  Íoslódáil rian iomlán saor in aisce 60 lá de Kutools le haghaidh Excel anois!


Luachanna ar ais i gcill eile má tá téacsanna difriúla i bhfoirmle le cill

Taispeánfaidh an chuid seo duit an fhoirmle chun luachanna a thabhairt ar ais i gcill eile má tá téacs difriúil i gcill in Excel.

1. Ní mór duit tábla a chruthú leis na luachanna sonracha agus na luachanna toraidh atá suite ar leithligh in dhá cholún. Féach an pictiúr:

2. Roghnaigh cill bhán chun an luach a thabhairt ar ais, clóscríobh an fhoirmle thíos agus brúigh an Iontráil eochair chun an toradh a fháil. Féach an pictiúr:

Foirmle: Luachanna ar ais i gcill eile má tá téacsanna éagsúla i gcill

= VLOOKUP (E6,B5: C7,2, BRÉAGACH)

Nótaí:

San fhoirmle, E6 an bhfuil an luach sonrach sa chill a thabharfaidh tú luach ar ais dó bunaithe ar, B5: C7 is é an raon colún ina bhfuil na luachanna sonracha agus na luachanna toraidh, an 2 ciallaíonn uimhir go bhfuil na luachanna toraidh atá suite ar an dara colún sa raon tábla.

As seo amach, nuair a athrófar an luach in E6 go luach sonrach, tabharfar a luach comhfhreagrach ar ais in F6 láithreach.


Luachanna a thabhairt ar ais go héasca i gcill eile má tá téacsanna éagsúla i gcill

I ndáiríre, is féidir leat an fhadhb thuas a réiteach ar bhealach níos éasca. Tá an Cuardaigh luach ar an liosta fóntais de Kutools le haghaidh Excel in ann cabhrú leat é a bhaint amach gan ach cúpla cad a tharlaíonn gan cuimhneamh ar fhoirmle.

1. Mar an gcéanna leis an modh thuas, ní mór duit freisin tábla a chruthú leis na luachanna sonracha agus na luachanna toraidh a aimsíonn ar leithligh in dhá cholún.

2. Roghnaigh cill bhán chun an toradh a aschur (roghnaigh mé F6 anseo), agus ansin cliceáil Kutools > Cúntóir Foirmle > Cúntóir Foirmle. Féach an pictiúr:

3. Sa Cúntóir Foirmle bosca dialóige, cumraigh mar seo a leanas:

  • 3.1 Sa Roghnaigh foirmle bosca, aimsigh agus roghnaigh Cuardaigh luach ar an liosta;
    Leideanna: Is féidir leat an scagairí bosca, cuir focal áirithe isteach sa bhosca téacs chun an fhoirmle a scagadh go tapa.
  • 3.2 Sa Tábla_eagar bosca, roghnaigh an tábla gan ceanntásca a chruthaigh tú i gcéim 1;
  • 3.2 Sa Cuardach_luach bosca, roghnaigh go bhfuil an luach sonrach a gheobhaidh tú luach bunaithe ar an gcill;
  • 3.3 Sa Colún bosca, sonraigh an colún a bhfillfidh tú an luach comhoiriúnaithe uaidh. Nó is féidir leat uimhir an cholúin a iontráil sa bhosca téacs go díreach mar a theastaíonn uait.
  • 3.4 Cliceáil ar an OK cnaipe. Féach an pictiúr:

As seo amach, nuair a athrófar an luach in E6 go luach sonrach, tabharfar a luach comhfhreagrach ar ais in F6 láithreach. Féach an toradh mar atá thíos:

  Más mian leat triail saor in aisce (30 lá) a bheith agat ar an bhfóntas seo, cliceáil le do thoil chun é a íoslódáil, agus ansin téigh chun an oibríocht a chur i bhfeidhm de réir na gcéimeanna thuas.


Kutools le haghaidh Excel - Cabhraíonn sé leat an obair a chríochnú i gcónaí roimh an am, Bíodh níos mó ama agat chun taitneamh a bhaint as an saol
An minic a bhíonn tú ag imirt teacht suas leis an obair, easpa ama le caitheamh ort féin agus ag do mhuintir?  Kutools le haghaidh Excel cabhrú leat chun déileáil leis 80% Excel puzzles agus feabhas a chur ar éifeachtacht oibre 80%, tabhair níos mó ama duit aire a thabhairt do theaghlach agus taitneamh a bhaint as an saol.
300 uirlis chun cinn le haghaidh 1500 cás oibre, déan do phost an oiread sin níos éasca ná riamh.
Ní gá foirmlí agus cóid VBA a chur de ghlanmheabhair a thuilleadh, tabhair sosa d’inchinn as seo amach.
Is féidir oibríochtaí casta agus arís agus arís eile a phróiseáil aon-uaire i soicindí.
Laghdaigh na mílte oibríochtaí méarchláir & luch gach lá, slán a fhágáil le galair cheirde anois.
Bí i do shaineolaí Excel i gceann 3 nóiméad, cabhrú leat aitheantas tapa a fháil agus ardú céime pá a fháil.
110,000 duine an-éifeachtach agus 300+ rogha cuideachtaí a bhfuil cáil dhomhanda orthu.
Déan do $ 39.0 ar fiú níos mó ná $ 4000.0 oiliúint daoine eile.
Triail saor in aisce gné iomlán 30 lá. Ráthaíocht Airgead Ar Ais 60 Lá gan chúis.

Comments (98)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
What would I need to add to this formula (Method A) to get a third BLANK option? And where would it go?
Example:
If the cell I'm searching is blank, I would like it to return no value in the other cell. I do not want the "if false" value to populate.
This comment was minimized by the moderator on the site
i want a cell to automatically choose a number based on the drop-down option in the cell before, e.g drop-down, half meal, full meal, if I select half meal drop-down, the next cell should show 50% while the full meal drop-down will reflect 100%. please help
This comment was minimized by the moderator on the site
Hi Josh,
In the cell where you want to output the percentage, enter the following formula and press the Enter key. Hope I can help.
=IF(C15="half meal", "50%", IF(C15="full meal", "100%", ""))
This comment was minimized by the moderator on the site
Is it possible to return a certain value in 1 column based on another?


What I currently have: On a differnt tab (same worksheet):
Column A Column B
Location Name Dept Location Name Location Code
Miami 4455 Miami 123


What I want to happen:
Location Name Dept
Miami 11234455

In Coumn B, I want it to first add a 1, then take what is in Column A, see what is the Location Code on then different tab, insert that location code, and add that to the front of the current data in Column B.

1 + 123 + 4455
This comment was minimized by the moderator on the site
Hi Jen Rader,
You can apply the following formula to get it done.
=IFERROR(1&VLOOKUP(A9,Sheet2!$A$2:$B$5,2, 0)&VLOOKUP(Sheet1!A9,Sheet1!$A$2:$B$5,2, 0),"")
In this formula,
1. A9 is the cell contains the value you want to return values based on;
2. Sheet2 is the tab name contains the "Location Code"; If the tab name contains space, please enclose the tab name in single quotes, such as 'tab name'.
3. Sheet1 is the tab name contains the "Dept";
4. $A$2:$B$5 is the range containing the table data (include both search values and return values ).
See screenshots below:
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/return1.png
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/return2.png
This comment was minimized by the moderator on the site
OMG Crystal! You are amazing! Show me your ways! Thank you so much - it worked exactly like I was hoping!
This comment was minimized by the moderator on the site
I am trying to find out the formula to copy the number shown in B2 on cell D2 if the Cell C2 has a letter "S". Can anyone help me?
This comment was minimized by the moderator on the site
Hi Aurora,
Please enter the following formula in cell D2.
=IF(ISNUMBER(SEARCH("S",C2)),B2,"No qualify")
This comment was minimized by the moderator on the site
Bonjour à tous, j'ai un tableau Excel et j'aimerais réaliser via une macro l'opération suivante: si une cellule contient une date, me renvoyer une valeur dans une autre cellule. Pourriez vous m'aider s'il vous plait? Je vous remercie par avance
This comment was minimized by the moderator on the site
STALLS D 25 Palais - Stalls SR
STALLS M 19 Palais - Stalls SR
LOUNGE E 22 Palais - Lounge SR
LOUNGE G 23 Palais - Lounge SL
ORCH K 40 Palais - Orchestra SR
For each line in a spreadsheet I need to evaluate against a table like above and find the value that matches all of the first 3 values.
Normally I would use sumifs for this type of problem but the answer is a text string so it returns 0
This comment was minimized by the moderator on the site
I need a formula that makes column N equal the text “OTO” if column K equals MCS TRAINING how do I do that???
This comment was minimized by the moderator on the site
Hi Abby,Please apply the following formula in the cells of column N:=IF(ISNUMBER(MATCH("MCS TRAINING",K1)),"OTO","NULL")
This comment was minimized by the moderator on the site
Hi - This is my formula that I am working with.  Currently it works, but where B21 is being returned, the value of B21 is a link to another place altogether.  I want to keep the display text but would like to include the link as well.  Can this be done?  =IF(ISNUMBER(SEARCH("DATA_MAPPING",general_report!AJ21)),general_report!B21,"")
This comment was minimized by the moderator on the site
Is it possible to split the return value to a different tab? IE: columns B and C are located on one tab, and values in columns E and F are located on a different tab?
This comment was minimized by the moderator on the site
Hi

Is it possible to populate another cell, not the cell with the formula.

Example
A1 Yes
A2 3
A3 Formula searches A1, Grabs value at A2 and if Yes Paste it in A4
A4 3 
This comment was minimized by the moderator on the site
Hi,
Sorry can't help you with the problem. I suggest you post the problem to the forum below to get help from other Excel enthusiasts.
https://www.extendoffice.com/forum/kutools-for-excel.html 
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations