Как отразить в Excel

Обновлено: 03.07.2024

Здравствуйте!
Есть ли чистый способ зеркального отображения на листах, чтобы при вставке или удалении строки на основном листе зеркальный лист автоматически обновлялся? Прямо сейчас у меня есть обходной путь, который требует двух действий вручную.

Вот что у меня есть. На вкладке ввода я помещаю имя, зарплату и часть времени, отработанного по программе для каждого сотрудника.

Затем на вкладке расчетов я вычисляю сумму в долларах. Я сделал формулы, поэтому, если я добавляю или удаляю строки на листе ввода, я могу перенумеровать столбец А, и изменения передаются на лист расчета.

Формулы ячеек
Диапазон	Формула
C1:G1	C1	=Input!E1
B2:B18	B2	=VLOOKUP(A2,Input!$A$2:$B$20,2,FALSE)< /td>
C2:G18	C2	=VLOOKUP($A2,Input!$A $2:$X$20,3,ЛОЖЬ)*(ВПР($A2,Ввод!$A$2:$X$20,ПОИСКПОЗ(C$1,Ввод!$A$1:$X$1,0),ЛОЖЬ)/ВПР( $A2,Ввод!$A$2:$X$20,4,ЛОЖЬ))

Это потенциально грязный обходной путь, поэтому мне интересно, есть ли более чистое решение. Я действительно не хочу использовать INDIRECT, потому что это сильно замедлит работу с электронной таблицей, но если это единственный способ сделать это, дайте мне знать, так как мне может понадобиться помощь с этой формулой, я ее не использовал довольно часто.
Большое спасибо!

Факты об Excel

Кто-нибудь скрывает данные о заработной плате в столбце G? Нажмите F5. Тип G1. Войти. Посмотрите в строку формул, пока вы прокручиваете стрелку вниз через G.

6StringJazzer

Известный участник

На втором листе вместо поиска имени (показанного в настоящее время в столбце B) вы можете исключить столбец A из обоих листов и использовать следующую формулу:

и столбец "Имя" на втором листе всегда будет синхронизирован со входным листом независимо от вставок и удалений.

Вы можете использовать ту же логику в других столбцах вместо использования функции ВПР для столбца А. Например, сохраняя предположение, что мы избавляемся от столбца А, формула в столбце С (который станет столбцом Б) становится

Это может показаться странной темой для обсуждения, но я всегда считал, что Excel очень полезен при манипулировании данными и часто быстрее, чем написание одноразовых скриптов.

Одной из наиболее часто используемых функций является кнопка «Текст в столбцы» (находится на ленте «Данные» в Excel), которая разбивает файлы с разделителями табуляцией и запятыми на отдельные столбцы. Но иногда значения могут не иметь одинакового количества столбцов. Например, давайте посмотрим на следующий пример данных:

CN=Computer1, OU=Пользователи настольных компьютеров, OU=BuildingA, DC=TEST, DC=LOCAL
CN=Laptop2, OU=Путешествующие пользователи, OU=Finance, OU=BuildingC, DC=TEST , DC=МЕСТНЫЙ

Представьте, что это двухстрочный образец из большого набора данных, и вы пытаетесь найти здание. Как видите, в первой строке здание находится в 3-м столбце, а во второй строке — в 4-м столбце. Однако здание является предпоследним столбцом с конца, поэтому, если мы реверсируем его, данные будут в столбце 3 для обеих строк.

Давайте поместим наши данные в электронную таблицу Excel

В Excel убедитесь, что вкладка "Разработчик" включена. Если нет, перейдите в «Файл» -> «Параметры» -> «Настроить ленту» и установите флажок «Разработчик» на правой панели.

Перейдите на вкладку "Разработчик" и нажмите кнопку Visual Basic

Щелкните правой кнопкой мыши на ThisWorkbook -> Вставить -> Модуль

Скопируйте и вставьте приведенный ниже код в окно модуля, затем закройте окно Visual Basic, чтобы вернуться к электронной таблице

В Excel теперь должна быть функция RevStr. В ячейке B1 введите =RevStr(A1) и заполните ячейку B2

Теперь вы должны увидеть данные в зеркальном отображении. Скопируйте и вставьте столбец B в качестве значений. Это заменит формулу фактическим текстом, чтобы вы могли его проанализировать.

Предполагая, что вы можете прочитать немного назад, вы увидите, что столбец C теперь содержит данные, содержащие здание.Чтобы вернуть данные в удобочитаемый вид, вставьте столбец и выполните еще одно действие =RevStr(C1)

Возможно, это руководство было более подробным, чем следовало бы, но возможность инвертировать строку — очень удобная функция для определенных типов данных. Надеюсь, это поможет!

Отражение данных в Excel звучит как тривиальная задача, выполняемая одним щелчком мыши, но на удивление такой встроенной опции нет. В ситуациях, когда вам нужно изменить порядок данных в столбце, расположенном в алфавитном порядке или от меньшего к большему, вы, очевидно, можете использовать функцию сортировки Excel. Но как перевернуть столбец с несортированными данными? Или как изменить порядок данных в таблице по горизонтали в строках? Вы получите все ответы через мгновение.

Отразить данные в Excel по вертикали

Проявив немного изобретательности, вы можете разработать несколько различных способов отражения столбца в Excel: с помощью встроенных функций, формул, VBA или специальных инструментов. Подробные шаги по каждому методу приведены ниже.

Как перевернуть столбец в Excel

Чтобы изменить порядок данных в столбце по вертикали, выполните следующие действия:

Добавьте вспомогательный столбец рядом со столбцом, который вы хотите перевернуть, и заполните этот столбец последовательностью чисел, начиная с 1. В этом совете показано, как сделать это автоматически.
Сортировать столбец чисел в порядке убывания. Для этого выберите любую ячейку во вспомогательном столбце, перейдите на вкладку Данные в группу Сортировка и фильтр и нажмите кнопку Сортировать от большего к меньшему (ZA).

Как показано на снимке экрана ниже, при этом будут отсортированы не только числа в столбце B, но и исходные элементы в столбце A с обратным порядком строк:

Теперь вы можете безопасно удалить вспомогательный столбец, так как он вам больше не нужен.

Введите 1 в первую ячейку и 2 во вторую ячейку (ячейки B2 и B3 на снимке экрана ниже).
Выделите ячейки, в которые вы только что ввели числа, и дважды щелкните правый нижний угол выделения.

Вот оно! Excel автоматически заполнит столбец серийными номерами до последней ячейки с данными в соседнем столбце.

Как перевернуть таблицу в Excel

Описанный выше метод также работает для изменения порядка данных в нескольких столбцах:

Иногда (чаще всего, когда вы выбираете весь столбец чисел перед сортировкой) Excel может отображать диалоговое окно Предупреждение о сортировке. В этом случае установите флажок Расширить выбор, а затем нажмите кнопку Сортировать.

Совет. Если вы хотите повернуть данные из строк в столбцы или наоборот, используйте функцию транспонирования Excel или другие способы преобразования строк в столбцы, описанные в разделе Транспонирование в Excel.

Как отразить столбцы в Excel с помощью формулы

Еще один способ перевернуть столбец вверх ногами — использовать следующую общую формулу:

Для нашего примера набора данных формула выглядит следующим образом:

…и безупречно переворачивает столбец A:

Как работает эта формула

В основе формулы лежит функция ИНДЕКС(массив, номер_строки, [номер_столбца]), которая возвращает значение элемента в массиве на основе указанных вами номеров строк и/или столбцов. .

В массиве вы передаете весь список, который хотите перевернуть (в этом примере A2:A7).

Номер строки определяется функцией ROWS. В своей простейшей форме ROWS(массив) возвращает количество строк в массиве. В нашей формуле умное использование относительных и абсолютных ссылок делает трюк «перевернуть столбец»:

