Pehea e hiki ai iaʻu ke hoʻohana i ka hana huli ma Excel e ʻimi ai i kahi waiwai ma kahi papaʻaina a i ʻole laulā o ke kelepona?
Ma Microsoft Excel, ʻo kekahi o nā hiʻohiʻona pono loa ʻo ia ka hana huli. Hāʻawi kēia hana iā ʻoe e ʻimi wikiwiki i kahi waiwai kikoʻī i loko o ka papaʻaina a i ʻole ka laulā o nā cell. Inā ʻoe e ʻimi nei i kahi helu kikoʻī a i ʻole kikokikona, e kōkua kēia hiʻohiʻona iā ʻoe e mālama i ka manawa a me ka hoʻoikaika ʻana ma ka ʻimi ʻana i ka ʻike pili i kāu pāpalapala. Maanei mākou e ʻimi ai pehea e hoʻohana ai i ka hana ʻimi ma Excel a me kekahi mau ʻōlelo aʻoaʻo e hoʻomaikaʻi ai i kona pono.
Ma mua o ka hoʻohana ʻana i ka hana lookup, pono e hoʻomaopopo i ke ʻano o ka hana a me ke ʻano o nā hopena e hiki ke hāʻawi. i kēlā waiwai. Hiki iā ʻoe ke ʻimi ma nā kolamu horizontal a me nā lālani kūpaʻa, e hilinaʻi ana i kahi o ka laulā o nā cells a i ʻole ka papaʻaina āu e hoʻohana nei.
No ka e hoʻohana i ka hana ʻimi, e koho wale i kahi cell kahi āu e makemake ai e hōʻike ʻia ka hopena a laila e ʻano “=SEARCH(search_value, cell_range, desired_result)”. ʻO ka "waiwai_search" ka ʻikepili āu e makemake ai e loaʻa, ʻo ka "cell_range" ʻo ka laulima āu e makemake ai e huli, a ʻo ka "hopena_makemake" ʻo he helu e kuhikuhi ana i ke ʻano o ka kaulike e ʻimi nei.
He mea nui e hoʻomaopopo i ka hana search he case-insensitive, no laila inā ʻoe e ʻimi nei i kahi waiwai kikoʻī, e hoʻomaopopo pono e hoʻohana i ka huaʻōlelo pololei. Eia kekahi, inā ʻaʻole ʻike ʻia ka waiwai i ʻimi ʻia ma ka laulā kelepona i ʻōlelo ʻia, e hoʻihoʻi ka hana i kahi hewa. No ka pale ʻana i kēia, hiki iā ʻoe ke hoʻohana i ka hana IFERROR me ka hana ʻimi e hōʻike i kahi leka maʻamau inā ʻaʻole ʻike ʻia ka waiwai i ʻimi ʻia.
I ka hopena, ʻo ka hana ʻimi ma Excel kahi mea hana ikaika e maʻalahi ai ka loaʻa ʻana o nā waiwai kikoʻī i loko o kahi papaʻaina a i ʻole ka nui o nā cell. Ke hana nei ʻoe i kahi papahana pilikino a i ʻole ka ʻoihana ʻoihana, hiki i ka haku ʻana i kēia hiʻona ke kōkua iā ʻoe e hoʻonui i kāu huahana a loaʻa i ka ʻike āu e pono ai me ka wikiwiki a me ka maikaʻi E hoʻāʻo e hoʻohana i ka hana huli ma Excel a ʻike pehea e maʻalahi ai kāu hana ma nā papapalapala.
- Hoʻomaka i ka hana ʻimi ma Excel
ʻO ka hana search ma Excel he mea hana pono loa ia e hiki ai ia makou ke huli a imi i ka waiwai kiko'ī i loko o ka papaʻaina a i ʻole ka laulā o nā cell. He mea maikaʻi loa kēia hiʻohiʻona ke hana mākou me ka nui o ka ʻikepili a pono e ʻimi pono i ka ʻike kikoʻī.
No ka hoʻohana ʻana i ka hana ʻimi ma Excel, pono mākou e hahai i kēia mau hana:
- E koho i ka laula o nā pūnaewele kahi a mākou e makemake ai e hana i ka huli. He mea nui ka wehewehe pono ʻana o ka laulā kelepona i pololei nā hopena.
- E hoʻokomo i ka waiwai a mākou e makemake ai e ʻimi. Hiki i kēia he helu, kikokikona, lā, a i ʻole kekahi ʻano ʻikepili ʻē aʻe a mākou e ʻimi nei.
- E wehewehe i ke ʻano o ka pāʻani a mākou makemake e hoʻohana. Hāʻawi ʻo Excel iā mākou i kekahi koho, pehea e ʻimi ai kahi waiwai pololei, huli i kahi waiwai pili, huli me wildcards, a me kekahi.
I ka pau ʻana o kēia mau ʻanuʻu, e hōʻike ʻo Excel i ka hopena ʻimi ma ke kelepona i koho ʻia. Inā loaʻa ka waiwai i ʻimi ʻia ma ka laulā o nā cell, e hōʻike mai ʻo Excel iā mākou i ka pāʻani mua i loaʻa. Inā ʻaʻole ʻike ʻia ka waiwai, e hōʻike ʻo Excel i kahi memo hewa.
ʻO ka hana ʻimi ma Excel he mea hana maʻalahi a ikaika loa e hiki ai iā mākou ke mālama i ka manawa a me ka hoʻoikaika ʻana i kā mākou mau hana i kēlā me kēia lā. Inā pono mākou e ʻimi i kahi helu invoice, lā kikoʻī, a i ʻole kekahi ʻano ʻike ʻē aʻe i loko o kahi papa nui, hāʻawi ka hana ʻimi ma Excel iā mākou me ka wikiwiki a pololei.
- Pehea e hoʻohana ai i ka hana VLOOKUP no ka ʻimi ʻana i kahi waiwai in a table a i ʻole ka laulā o nā cell
Ma Excel, ʻo ka hana VLOOKUP he mea hana ikaika ia e hiki ai iā ʻoe ke ʻimi i kahi waiwai kikoʻī ma ka papaʻaina a i ʻole ka laulā o nā cell. Hoʻohana nui ʻia kēia hana i ka nānā ʻana a me nā hana helu, no ka mea hiki iā ʻoe ke ʻimi wikiwiki i ka ʻike āu e pono ai. No ka hoʻohana ʻana i ka hana VLOOKUP, pono ʻoe e koho mua i ka laulima o nā cell āu e makemake ai e ʻimi i ka waiwai i makemake ʻia. Hiki iā ʻoe ke hana i kēia ma ke kaomi ʻana i kahi cell a me ka huki ʻana i ka cursor e koho i nā cell pili.
Ke koho ʻoe i ka laulā o nā cell a paʻi i ka hana VLOOKUP, pono ʻoe e hāʻawi i nā manaʻo kūpono. Penei ka syntax o ka hana: VLOOKUP(lookup_value, array, column_index, [exact_match]). ʻO ka pane first, lookup_value, ʻo ia ka waiwai āu e ʻimi nei ma the table a i ʻole ka laulā o nā cell. He helu, kikokikona, a i ʻole he kuhikuhi i ke kelepona ʻē aʻe. ʻO ka hoʻopaʻapaʻa ʻelua, array, ʻo ia ka laulā o nā cell āu e makemake ai e ʻimi i ka waiwai. E hōʻoia i kēia laulima i ke kolamu kahi i loaʻa ai ka waiwai āu e ʻimi nei. ʻO ka hoʻopaʻapaʻa ʻekolu, index_column, hōʻike i ke kolamu o ka matrix i loaʻa ka waiwai āu e makemake ai e ʻimi. ʻO ka hope, ʻo ka pane ʻehā, exact_match, he waiwai kūpono (TRUE a i ʻole FALSE) e hoʻoholo ai inā makemake ʻoe i kahi pāʻani pololei a ʻaʻole paha. Inā haʻalele ʻoe i kēia hoʻopaʻapaʻa, e manaʻo ʻia ʻo TRUE ma ka paʻamau.
Ke hāʻawi ʻoe i nā manaʻo i koi ʻia, e ʻimi ʻo Excel i ka waiwai i hōʻike ʻia ma ke kolamu i hōʻike ʻia i loko o ka pūʻulu i koho ʻia inā loaʻa iā ia kahi hoʻohālikelike pololei, e hoʻihoʻi ka hana VLOOKUP i ka waiwai kūpono mai kahi kolamu ʻē aʻe ma ka laina like. Inā ʻaʻole hiki iā ʻoe ke loaʻa i kahi pāʻani pololei, hiki iā ʻoe ke hoʻohana i ka hoʻopaʻapaʻa exact_match e wehewehe inā makemake ʻoe i kahi hoʻokūkū pili a ʻaʻole paha. E hoʻomanaʻo e ʻimi wale ka hana VLOOKUP i ka waiwai i loko o hoʻokahi columna. Inā makemake ʻoe e ʻimi i nā kolamu he nui, hiki iā ʻoe ke hoʻohui i ka hana VLOOKUP me nā hana ʻē aʻe e like me CONCATENATE a i ʻole HLOOKUP. Me kēia hiʻohiʻona ikaika, hiki iā ʻoe ke hana i nā huli wikiwiki a maikaʻi hoʻi i kāu mau papa a me nā pae o nā pūnaewele ma Excel.
- Manaʻo e hoʻomaikaʻi i kāu hulina ma Excel
ʻO ka hana ʻimi ma Excel he mea hana pono loa ia e hiki ai iā mākou ke ʻimi i kahi waiwai kikoʻī i loko o kahi papaʻaina a i ʻole ka nui o nā cell. No ka hoʻomaikaʻi ʻana i kāu hulina a loaʻa i nā hopena pololei, eia kekahi mau manaʻo kōkua e kōkua iā ʻoe:
- E hoʻohana i ka hana VLOOKUP: ʻO ka hana VLOOKUP kekahi o nā hana nui i hoʻohana ʻia ma Excel e ʻimi ai i kahi waiwai ma i kahi papaʻaina a i ʻole ka laulā o nā cell. ʻO ka syntax kumu o kēia hana ʻo =VLOOKUP(waiwai_lookup, papa_kaulana, kolamu_helu, [kokoke_kaulana])Hiki iā ʻoe ke hoʻohana i kēia hana no ka huli ʻana i nā waiwai kikoʻī a i ʻole nā kumu waiwai e pili ana i ka ʻāpana. pili_pili.
- E hoʻokaʻawale i ka ʻikepili mai i ka pākaukau: Ma mua o ka hana ʻana i kahi hulina, pono e e hoʻokaʻawale i ka ʻikepili papa ma ke ʻano piʻi a i ʻole ka iho. E kōkua kēia iā ʻoe e ʻimi wikiwiki i ka waiwai i makemake ʻia. Hiki iā ʻoe ke hoʻohana i ka hana ʻano ma Excel e hoʻokō i kēia hana.
- E kaupalena i ka laula hulina: Inā loaʻa iā ʻoe kahi papaʻaina nui loa a i ʻole ka nui o nā cell, hiki iā ʻoe ke kaupalena i ka ʻimi hulina me ka hoʻohana ʻana i ka ʻāpana papa_ʻāina i ka hana VLOOKUP. Ma kahi o ka wehewehe ʻana i ka papaʻaina holoʻokoʻa, hiki iā ʻoe ke koho i nā kolamu a i ʻole nā lālani kūpono e hōʻemi i ka manawa huli.
Ke hoʻohana nei i kēia mau ʻōlelo aʻoaʻo, hiki iā ʻoe ke hoʻomaikaʻi i kāu ʻimi ʻana ma Excel a loaʻa i nā waiwai āu e pono ai. E hoʻomanaʻo i ka hoʻomaʻamaʻa a e ʻimi i nā hana ʻē aʻe e pili ana i mea e ʻoi aku ka maikaʻi o ke kauoha o Excel a me e hoʻohana pono i kāna mau mea hiki.
– Nā hiʻohiʻona ʻimi maʻamau ma Excel
ʻO ka hana ʻimi ma Excel he mea pono loa ia e hiki ai iā ʻoe ke ʻimi koke i kahi waiwai kikoʻī i loko o kahi papaʻaina a i ʻole ka nui o nā cell. Ma o nā hiʻohiʻona kūpono, e ʻike mākou pehea e hoʻohana ai i kēia hana e hoʻomaʻamaʻa i kā mākou hana me ka nui o ka ʻikepili.
Laʻana 1: Inā paha he papaʻaina kā mākou me ka inoa o nā huahana like ʻole a me kā lākou mau kumukūʻai. Inā makemake mākou e ʻimi i ke kumu kūʻai o kekahi huahana, hiki iā mākou ke hoʻohana i ka hana VLOOKUP. Hāʻawi kēia hana iā mākou e ʻimi i ka waiwai ma kahi kolamu kikoʻī a hoʻihoʻi i ka waiwai kūpono mai ke kolamu pili.
Laʻana 2: ʻO kahi ala ʻē aʻe e hoʻohana ai i ka hana search ma Excel ʻo ia ma ka hoʻohana i nā kānana. Inā makemake mākou e kānana i kahi papaʻaina ma kahi kikoʻī kikoʻī, hiki iā mākou ke hoʻohana i ka hana FILTER. No ka laʻana, inā loaʻa iā mākou kahi papa o nā limahana a makemake mākou e kānana iā ia e ka ʻoihana, hiki iā mākou ke hoʻohana i kēia hana no ka loaʻa ʻana o nā limahana no ia ʻoihana.
Laʻana 3: Hiki iā mākou ke hoʻohana i ka hana ʻimi ma Excel e ʻimi ai i ka ʻikepili ma nā pāpalapala lehulehu. No ka laʻana, inā he ʻokoʻa kā mākou pāpalapala me ka ʻike kūʻai i kēlā me kēia mahina a makemake mākou e ʻimi i ka nui o nā kūʻai aku i ka makahiki i hāʻawi ʻia Me ka hana SUMIF hiki iā mākou ke ʻimi i ka waiwai ma nā pāpalapala a pau.
Hōʻike kēia mau hiʻohiʻona kūpono i ka maʻalahi o ka hana ʻimi ma Excel a pehea e hiki ai iā mākou ke hoʻohana pono iā ia e hoʻopololei i kā mākou mau hana i kēlā me kēia lā inā e ʻimi ana i nā waiwai kikoʻī, kānana ʻikepili, a i ʻole ka ʻimi ʻana ma nā pāpalapala lehulehu, hiki i kēia mea hana ke hana maikaʻi me. ka nui o ka ʻike.
- Pehea e hoʻohana ai i ka hana FILTER no ka ʻimi ʻana i kahi waiwai i loko o ka papaʻaina a i ʻole ka nui o nā cell
ʻO ka hana FILTER ma Excel kahi mea hana ikaika e hiki ai iā ʻoe ke ʻimi i kahi waiwai i loko o ka papaʻaina a i ʻole ka nui o nā cell me ka pono. Me kēia hiʻohiʻona, hiki iā ʻoe ke kānana i ka ʻikepili a hōʻike wale i nā mea i kūpono i nā koina hulina āu e makemake ai. Ke ʻimi nei ʻoe i kahi helu kikoʻī, kikokikona, a i ʻole ka hui pū ʻana o nā mea ʻelua, e ʻae ke kānana iā ʻoe e ʻimi koke i ka ʻike āu e pono ai.
No ka hoʻohana ʻana i ka hana FILTER, pono ʻoe e koho mua i ka laulā o ka ʻikepili āu e makemake ai e ʻimi Hiki iā ʻoe ke koho i kahi papaʻaina holoʻokoʻa a i ʻole ka laulā kikoʻī. Ke koho ʻoe i ka laulā, hiki iā ʻoe ke hoʻohana i ka kānana me ka hoʻohana ʻana i kēia ʻano: =FILTER(lalana, mau koho). Ma hea ʻo “kaulana” ʻo ka laula o ka ʻikepili ma kahi āu e makemake ai e ʻimi a me ”koʻina” nā kūlana e hui ai ka ʻikepili i mea e hōʻike ʻia ai.
Hiki ke maʻalahi loa nā pae hulina a hiki iā ʻoe ke hoʻohana i nā mea hoʻohana pono e like me «<», ">«, «=», »<=", ">=» a me «<>«. Eia hou, hiki iā ʻoe ke hoʻohui i nā aʻo nui me ka hoʻohana ʻana i nā mea hoʻohana logical e like me "AND" a i ʻole "OR". No ka laʻana, inā makemake ʻoe e ʻike i nā waiwai āpau i ʻoi aku ma mua o 100 i loko o kahi kolamu, ʻo ia ka ">100«. Inā makemake ʻoe e ʻike i nā waiwai āpau i ʻoi aku ma mua o 100 a i ʻole ka liʻiliʻi ma mua o 50, hiki iā ʻoe ke hoʻohana i ka criterion »>100 A I ʻole <50«. Ke hoʻohana ʻia ka kānana, e hōʻike ʻia ka ʻikepili i kūpono i i nā koina i hoʻokumu ʻia.
ʻO wau ʻo Sebastián Vidal, he ʻenekinia kamepiula makemake nui i ka ʻenehana a me DIY. Eia kekahi, ʻo wau ka mea nāna i hana tecnobits.com, kahi aʻu e kaʻana like ai i nā kumu aʻo e hoʻomaʻamaʻa a hoʻomaopopo ʻia ka ʻenehana i nā mea a pau.