Въпрос към урок: Логически оператори от курс: Анализи с Excel

Как да събера няколко оператора IF в една формула, която да изчислява правилно зададеното условие?

MS Excel
Електронни таблици
MS Office
Excel VBA
Анализи с Excel

Здравейте, как мога да събера всичките условия в една формула, опитвах се и все ми дава грешка:

=IF(LEFT(A2;1)="V";"A000";IF(LEFT(A2;1)="W";"A000";IF(LEFT(B2;1)="V";"A000";IF(LEFT(A1;1)="A";"000";IF(LEFT(B2;2)="AA";"A5859 bgn";IF(LEFT(B2;4)="M536";"A5859 bgn";IF(LEFT(B2;1)="D";"SA";IF(LEFT(B2;1)="M";"A888 ABU";IF(LEFT(B2;1)="L";"A390";IF(LEFT(B2;1)="R";"LR";IF(LEFT(B2;1)="F";"LR";IF(LEFT(B2;1)="G";"LR";""))))))))))))

=IF(LEFT(B;8)="KIT88888";"SA BU";IF(LEFT(B;6)="KIT888";"LR";IF(LEFT(B;1)="A";"LR";IF(LEFT(B;6)="KIT889";""))))

=IF(LEFT(B;6)=“KIT810“;“SA“;““)

Имам още един въпрос, как формулата ще прави разлика между KIT88888, KIT889 и KIT870 като първите четири символа са еднакви, а това, което трябва да излиза като резултат е различно. Благодаря предварително!

Затова ги използвам трите формули една след друга, за да не се конфронтират и да излезе грешен резултат.

Но аз ще се радвам да получа отговор как да поправя това и да се използва само една формула.

Здравейте Валентина, опитайте с функция VLookup, като в спомагателната таблица зададете символите, които трябва да се записват като резултат вместо много If. Така ще се направи разлика между KIT88888, KIT889 и KIT870.

Здравейте, ето и обединение на формулите:

=IF(OR(LEFT(A2;1)="V";LEFT(A2;1)="W";LEFT(B2;1)="V");"A000";IF(LEFT(A2;1)="A";"000";IF(OR(LEFT(B2;2)="AA";LEFT(B2;4)="M536");"A5859 bgn";IF(OR(LEFT(B2;1)="D";LEFT(B2;6)="KIT810");"SA";IF(LEFT(B2;1)="M";"A888 ABU";IF(LEFT(B2;1)="L";"A390";IF(OR(LEFT(B2;1)="R";LEFT(B2;1)="F";LEFT(B2;1)="G";LEFT(B2;1)="A");"LR";IF(LEFT(B2;8)="KIT88888";"SA BU";IF(LEFT(B2;6)="KIT888";"LR";"")))))))))

Формулата отговаря на следните условия (ако съм ги разбрала правилно от формулите):

ако първите знаци в колона А са: или ако първите знаци в колона В са: резултат
V A000
W A000
V A000
A 000
AA A5859 bgn
M536 A5859 bgn
D SA
M A888 ABU
L A390
R LR
F LR
G LR
KIT88888 SA BU
KIT888 LR
A LR
KIT889
KIT810 SA

Тази формула е много дълга, трудна за корекции и проследяване и с голяма вероятност за допускане на грешки. Винаги по-добър вариант е да се използват по-кратки и лесни за четене и корекция формули когато е възможно.

Както казва г-жа Вълкова най-добре е по възможност да се използва функция Vlookup.

Здравейте, много ви благодаря за усилията и помощта!

Здравейте, това е базата данни.За съжаление sverweis не ми помага.

ако първите знаци в колона А са: или ако първите знаци в колона В са: резултат A A350 KIT5YD53 SA BF KIT5YF LR KIT5YG LR G LR F LR R LR D SA KIT5YD SA L A380 V A350 WQ A350 M536 A400M CFS M000 A400M APU M524 A400M APU M538 A400M APU M491 A400M APU M498 A400M APU M530 A400M APU AA A400M CFS A LR

Здравейте, Валентина,

Ето и формулата с IF за номерата на снимката:

