0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро перейти к следующей ячейке с данными в Excel?

Как быстро перейти к следующей ячейке с данными в Excel?

В этой статье рассматриваются надстройка Power Query в Excel 2010/2013, группа вкладок «Загрузка и преобразование данных» в Excel 2016 и экран «Получить данные» в Power BI Desktop. В прошлых статьях мы рассмотрели концепцию «Power Query. «.

Может возникнуть ситуация, когда вам понадобится значение ячейки из таблицы Power Query. Как это сделать, показано в статье с использованием редактора запросов и редактора кода. Также подробно рассматриваются дополнительные возможности последнего. Кстати, эта тема частично рассматривается в главе M книги Power Query Криса Вебба, но она уже несколько устарела.

Ссылка на значение ячейки в Редакторе запросов

Ссылка на значение ячейки в Редакторе кода

Рассмотрим эти три шага: – Источник – получаем данные из таблицы Excel
– «Измененный тип» – устанавливаем тип данных для трех столбцов в целочисленный
– poleB – возвращает значение ячейки из второй строки столбца В (строки начинаются с 0).

Вы можете использовать этот код для обращения к отдельным ячейкам таблицы, ссылаясь на имя столбца и номер строки, повторяя, что нумерация строк начинается с нуля. Поэтому выражение:

«Измененный тип»<1>[poleB]

Вернет значение ячейки из второй строки столбца poleB. Аналогично, выражение

«Измененный тип»<0>[poleC]

Возвращает значение 3, соответствующее первой строке колонки PoleC.

Отметим, что ссылки на столбец и строку могут идти в любом порядке, и выражение #»Измененный тип»<1>[poleB] вернёт то же самое, что и

«Измененный тип»[poleB]

В некоторых случаях, как вы вскоре узнаете, может иметь значение, как расположены строки и столбцы.

Ссылка на отсутствующие строку или столбец

Что произойдет, если вы используете ссылку на отсутствующий столбец и/или строку? Конечно, мы получим сообщение об ошибке. В качестве примера давайте запишем его:

Оба выражения вернут ошибку, поскольку в таблице нет строки 5 и столбца PoleD.

Оператор «?» вместо того, чтобы выдать ошибку, выдает значение NULL. оператор после ссылки. Например, выражение

«Измененный тип»<1>[poleD]?

Вернет null вместо сообщения об ошибке:

Читайте так же:
Как автоматически запускать макрос при изменении результата формулы ячейки?

Но будьте осторожны! Выражения

«Измененный тип»<4>?[poleB]

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

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

«Измененный тип»[poleB]<4>?

Или применяя оператор «?» к обеим ссылкам:

«Измененный тип»<4>?[poleB]?

Эффект первичного ключа

Знаете ли вы, что таблицы Power Query могут содержать первичный ключ (т.е. один или несколько столбцов, значения которых однозначно идентифицируют каждую строку), определяемый самим плагином? Нет? Неудивительно, что это совсем не очевидно из пользовательского интерфейса. Однако существует несколько ситуаций, в которых Power Query определяет первичный ключ таблицы, в том числе :

  1. Когда импортируются данные из таблицы реляционной базы данных, подобной SQL Server, и таблица уже имеет первичный ключ.
  2. Когда используется кнопка Удалить повторения чтобы убрать повторяющиеся значения из столбца или столбцов, скрытно вызывается функция Table.Distinct()
  3. Когда к таблице применяется функция Table.AddKey()

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

(Кстати, вы можете использовать Table.Keys(), чтобы посмотреть, какие ключи определены для таблицы Power Query).

Повторим действие с деталью «Углубление» после удаления дубликатов. Получаем следующее:

И последнее, но не менее важное: обратите на это внимание! Ссылка на первичный ключ сделана вместо ссылки на номер строки.

Замечания напоследок о производительности

Возможность ссылок на отдельные значения невероятно полезна в определенных типах запросов и расчётов. Однако стоит помнить, что зачастую существует несколько способов решения задачи, и не все они одинаково хороши.
Одно очевидное применение техники описанной в статье – запись предыдущих вычислений там, где необходимы ссылки на значения предыдущей строки таблицы. Но по опыту известно, что запись расчетов, использующих ссылки на строку/столбец не даёт осуществлять Query Folding («квэри фолдинг» — термин, означающий передачу тяжелых операций по обработке запросов на сторону сервера при работе с совместимой базой данных, на текущий момент это MS SQl, прим. пер.), и ведет к снижению производительности.
Возможно, альтернативные подходы (некоторые описаны в статьях Implementing Common Calculations In Power Query и Join Conditions in Power Query, Part 2: Events-In-Progress, Performance and Query Folding) будут лучшим выходом.

