В това видео ще разгледаме как да подчертаем приблизителните справки за съвпадение с условно форматиране.
Тук имаме проста справочна таблица, която показва материалните разходи за различни височини и ширини. Формулата в K8 използва функциите INDEX и MATCH за извличане на правилните разходи въз основа на стойностите на ширина и височина, въведени в K6 и K7.
Обърнете внимание, че търсенето се основава на приблизително съвпадение. Тъй като стойностите са във възходящ ред, MATCH проверява стойностите, докато се достигне по-голяма стойност, след което отстъпва назад и връща предишната позиция.
Нека да изградим правило за условно форматиране, за да подчертаем съответстващия ред и колона.
Както винаги при по-сложно условно форматиране, препоръчвам ви първо да работите с фиктивни формули и след това да прехвърлите работеща формула директно в правилото за условно форматиране. По този начин можете да използвате всички инструменти на Excel, когато отстранявате грешки във формулата, което ще ви спести много време.
Първо ще настроя формулата за ширина. Трябва да върнем TRUE за всяка клетка в ред 7, където съвпадащата ширина е 200.
Това означава, че започваме формулата си с $ B5 = и трябва да заключим колоната.
= $ B5 =
Сега не можем да търсим 275 в колоната ширини, защото я няма. Вместо това се нуждаем от приблизително съвпадение, което намира 200, точно като нашата формула за търсене.
Най-лесният начин да направите това е да използвате функцията LOOKUP. LOOKUP автоматично прави приблизително съвпадение и вместо да връща позиция като MATCH, LOOKUP връща действителната стойност на съвпадението. И така, можем да напишем:
$ B5 = ПРЕГЛЕД ($ K $ 6, $ B $ 6: $ B $ 12)
С нашата входна ширина за стойност на търсене и всички ширини в таблицата за вектор на резултата.
Ако използвам F9, можете да видите стойността LOOKUP връща.
Сега, когато въведа формула в таблицата, получаваме TRUE за всяка клетка в реда с ширина 200.
Сега трябва да разширим формулата, за да съответства на колоната с височина. За да направя това, ще добавя функцията ИЛИ и след това втора формула, която да съответства на височината.
Ще започнем формулата по същия начин, но този път трябва да заключим реда:
= B $ 5
След това използваме функцията LOOKUP отново с височина за справочна стойност и и всички височини в таблицата като вектор на резултата.
= ИЛИ ($ B5 = ПРЕГЛЕД ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = ПРЕГЛЕД ($ K $ 7, $ C $ 5: $ H $ 5))
Когато копирам формулата в таблицата, получаваме TRUE за всяка клетка в съответстващата колона и всяка клетка в съответстващия ред - точно това, от което се нуждаем за условно форматиране.
Мога просто да копирам формулата в горната лява клетка точно и да създам ново правило.
Сега, ако променя ширината или височината, подчертаването работи както се очаква.
И накрая, ако искате да подчертаете само самата стойност за търсене, това е проста промяна. Просто редактирайте формулата и заменете функцията ИЛИ с функцията И.
= И ($ B5 = ПРЕГЛЕД ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = ПРЕГЛЕД ($ K $ 7, $ C $ 5: $ H $ 5))
Разбира се
Условно форматиранеСвързани преки пътища
Въведете едни и същи данни в множество клетки Ctrl + Enter ⌃ + Return Показване на диалоговия прозорец Специално поставяне Ctrl + Alt + V ⌘ + ⌃ + V Превключване на абсолютни и относителни препратки F4 ⌘ + T