=IF(OR(LEFT(A2;1)="A";LEFT(B2;1)="V";LEFT(B2;2)="WQ");"A350";IF(LEFT(B2;8)="KIT5YD53";"SA BF";IF(LEFT(B2;1)="L";"A380";IF(OR(LEFT(B2;4)="M000";LEFT(B2;4)="M524";LEFT(B2;4)="M538";LEFT(B2;4)="M491";LEFT(B2;4)="M498";LEFT(B2;4)="M530");"A400M APU";IF(OR(LEFT(B2;4)="M536";LEFT(B2;4)="AA");"A400M CFS";IF(OR(LEFT(B2;6)="KIT5YF";LEFT(B2;6)="KIT5YG";LEFT(B2;1)="G";LEFT(B2;1)="F";LEFT(B2;1)="R";LEFT(B2;1)="A");"LR";IF(OR(;LEFT(B2;1)="D";LEFT(B2;6)="KIT5YD");"SA";"")))))))

Стана още по-дива....

Обаче ако в работните колони А и В няма други символи в номерата (кодовете) освен записаните в базата данни с критериите, по-добър вариант е да се използва формула, която комбинира функциите IFERROR и VLOOKUP (Функция IFERROR е налична от версия Excel 2007 и следващите. Ако сте с по-стара версия се прави комбинация с други функции.):

=IFERROR(VLOOKUP(B2;$K$2:$L$22;2;0);VLOOKUP(A2;$J$2:$L$22;3;0))

В тази формула е възприето, че:

  • данните, които ще обработвате са в колони А и В

  • базата данни се намира в областта J1:L22 на работния лист, т.е. данните от снимката. Тези данни може да бъдат в различен от работния лист, също така и в друг файл - съответно и формулата би изглеждала малко по-различно в този случай.

Формулата търси кода/номера в клетка B2 в областта K2:K22 и ако го намери връща текста от съответния ред на колона L. Ако не намери съвпадение, започва да търси записаният код/номер в клетка A2 в областта J2:J22 и ако го намери, връща текста от съответния ред на колона L. Ако и в колона J не намери съвпадение връща грешка #N/A.

На снимката се вижда по-добре как са разположени данните за примера.

Надявам се, че съм успяла да помогна!

Силвия, това много добро решение - комбинация VLOOKUP + IFERROR. От практически опит съм установила, че VLOOKUP върши повече работа, отколкото множество IF-ове!

Напълно съм съгласна, г-жо Вълкова. Тъй като формулите с IF функции във въпроса търсят определен брой първи знаци, допускам че е възможно тези кодове/номера да само част от низа символи. В този случай не се сещам за "разумен" вариант и с VLOOKUP.

А защо vlookup не работи при мен, може би трябваше да уточня, че след всеки код (първите цифри от таблицата) следват в самия код още 13 цифри, които всеки път са различни, дали затова не се получава vlookup-а? Защото досега само IF ми помага да получа желания резултат.

Много благодаря за старанието и отговорите на г-жа Данаилова! :)

Здравейте, Валентина,

Да, това е причината VLOOKUP в този си вариант да не ви върши работа.

Хрумва ми вариант и с VLOOKUP, който би свършил работа във вашия случай.

Винаги ли имате още 13 цифри след описаните знаци в базата ви данни?

Ето и формула, която е комбинация от функции IFERROR, VLOOKUP, LEN и LEFT. Тя ще върши работа ако винаги имате 13 знака след описаните в базата ви с данни.

=IFERROR(VLOOKUP(LEFT(B2;LEN(B2)-13);$K$2:$L$22;2;0);VLOOKUP(LEFT(A2;LEN(A2)-13);$J$2:$L$22;3;0))

В случая с LEN се изчислява броят знаци в анализираните клетки A2 и B2. От изчисления брой знаци се изважда числото 13. Полученият резултат се използва във функция LEFT за брой първи знаци.

Съответно ако в клетка B2 кодът е бил A1111111111111 комбинацията LEFT(B2;LEN(B2)-13) ще върне отговор "A". Този отговор "A" се използва във функция VLOOKUP за търсене на съвпадение в базата с данни. Крайният резултат ще бъде LR.