Читайте так же:
Как в Excel разбить текст на отдельные столбцы заглавной буквой?

Здесь нет общих правил, которые можно было бы посоветовать, вы должны сами попробовать различные способы.

39 самых полезных горячих клавиш в Excel

Подборка из 39 горячих клавиш в Excel. Они ускорят работу в программе.

F2: редактировать выделенную ячейку

Вы немедленно войдете в режим редактирования, нажав F2 на клавиатуре.

F4: повторение последней команды или действия + смена типа ссылок абсолютный/относительный

Использование клавиши F4 для циклического перебора абсолютных и относительных ссылок, когда ссылка на ячейку или диапазон выделена в формуле. Кроме того, клавиша F4 переключает тип ссылки на ячейку или диапазон (абсолютная/относительная).

CTRL + ALT + F9: обновляет формулы во всех ячейках на всех листах всех открытых книг

При нажатии этой комбинации ячейки с формулами в открытых файлах Excel будут пересчитаны.

F11: создать диаграмму

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

ALT: отобразить подсказки клавиш на ленте

Нажмите клавишу ALT на клавиатуре, и над пунктами меню на панели инструментов Excel появятся буквы. Нажатие этих букв на клавиатуре сразу же активирует нужный пункт меню на ленте.

ALT и = : сумма выделенного диапазона с данными

Если у вас есть диапазон данных, который вы хотите суммировать — нажмите сочетание клавиш ALT и = и в следующей после диапазона ячейке появится сумма выделенных вами значений.

ALT + Enter: перенос строки внутри ячейки

Это сочетание клавиш будет полезно тем, кто пишет много текста внутри одной ячейки. Для большей наглядности текста — его следует разбивать на абзацы или начинать с новых строк. Используйте сочетание клавиш ALT + Enter и вы сможете создавать строки с текстом внутри одной ячейки.

Читайте так же:
Как быстро объединить текст и дату в одну ячейку в Excel?

CTRL + Page Down: перейти на следующий лист Excel

Вы можете использовать это сочетание клавиш для перехода к следующему листу в Excel (справа).

CTRL + Page Up: перейти на предыдущий лист Excel

Нажав эту комбинацию, вы перейдете на предыдущий лист файла Excel (слева).

CTRL + ‘: показать формулу в ячейке

Использование этой комбинации в ячейке с формулой покажет диапазон ячеек, участвующих в формуле.

CTRL + Backspace: демонстрация активной ячейки

Если вы потерялись в файле и не можете найти ячейку, на которой находится курсор, вы можете нажать эту комбинацию клавиш, и система вернет вас обратно в область файла с выбранной ячейкой.

CTRL + Shift + #: применить формат даты к ячейке

Это сочетание клавиш мгновенно изменяет формат ячеек на стандартный формат даты.

CTRL + K: вставить гиперссылку

Теперь вы знаете, как быстро вставить гиперссылку.

CTRL + Shift + $: применить денежный формат к ячейке

Нажмите CTRL + Shift + $ в любой ячейке, и она будет отображаться в финансовом формате.

CTRL + Shift + &: добавить внешние границы ячейки

Используйте эту комбинацию, чтобы мгновенно установить внешние границы выбранной ячейки или области.

CTRL + B: выделить жирным

Это позволит быстро выделить нужные ячейки жирным шрифтом.

CTRL + I: выделить курсивом

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

CTRL + U: сделать подчеркивание

Выберите ячейку и нажмите CTRL + U для ее подчеркивания.

CTRL + S: быстрое сохранение

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

CTRL + C: скопировать данные

Выделите диапазон с данными и нажмите CTRL + C — это позволит вам скопировать выделенные данные.

CTRL + V: вставить данные

Чтобы вставить данные в ячейку, нажмите CTRL+V

CTRL + X: вырезать данные

Используйте CTRL + X для вырезки данных.

CTRL + Shift +

: назначить общий формат ячейки

Таким образом можно присвоить выбранной ячейке общий формат.

CTRL + Shift + %: задать процентный формат ячейки

Значения ячеек преобразуются в проценты с помощью этой комбинации.

CTRL + Shift + ^: назначить экспоненциальный формат ячейки

Нажатие этих клавиш придаст выделенной ячейке экспоненциальную форму.

CTRL + Shift + @: присвоить стиль даты со временем

С помощью этих клавиш вы можете назначить дате формат времени.

