Сегодняшний вопрос с семинара по Power Excel в Атланте.
У меня данные отформатированы, и все остальные строки отображаются серым цветом. Когда я вставляю две новые строки в середину данных, обе новые строки становятся серыми, а не следуют шаблону.
Взгляните на рабочую книгу на изображении ниже. Строки с четными номерами имеют серую заливку.
Когда вы выбираете A5: A6 и Insert Rows, вы получаете две новые пустые строки. Но оба ряда серые.
У меня есть решение, но для этого нужно вернуться к математике в третьем классе.
Во 2 или 3 классе, когда вы впервые учились в отделении, вы могли вспомнить следующее:
- 4 переходит в 9: 2 с остатком 1 или 2R1
- 5 Переход к 17: 3 с остатком 2 или 3R2
В начальной школе вы называли это остатком. Я считаю, что многие люди это помнят.
Но позже, в старшей школе, когда большинство из нас перестали обращать внимание на математику, они представили концепцию под названием Modulo.
17 по модулю 5 равно 2.
Modulo происходит от латинского слова Modulus.
По-английски это гласит: «Разделите 5 на 17, а остаток - 2».
В Excel вы можете рассчитать Modulo, используя =MOD(17,5). Вот некоторые результаты MOD:
Эта статья должна быть посвящена закрашиванию строк серым цветом. К чему вся математическая теория? Потому что MOD решает эту проблему!
Посмотрите столбец A ниже. =ROW()Функция сообщает вам , что строки вы находитесь.
А столбец C делит число 2 на номер строки и дает остаток. Для четных строк остаток равен 0. Для нечетных строк остаток равен 1.
Настройте условное форматирование, чтобы проверить MOD(ROW(),2)=0:
- Выделите все ячейки в ваших данных.
- Домашняя страница, условное форматирование, новое правило.
- Используйте формулу, чтобы определить, какие ячейки нужно форматировать.
- Формула
=MOD(ROW(),2)=0 - Щелкните Форматировать… и примените серую заливку.
- Щелкните ОК.
Все остальные строки имеют серый цвет. Но - прекрасное преимущество - когда вы вставляете новые строки, они сохраняют чередующийся серый / белый цвет. (Посмотрите видео ниже, чтобы увидеть это в действии.)
Вы можете расширить эту концепцию. Допустим, вам нужно два ряда зеленого цвета, за которыми следуют два ряда белого цвета. Рассчитайте =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 и, если это так, то я хочу отформатировать это с серым фоном - заполнить серым; нажмите ОК; нажмите ОК; и бац - все остальные строки серые, точно так же, как мы начинали в Атланте до того, как я вставил эти две строки и все испортил. Но самое прекрасное в этом - когда я решаю, что мне нужно больше данных, и вставляю 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 ряда в высоту. Раньше, когда мы печатали наши отчеты с зеленой полосой, наши отчеты с зеленой полосой - это не была 1 строка, 1 строка, 1 строка, это были 2 строки, 2 строки или, может быть, даже 4 строки, 4 строки. Хорошо? Итак, если я хочу сделать 2 строки высотой - вот наша функция = ROW, а затем я беру = MOD этого номера строки и делю его на 4, и мы перетаскиваем их вниз и посмотрим, что у нас получится. Сейчас мы получим 4 возможных ответа - 0, 1, 2 или 3. Итак, весь вопрос в том, является ли = MOD (ROW, 4) = 0 или 1, 2 или 3? Твой выбор. Выберите все эти данные здесь; Alt + O + D для условного форматирования; Новое правило; «Используйте формулу»; тогда мы говорим = MOD (ROW (), 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. Итак, настройте условное форматирование, проверьте if = MOD (ROW (), 2) = 1 и, если это так, заполните эту строку серым цветом. Работает потрясающе.
Не стесняйтесь скачать книгу - URL-адрес находится внизу в описании Youtube - и настройте функцию = MOD, чтобы выделить 3 строки оранжевым и 1 строку бирюзовым, и это правильно. Вы также можете создавать отвратительно выглядящие книги, просто используя функцию = MOD. Я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз для другого сетевого вещания от.
Скачать файл Excel
Чтобы загрузить файл Excel: Insert-alternating-rows.xlsx
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
«Две таблицы лучше, чем ничего».
Джордан Голдмайер