В този случай (освен ако няма и друга подробност поради която и тази формула не би свършила работа) по-добрият вариант е да се използва горната комбинация от IFERROR, VLOOKUP, LEN и LEFT. Самата формула се състои от 97 знака, докато комбинацията от IF, OR и LEFT има 468 знака. Разбира се броят знаци във формулата с IF, OR и LEFT би бил малко по-малък ако се използват референции към базата с данни вместо изписването на конкретните знаци, но тогава според мен прегледа на формулата ще бъде още по-труден.

Формула с 97 знака се чете и разбира много по-лесно от такава с над 400 знака. Съответно при промяна на базата с данни много по-лесно ще се коригира и самата формула.

Тук може да се създадат две помощни колони (разбира се ако е удачно да има такива в конкретния файл). В тях може да се запишат формулите LEFT(B2;LEN(B2)-13) и LEFT(A2;LEN(A2)-13) като колоната определена за крайния резултат се запише формулата

=IFERROR(VLOOKUP(B2;$K$2:$L$22;2;0);VLOOKUP(A2;$J$2:$L$22;3;0))

В нея вместо B2 ще се запише адреса клетката в която е поставена формулата LEFT(B2;LEN(B2)-13) и вместо A2 ще се запише адреса на клетката с формула LEFT(A2;LEN(A2)-13)

Така при преглед на данните направеният анализ ще бъде още по-четим с използвани още по-кратки формули.

Клетки E3 и G3 са форматирани като след написването на буквата А, съответно В, са добавени 13 интервала, ако 13 е максималният брой символи, които се предполага да има. Името на клетката се написва в полето в ляво горе, като кликнем в Name Box. НЕ трябва да има интервал между думите! След написването, натискаме Enter. Формулата на г-жа Данаилова се вижда. Лек ден и успехи!

Благодаря и на двамата, но за съжаление не винаги са 13 символите, но за повечето случаи може би ще ми помогне. Ще пробвам утре формулата и ще върна отговор. Много благодаря за отделеното време и вниманието!

И друго да уточня, след първите цифри следват 13 цифри, които винаги са различни, а след тях следват още символи, които увеличават броя да символи в дадена клетка, няма да е проблем да се добавят две помощни колони, които биха улеснили работата, но освен горния вариант, който показах, засега нищо друго не можах да измисля, което да дава желания резултат. А често таблицата е с над 1000 реда и няма как да се изгради точен модел за vlookup, за съжаление.

Валентина, ако броят знаци след описаните в базата данни са различен брой (няма значение дали са еднакви) без някаква логика/закономерност, която да послужи за създаване на по-кратка формула, аз не се сещам за друг вариант освен формулата с IF, OR и LEFT. Надявам се поне тя да ви помогне да не използвате три формули.

Вариант е също така с цел избягване на технически грешки да използвате макрос или да съхраните някъде формулата вярно записана и когато ви потрябва да я копирате, за да не се налага да я изписвате всеки път.

Освен клетки, знаете, че може да форматирате цели колони и да добавите още интервали, колкото мислите, че биха могли да се появят в бъдеще. Аз написах 13, защото тази цифра се коментираше, но нищо не пречи броя им да се увеличи. При изпълнение на формулата, ще се покажат точните стойности. Лек ден и успехи!

Здравейте Силвия, Благодаря ви за помощта, но за съжаление формулата не работи,възможно ли е, скобите в края не са достатъчни?

Копирала съм формулата директно от файла, в който я съставих. Там тя работи. Ако искате изпратете ми примерен файл, където не се получава за да видя какъв точно е проблема - silvi612@abv.bg

Здравейте Валентина,

все пак има известен алгоритъм в клетките и бих опитал с комбинация от Len, Search и... пак Search! Search ще ви върне поредния номер на определен стринг/знак в клетката.

Да кажем, ако търсите знак интервал в "Иван Петров" (в клетка а1)- ще получите 5. =Search (" ", a1). А ако искате да извадите само "Петров", то тогава и трябва =Right(A1, Len(a1)-Search(" ",a1)).