Читайте так же:
Как автоматически отправлять электронную почту с полем cc или bcc с помощью функции mailto в Excel?

CTRL + Shift + !: назначить числовой формат ячейки

Используя этот метод, можно определить числовой формат любой ячейки.

CTRL + F12: открыть файл

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

CTRL + Пробел: выделить данные во всем столбце

Эта комбинация клавиш выделяет все ячейки в столбце.

CTRL + E: Мгновенное заполнение

Вызов функции Instant Fill и применение к выбранному диапазону ячеек

CTRL + ; вставить текущую дату

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

CTRL + Shift + ; вставить текущее время

В данном случае в ячейку вводится текущее время.

CTRL + A: выделить все ячейки

Если вы хотите выделить все ячейки на листе — просто нажмите CTRL + A.

CTRL + D: скопировать формулу из ячейки выше

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

CTRL + F: поиск

Найти данные на листе.

CTRL + H: поиск и замена

Поиск и замена данных на листах Excel.

Shift + Пробел: выделить всю строку

Всю линию листа можно выделить, нажав Shift + Пробел.

CTRL + Shift + Стрелка вниз (вправо, влево, вверх): выделить ячейки со сдвигом вниз

Эти сочетания клавиш позволяют выделить диапазон ячеек. Особенностью этого выделения является то, что оно происходит мгновенно перед первым пробелом в данных.

CTRL + Стрелка вниз (вправо, влево, вверх): быстрое перемещение по данным

Эти клавиши позволяют быстро перемещаться по области данных.

Как изменять и форматировать данные в таблице

Вы можете добавлять данные в ячейки таблицы, а также редактировать и форматировать их.

Как изменять данные в ячейках

  1. Откройте файл в Google Spreadsheets.
  2. Щелкните по пустой ячейке. Если ячейка содержит данные, дважды коснитесь ее.
  3. Введите данные.
  4. Если вы хотите переместить текст в ячейке в новую строку, нажмите ⌘ + Enter на Mac или Ctrl + Enter в Windows.
  5. Когда вы закончите, нажмите Enter.

Как форматировать данные в одной или нескольких ячейках

  1. Откройте файл в Google Spreadsheets.
  2. Щелкните по ячейке, затем, удерживая кнопку мыши, наведите курсор на ячейки, которые нужно выделить. Вы также можете выделить ячейки, удерживая кнопку ⌘ на Mac или клавишу Ctrl на Windows.
  3. Чтобы отформатировать данные в ячейке, используйте кнопки на панели инструментов в верхней части экрана.
Читайте так же:
Как в Excel удалить все листы кроме указанного / текущего?

Как форматировать данные

Вот некоторые способы форматирования ячеек или данных в них. Каждый из них расположен над рабочей областью документа.

  • Отменить
  • Повторить
  • Полужирный
  • Курсив
  • Зачеркнутый
  • Начертание и размер шрифта
  • Цвет текста
  • Цвет заливки ячейки
    • Один цвет
    • Чередующиеся цвета
    • Цвет границы
    • Стиль границ

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

    Как выравнивать объекты и менять их размер

    1. Выберите ячейку.
    2. Нажмите Формат цифр в верхней части экрана.
    3. Выберите формат в открывшемся меню. Если нужного формата нет в списке, в нижней части открытого меню нажмите Другие форматы и выберите соответствующий пункт, например, Другие форматы даты и времени или Другие форматы чисел.
    4. Если нужного формата нет в списке, нажмите Другие форматы в нижней части открывшегося меню и выберите нужный вариант, например Другие форматы даты и времени или Другие форматы чисел.

    В справочном центре можно узнать, как форматировать числа.

    Как добавить тему

    При использовании тем вы можете настроить стиль сразу для всей таблицы.

    1. Откройте файл в Google Spreadsheets.
    2. В верхней части страницы нажмите на тему FormatSelect.
    3. Выберите доступную тему или нажмите Настроить, чтобы создать свою собственную.

    Примечания

    • При создании пользовательской темы будет сохранена самая последняя версия документа.
    • Цвета из текущей темы будут доступны в тексте и палитрах цвета заливки.

    Что тема меняет в таблице

    • Цвет шрифта и текста в ячейках, графиках и сводных таблицах.
    • Цвет текста в гиперссылках.
    • Цвет фона графиков.
    • Цвет серии на графиках.
    • Цвет фона поворотных таблиц.

    Примечание. Изменения форматирования объекта будут переопределять форматирование, настроенное в теме.

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector