Babban tace da wasu sihiri

Ga mafi yawan masu amfani da Excel, lokacin da kalmar "tace bayanai" ta fito a cikin kawunansu, kawai tacewa ta yau da kullun daga shafin. Bayanai - Tace (Bayanai - Tace):

Irin wannan tacewa abu ne sananne, babu shakka, kuma ga mafi yawan lokuta zai yi. Koyaya, akwai yanayi lokacin da kuke buƙatar tace ta babban adadin hadaddun yanayi a cikin ginshiƙai da yawa lokaci ɗaya. Tacewar da aka saba anan ba ta dace sosai ba kuma ina son wani abu mafi ƙarfi. Irin wannan kayan aiki zai iya zama m tace, musamman tare da ɗan "ƙarewa da fayil" (bisa ga al'ada).

Basis

Don farawa, saka ƴan layukan da ba komai a saman teburin bayanan ku kuma kwafi kan tebur ɗin can - wannan zai zama kewayo tare da yanayi (wanda aka haskaka da rawaya don tsabta):

Dole ne a sami aƙalla layi mara komai tsakanin sel rawaya da tebur na asali.

Yana cikin ƙwayoyin rawaya waɗanda kuke buƙatar shigar da ma'auni (sharadi), gwargwadon abin da za a yi tacewa. Alal misali, idan kuna buƙatar zaɓar ayaba a cikin Moscow "Auchan" a cikin kwata na III, yanayin zai yi kama da haka:

Don tacewa, zaɓi kowane tantanin halitta a cikin kewayon tare da bayanan tushe, buɗe shafin data Kuma danna Ƙari (Bayanai - Na ci gaba). A cikin taga da ke buɗewa, ya kamata a riga an shigar da kewayon da ke da bayanai ta atomatik kuma kawai za mu ƙididdige kewayon yanayi, watau A1:I2:

Lura cewa kewayon yanayi ba za a iya kasaftawa "tare da gefe", watau ba za ka iya zaɓar karin m rawaya Lines, saboda komai cell a cikin kewayon yanayi ana gane ta Excel a matsayin rashin ma'auni, da kuma gaba daya fanko. layi azaman buƙatar nuna duk bayanan ba tare da nuna bambanci ba.

switch Kwafi sakamakon zuwa wani wuri zai ba ka damar tace jerin ba a can akan wannan takardar (kamar yadda yake tare da tacewa na yau da kullum), amma don sauke layuka da aka zaɓa zuwa wani kewayon, sannan za a buƙaci a ƙayyade a filin. Sanya sakamako a cikin kewayo. A wannan yanayin, ba ma amfani da wannan aikin, mun bar Tace jeri a wurin kuma danna OK. Za a nuna layuka da aka zaɓa a kan takardar:

Ƙara macro

"To, ina jin dadi a nan?" ka tambaya za ka yi gaskiya. Ba wai kawai kuna buƙatar shigar da yanayi a cikin sel rawaya tare da hannayenku ba, amma kuma buɗe akwatin maganganu, shigar da jeri a can, danna. OK. Bakin ciki, na yarda! Amma "komai yana canzawa idan sun zo ©" - macros!

Ana iya haɓaka aiki tare da ingantaccen tacewa da sauƙaƙe ta amfani da macro mai sauƙi wanda zai gudanar da ingantaccen tacewa ta atomatik lokacin shigar da yanayi, watau canza kowane tantanin halitta rawaya. Danna dama akan shafin takardar yanzu kuma zaɓi umarnin Tushen rubutu (Lambar tushen). A cikin taga da yake buɗewa, kwafi kuma liƙa lambar mai zuwa:

Canje-canje na Sub Worksheet_Mai zaman kansa(ByVal Target A Matsayin Range) Idan Ba ​​Tsallakewa (Manufa, Range("A2:I5")) Ba Komai Bane akan Kuskure Ci gaba ActiveSheet na gaba.ShowAllData Range("A7").Yankin Yanzu.AdvancedFilter Action:=xlFilterInPlace :=Range("A1").Yankin Yanzu Idan Ƙarshen Sub  

Wannan hanya za ta gudana ta atomatik lokacin da aka canza kowane tantanin halitta akan takardar aiki na yanzu. Idan adireshin tantanin da aka canza ya fada cikin kewayon rawaya (A2: I5), to wannan macro yana cire duk abubuwan tacewa (idan akwai) kuma ya sake shigar da tsawaita tacewa zuwa teburin bayanan tushen farawa da A7, watau komai za a tace nan take, nan da nan. bayan shigar da yanayi na gaba:

Don haka komai ya fi kyau, dama? 🙂

Aiwatar da hadaddun tambayoyi

Yanzu da aka tace komai akan tashi, za mu iya ɗan zurfafa cikin nuances kuma mu ɓata hanyoyin ƙarin hadaddun tambayoyin a cikin ingantaccen tacewa. Baya ga shigar da matches daidai, zaku iya amfani da haruffan kati daban-daban (* da ?) da alamun rashin daidaituwar lissafi a cikin yanayi da yawa don aiwatar da bincike mai ƙima. Halin hali ba shi da mahimmanci. Don a fayyace, na taƙaita duk zaɓuɓɓukan da za a iya yi a cikin tebur:

Maƙasudai masu hankali:

• Alamar * tana nufin kowane lamba na kowane haruffa, kuma? – kowane hali daya.
• Hankali wajen sarrafa rubutu da tambayoyin lambobi sun ɗan bambanta. Don haka, alal misali, tantanin halitta mai lamba 5 ba yana nufin nemo duk lambobi da suka fara da biyar ba, amma yanayin tantanin halitta mai harafin B daidai yake da B*, watau zai nemi kowane rubutu da ya fara da harafin B.
• Idan tambayar rubutu ba ta fara da alamar =, to, zaku iya sanya * a hankali.
• Dole ne a shigar da kwanan wata a cikin tsarin Amurka na wata-rana-shekara kuma ta hanyar juzu'i (ko da kuna da saitunan Excel da na yanki).

Hanyoyi masu ma'ana AND-OR

Sharuɗɗan da aka rubuta a cikin sel daban-daban, amma a cikin layi ɗaya, ana ɗaukar su azaman haɗin haɗin gwiwa ta mai aiki da hankali И (DA):

Wadancan. tace min ayaba a cikin kwata na uku, daidai a Moscow kuma a lokaci guda daga Auchan.

Idan kana buƙatar haɗa yanayi tare da ma'aikacin ma'ana OR (OR), to kawai suna buƙatar shigar da su cikin layi daban-daban. Alal misali, idan muna bukatar mu nemo duk umarni na manajan Volina na Moscow peaches da duk umarni ga albasa a cikin kwata na uku a Samara, wannan za a iya bayyana a cikin kewayon yanayi kamar haka:

Idan kana buƙatar sanya sharuɗɗa biyu ko fiye akan shafi ɗaya, to kawai zaka iya kwafin rubutun shafi a cikin kewayon ma'auni kuma shigar da na biyu, na uku, da sauransu a ƙarƙashinsa. sharuddan. Don haka, alal misali, zaku iya zaɓar duk ma'amaloli daga Maris zuwa Mayu:

Gabaɗaya, bayan “kammala da fayil”, babban tacewa yana fitowa ya zama kayan aiki mai kyau, a wasu wurare bai fi na'urar tacewa ta yau da kullun ba.

• Superfilter akan macros
• Menene macros, inda kuma yadda ake saka lambar macro a cikin Visual Basic
• Smart Tables a cikin Microsoft Excel