По сложното става ако имате следното:

в А1 - Иван Петров Тодоров в А2 - Димитрина Георгиева Михайлова

а вие искате да вадите само фамилните имена. Този проблем е близък до вашия до известна степен - фамилното име започва след първият интервал след второто име. Или с други думи вие трябва 1) да намерите първия интервал, както по-горе, с Иван Петров, и 2) пак със Search да намерите втория. В синтакса на Search имате трети аргумент, който е "starting symbol" - т.е. откъде Ексел да почне да търси. Е, ако имате "знак"-интервал-"11 знака"-интервал-"още символи", то вие спокойно можете да опитате така да намерите тези "още символи" с комбинация от Mid, Len и Search

Здравейте Витали, съжалявам, но не разбирам как мога да приложа обяснението в моята ситуация? Поздрави

Валентина, искаме да ти помогнем, виж само колко хора се включват. Какво ще кажеш да изпратиш картинка с примерни данни - какво имаш и какво искаш да получиш, така всички заедно ще намерим решението за теб :-)

Здравейте на всички и много Ви благодаря за желанието за помощ. В случая имам всеки ден таблица с над 1000 реда, в която имам различна информация.В колона А имам винаги цифри, а понякога се появяват цифри започващи с буквата А или WQ, а в колона B, имам думи започващи със следните символи, само тези символи са винаги еднакви, а дължината на думите е винаги различна:
G,F,A (s -), R, KIT5YF, KIT5YG - LR D, KIT5YD - SA L - A380 V, WQ 650,WR650,WQ640,WR640, A (без-) A350 KIT5YD533 + KIT5YD534 - SA BF AA, M536 - A400M CFS M000, M524, M538, M491, M498, M530 - A400M APU. Ето и снимка на част от таблицата и желания резултат.

Благодаря за отделеното време на всички!

Ето снимка на таблицата с логиката, как започват думите, видях, че горе се е променило форматирането.

Здравейте, Валентина. Задачата, която задавате, се усложнява от многото различни условия и критерии за колона Резултат. За да избегнете сложни формули с много IFs & VLOOKUPs, опитайте следното: 1. Попълнете подробна таблица с две колони за всички символи и съответстващите им резултати. Някои стойности в колона Резултат ще се повтарят, но по този начин VLOOKUP ще връща коректен резултат. Тази таблица е помощна и от нея ще се попълват съответните кодове (резултати) в основната таблица.

На картинката с 1 и 2 са означени допълнителните кодове, които съм включила в таблицата

Нещо не се получи както трябва с картинката, но пък се вижда добре. Продължавам със следващите стъпки: 2. Забелязах, че в основната таблица, всички кодове имат разделител "-". Функцията Find намира позицията на разделителя "-". Добавям колона за Find.

  1. Колона Резултат се попълва с функция IFERROR, VLOOKUP, LEFT.

  1. За удобство може двете таблици да бъдат дефинирани таблици (Insert --> Table). Това ще Ви улесни, когато допълвате спомагателната таблица.

Надявам се, че това решение ще Ви помогне!

Благодаря за помощта!

Вашият отговор:

Научи компютърните програми онлайн от топ експертите на България
Регистрацията в АУЛА ви дава:
  • 20 безплатни урока
  • Трикове и тънкости за софтуера
  • Отговори на вашите въпроси
  • Име
    Имейл
     


    © 2016 Фондация “Аула”. Всички права запазени.
    Autodesk AutoCAD, Autodesk Inventor, Autodesk Civil 3D, Autodesk Revit, Autodesk 3ds Max, Autodesk Maya, Microsoft Word, Microsoft Excel, SharePoint Online, Microsoft PowerPoint, Microsoft Project, Microsoft, Adobe Photoshop, Adobe Illustrator, Adobe InDesign, Adobe After Effects, Adobe Premiere, Adobe Dreamweaver, V-Ray, Solidworks са регистрирани търговски марки на Adobe Systems Incorporated, Microsoft Corporation, Chaos Software Ltd, Autodesk Inc. и Dassault Systèmes SolidWorks Corp.