Для первой ячейки (B2) функция ROWS(A2:$A$7) возвращает 6, поэтому функция ИНДЕКС получает последний элемент в списке (6-й элемент).
Во второй ячейке (B3) относительная ссылка A2 изменяется на A3, следовательно, ROWS(A3:$A$7) возвращает 5, заставляя INDEX извлекать предпоследний элемент.

Другими словами, ROWS создает своего рода убывающий счетчик для INDEX, чтобы он перемещался от последнего элемента к первому элементу.

Теперь, когда у вас есть два столбца данных, вы можете заменить формулы вычисляемыми значениями, а затем удалить дополнительный столбец. Для этого скопируйте ячейки формулы, выберите ячейки, в которые вы хотите вставить значения, и нажмите Shift+F10, затем V , что является самым быстрым способом применения параметра Excel Special Paste Special > Values.

Как перевернуть столбцы в Excel с помощью VBA

Если у вас есть опыт работы с VBA, вы можете использовать следующий макрос, чтобы изменить порядок данных по вертикали в одном или нескольких столбцах:

Как использовать макрос "Перевернуть столбцы"

Откройте окно Microsoft Visual Basic для приложений ( Alt + F11 ).
Нажмите Вставить >Модуль и вставьте приведенный выше код в окно кода.
Запустите макрос ( F5 ).
Откроется диалоговое окно Перевернуть столбцы, в котором вам будет предложено выбрать диапазон для отражения:

Вы выбираете один или несколько столбцов с помощью мыши, не включая заголовки столбцов, нажимаете ОК и через мгновение получаете результат.

Чтобы сохранить макрос, обязательно сохраните файл как книгу Excel с поддержкой макросов.

Как отразить данные в Excel с сохранением форматирования и формул

С помощью описанных выше методов вы можете легко изменить порядок данных в столбце или таблице. Но что, если вы хотите поменять местами не только значения, но и форматы ячеек? Кроме того, что, если некоторые данные в вашей таблице основаны на формулах, и вы хотите, чтобы формулы не нарушались при переключении столбцов? В этом случае вы можете использовать функцию Flip, включенную в Ultimate Suite for Excel.

Предположим, у вас есть хорошо отформатированная таблица, как показано ниже, где некоторые столбцы содержат значения, а некоторые столбцы содержат формулы:

Вы хотите перевернуть столбцы в таблице, сохранив как форматирование (серое затенение для строк с нулевым количеством), так и правильно рассчитанные формулы. Это можно сделать в два быстрых шага:

Выделив любую ячейку в таблице, перейдите на вкладку Данные Ablebits в группу Преобразование и нажмите «Отразить» > «Отразить по вертикали».
В диалоговом окне Отразить по вертикали настройте следующие параметры:
- В поле Выберите диапазон проверьте ссылку на диапазон и убедитесь, что строка заголовка не включена.
- Выберите параметр "Настроить ссылки на ячейки" и установите флажок "Сохранить форматирование".
- При необходимости выберите Создать резервную копию (выбрано по умолчанию).
- Нажмите кнопку "Перевернуть".

Готово! Порядок данных в таблице изменен на обратный, форматирование сохранено, а ссылки на ячейки в формулах изменены соответствующим образом:

Отразить данные в Excel по горизонтали

До сих пор в этом руководстве мы переворачивали столбцы вверх ногами. Теперь давайте посмотрим, как изменить порядок данных по горизонтали, т. е. перевернуть таблицу слева направо.

Как перевернуть строки в Excel

Поскольку в Excel нет возможности сортировать строки, вам нужно сначала преобразовать строки в столбцы, затем отсортировать столбцы, а затем перенести таблицу обратно. Вот подробные шаги:

Примечание. Если ваши исходные данные содержат формулы, они могут быть повреждены во время операции транспонирования. В этом случае вам придется восстанавливать формулы вручную. Или вы можете использовать инструмент Flip, включенный в наш Ultimate Suite, и он автоматически настроит все ссылки для вас.

Обратный порядок данных по горизонтали с помощью VBA

Вот простой макрос, который может быстро отразить данные в таблице Excel по горизонтали:

Чтобы добавить макрос в книгу Excel, выполните следующие действия. Как только вы запустите макрос, появится следующее диалоговое окно с просьбой выбрать диапазон:

Вы выбираете всю таблицу, включая строку заголовка, и нажимаете ОК. Через мгновение порядок данных в строках будет обратным:

Отразить данные в строках с помощью Ultimate Suite для Excel

По аналогии с переключением столбцов вы можете использовать наш Ultimate Suite для Excel, чтобы изменить порядок данных в строках. Просто выберите диапазон ячеек, которые вы хотите отразить, перейдите на вкладку Данные Ablebits > группу Преобразование и нажмите «Отразить» > «Отразить по горизонтали».

В диалоговом окне Отразить по горизонтали выберите параметры, подходящие для вашего набора данных. В этом примере мы работаем со значениями, поэтому выбираем Вставить только значения и Сохранить форматирование:

Нажмите кнопку Flip, и ваша таблица будет перевернута слева направо в мгновение ока.

Вот как вы переворачиваете данные в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

"Зеркало, зеркало на стене..." Многим из нас трудно работать с файлами Excel, потому что вы можете видеть только одну часть файла за раз… когда вы хотите внести изменения в одну часть модели и посмотреть, как это повлияет на другую часть, вы должны переключиться на другой раздел, да? Точно так же, если вы хотите связать лист с другим листом в том же файле, вы должны продолжать переходить от листа к листу, верно? Было бы неплохо, если бы вы могли работать с двумя или более частями рабочей книги, включая разные листы, как с «зеркалами» или живыми отдельными окнами, чтобы, если вам нужно просматривать их одновременно и легко переключаться между ними, ты сможешь? Да, это было бы… и вы можете сделать это довольно легко, используя некоторые инструменты в разделе «Windows» на вкладке меню «View» в Excel.

Предположим, вы работаете с моделью, подобной изображенной ниже. Обратите внимание, что, как видно из области «Переключение окон» на вкладке «Вид», открыт только один файл с именем «Модель медальона»:

Шаг 1. Создайте зеркальное окно файла. Перейдите на вкладку «Вид» и выберите «Новое окно» (Alt W, N). Это автоматически создаст дубликат версии файла, как показано ниже. Обратите внимание, что теперь в области «Переключение Windows» есть два «файла»: Medallion Model:1 и Medallion Model:2.

Однако эти два «файла» на самом деле не являются разными файлами. Скорее, опция «Новое окно» создает дублирующее «зеркало» файла в отдельном окне. Любые изменения, сделанные в «:1», будут одновременно автоматически отображаться в «:2». По какой-то причине вновь созданное окно не использует тот же размер масштаба или настройки сетки, что и исходное окно, но это можно легко изменить вручную.

Шаг 2. Просмотр обоих зеркал одновременно. Чтобы увидеть окна обоих зеркал одновременно, перейдите на вкладку «Вид» и выберите «Упорядочить все»: (Alt + W + A).

Вы можете выбрать стиль просмотра, который вам больше нравится. Если вы выберете Горизонтальное, это будет выглядеть так:

Как уже упоминалось, все, что вы делаете в одном из окон, будет продублировано в другом.

Шаг 3. Начните работать в обоих окнах. Чтобы создать простой интерфейс для связи листа «Модель» с листом «Сценарии», просто наведите курсор на лист «Сценарии» в одном из окон. Чтобы связать листы, как обычно, введите знак «=» в ячейку, в которой вы хотите создать формулу на листе модели. Чтобы перейти к другому окну, используйте сочетание клавиш CTRL + Tab, а затем используйте клавиши со стрелками, чтобы перейти к ячейке назначения ссылки, затем нажмите Enter:

Сочетание клавиш CTRL + Tab позволяет очень легко перемещаться между окнами.

Читайте также: