Сьогоднішнє запитання з семінару в Атланті Power Excel.
У мене є дані, відформатовані з кожним другим рядком сірим кольором. Коли я вставляю два нові рядки посередині даних, обидва нові рядки закінчуються сірим кольором, а не слідують шаблону.
Погляньте на книгу на малюнку нижче. Рівни з парними номерами мають сіру заливку.
Коли ви вибираєте A5: A6 та Insert Rows, ви отримуєте два нові порожні рядки. Але обидва ряди сірі.
У мене є рішення, але для цього потрібно повернутися до математики 3 класу.
У 2-му або 3-му класі, коли ви вперше навчалися підрозділу, ви могли б пам’ятати таке:
- 4 Входить у 9: 2 із залишком 1 або 2R1
- 5 Входить до 17: 3 із залишком 2 або 3R2
У початковій школі ви називали це залишком. Я вважаю, багато людей це пам’ятають.
Але пізніше, у середній школі, коли більшість з нас перестали звертати увагу на математику, вони ввели поняття під назвою Modulo.
17 Модуль 5 - це 2.
Modulo походить від латинського слова Modulus.
По-англійськи це говорить: "Розділіть 5 на 17, а залишок - 2"
В Excel ви можете розрахувати модуль за допомогою =MOD(17,5). Ось деякі результати MOD:
Ця стаття повинна бути про затінення рядків сірим кольором. Чому вся теорія математики? Тому що MOD вирішує цю проблему!
Перегляньте стовпець А нижче. =ROW()Функція повідомляє вам , що рядки ви перебуваєте.
І стовпець C ділить число 2 на число рядка і дає залишок. Для парних рядків залишок дорівнює 0. Для непарних рядків залишок дорівнює 1.
Налаштуйте умовне форматування, щоб перевірити, чи MOD(ROW(),2)=0:
- Виділіть усі комірки у ваших даних.
- Головна, умовне форматування, нове правило.
- За допомогою формули визначте, які клітини потрібно форматувати.
- Формула така
=MOD(ROW(),2)=0 - Клацніть Формат … і застосуйте сіру заливку.
- Клацніть OK.
Кожен другий рядок відформатований сірим кольором. Але - прекрасна перевага - коли ви вставляєте нові рядки, вони зберігають чергування сіро-білого кольору. (Перегляньте відео нижче, щоб побачити це в дії.)
Ви можете розширити цю концепцію. Скажімо, ви хочете два ряди зеленого, а потім два ряди білого. Обчислити =MOD(ROW(),4). Чотири можливі відповіді - 0, 1, 2 та 3. Налаштуйте правило, щоб перевірити, чи результат <2, і відформатуйте ці рядки зеленим кольором.
Переглянути відео
Стенограма відео
Дізнайтеся Excel з подкасту Епізод 2209: Вставка змінних рядків.
Привіт, ласкаво просимо до мережі MrExcel, я Білл Джелен. Це класика - я був в Атланті, і хтось описав, що у них є така електронна таблиця. Тепер, привіт, я не хочу слухати, що ви не повинні вести свою електронну таблицю так; у них була така електронна таблиця, і вони сказали: "Ось наша проблема. У нас є 2 фізичні рядки для кожних даних, і чи варто це робити, чи ні, коли я вставляю 2 рядки, Alt + I + R і вставляю 2 рядки всі 3 ряди, що страшно стають сірими! " Я сказав: "Ах, так, у мене є рішення для цього".
Але, перш ніж ми дійдемо до цього рішення, ми повинні повернутися, наприклад, до третього класу / другого класу, коли ви вперше навчитесь ділити. Запам'ятай це? 4 переходить у 9: 9/4, і відповідь буде 2 із залишком 1. 4 переходить у 9 2 рази; 4х2 = 8; залишився 1 залишок. Отже, ми б написали 2R1. 5 потрапляє в 17 3 рази з залишком 2; 3R2. Ви пам’ятаєте залишки, правда? Це тому, що ми всі звертали увагу в третьому класі. Це було важко ще в третьому класі. У старшій школі ніхто вже не звертав уваги на математику - ми звертали увагу на симпатичну дівчину, яка сиділа перед нами, - і тому ви не пам’ятаєте, коли ваш вчитель математики навчав вас про щось, що називається Modulo.
Modulo схожий на залишки. 17 Модуль 5 - це 2, оскільки 2 - це залишок, коли ви ділите 5 на 17. Ви отримуєте 3 із залишком 2. Отже, якщо ми попросили = MOD (17,5), відповідь буде 2. MOD стоїть для Modulo, що насправді є просто якоюсь вигадливою назвою для залишку. Це залишок. Гаразд, чому всі ці розмови про Modulo?
Отже, я буду використовувати функцію тут = ROW, і = ROW просто повідомляє мені, в якому рядку вона знаходиться (3, 4, 5, 6, 7, 8, 9, 10, 11, 12 …), а потім я ' m збирається взяти = MOD цього рядка, розділений на 2. Гаразд, значить, 2 входить в 3 1 раз із залишком 1. 2 переходить у 4 двічі з залишком 0. Ця формула тут дасть мені лише 2 можливі відповіді: або 0, або 1, 0 або 1. І найкрасивішим є те, що коли я вставляю новий рядок, ці нулі чи одиниці - вони збираються коригувати. Отже, в основному, 1 дасть мені кожен непарний рядок, а 0 - кожен парний рядок. Схоже, тут я хочу виділити парні рядки. Отже, я просто виберу всі ці дані і перейду до умовного форматування. Дім; Умовне форматування; Нове правило; Формат комірки; За допомогою формули визначте, які клітинки потрібно форматувати; і я'm збирається запитати = MOD (ROW (), 2) = 0, і якщо це так, то, що я хочу зробити, це хочу відформатувати це із сірим фоном - заливка сірого; натисніть ОК; натисніть ОК; і bam-- кожен другий рядок сірий, так само, як ми починали в Атланті до того, як я вставив ці два ряди і все зіпсував. Але найгарніше в цьому, коли я вирішую, що мені потрібно більше даних, і вставляю 2 рядки - Alt + I + R - рядки відформатовані в білий та сірий кольори, як я хотів. І я навіть міг вставити тут 2 рядки - Alt + I + R - і він все ще продовжує форматувати в білий та сірий кольори, як я хотів. За модулем - це залишок.так само, як ми починали з Атланти до того, як я вставив ці два ряди і все зіпсував. Але найгарніше в цьому, коли я вирішую, що мені потрібно більше даних, і вставляю 2 рядки - Alt + I + R - рядки відформатовані в білий та сірий кольори, як я хотів. І я навіть міг вставити тут 2 рядки - Alt + I + R - і він все ще продовжує форматувати в білий та сірий кольори, як я хотів. За модулем - це залишок.так само, як ми починали з Атланти до того, як я вставив ці два ряди і все зіпсував. Але найгарніше в цьому, коли я вирішую, що мені потрібно більше даних, і вставляю 2 рядки - Alt + I + R - рядки відформатовані в білий та сірий кольори, як я хотів. І я навіть міг вставити тут 2 рядки - Alt + I + R - і він все ще продовжує форматувати в білий та сірий кольори, як я хотів. За модулем - це залишок.
Добре, тепер, коли у нас це є, давайте зробимо 2 ряди заввишки. У той час, коли ми друкували наші звіти Green Bar, наші звіти Green Bar-- вони не були 1 рядком, 1 рядком, 1 рядком, це були 2 ряди, 2 рядки, а може, навіть 4 рядки, 4 ряди. Добре? Отже, якщо я хочу зробити 2 рядки заввишки - ось наша функція = ROW, а потім я беру = MOD цього номера рядка і ділю його на 4, і ми перетягнемо обидва ці дані вниз і подивимось, що отримаємо. Зараз ми отримаємо 4 можливі відповіді - 0, 1, 2 або 3. Отже, все питання полягає в тому, чи є = MOD (РЯДОК, 4) = 0 або 1, 2 або 3? Ваш вибір. Виберіть усі ці дані тут; Alt + O + D для умовного форматування; Нове правило; «Використовуй формулу»; тоді ми говоримо = MOD (РЯДОК (), 4) = <2 (отже, вибираємо 0, 1, 2, 3. Я хочу виділити кожен інший, так = <2; це означає, що це 0 чи 1? ); і якщо це так, я 'я збираюся вибрати той самий зелений колір, який був у нас у принтерах IBM ще у 1980-х (так, я такий старий); натисніть ОК; натисніть ОК; та БАМ! тепер у нас кожен рядок має 2 висоти, потім 2 висоти, потім 2 висоти, 2 висоти, і знову ж таки, це чудово працює, якщо я вставляю цілу купу інших рядків, я зберігаю цей зелений формат до кінця.
Мені подобається, коли я отримую чудове запитання в Excel, на яке я знаю відповідь, і відповідь передбачає якусь дивну, виразну математику, тому що зараз це виродкова Excel і виродлива математика. Слово Modulo - я не знаю, латинське воно чи грецьке, або хто знає, що це таке, але воно вирішує цю проблему.
MrExcel LIVe: 54 найкращі поради Excel усіх часів. Клацніть на "Я" там, у верхньому правому куті, щоб прочитати більше про цю книгу.
Підсумок цього епізоду від Ради Південної затоки на семінарі IMA в Атланті - привіт, у мене є дані, відформатовані з кожним другим рядком сірого; Коли я вставляю два рядки, обидва рядки сірі. Я маю чудове рішення з умовним форматуванням, але вимагає математики третього класу. Поділіть номер рядка на 2. Який залишок? Це буде або 0, або 1. Отже, налаштуйте умовне форматування, перевірте, чи = MOD (ROW (), 2) = 1, і якщо це так, то заповніть цей рядок сірим кольором. Це працює неймовірно.
Не соромтеся завантажувати книгу - URL-адреса є там в описі Youtube - і налаштуйте функцію = MOD, щоб виділити 3 рядки оранжевим та 1 рядок чирок, і це правильно. Ви також можете створювати огидні книжки, просто використовуючи функцію = MOD. Я хочу подякувати вам, що завітали. Побачимось наступного разу для чергової трансляції від.
Завантажте файл Excel
Щоб завантажити файл Excel: inserting-alternating-рядки.xlsx
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"Дві електронні таблиці краще, ніж жодні."
Джордан Гольдмаєр
