Как сделать шаг в excel
Изменение параметров шага запроса (Power Query)
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .
Примечание: Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать. Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция “Скачать и преобразовать” в Excel 2016.
Примечание: Краткое видео о том, как вывести Редактор запросов читайте в конце этой статьи.
Когда вы добавляете шага запроса в Power Query, будет вставлен последовательность действий, следующий за выбранный шаг. При добавлении в любом месте отличное от шаг в конце процесса, убедитесь, что все последующие шаги работать неправильно.
Примечание: Вставка или удаление промежуточного шага запроса может нарушить работу запроса. При попытке вставить новый шаг в Power Query отображается предупреждение Вставка шага.
В этой статье
На следующем рисунке показана редакторе запросов с помощью области Параметры запроса правой части окна. Редактор запросов является где создаются запросов Power Query, уточнение и редактировать.
Добавление шага запроса
Существует два способа добавить шаг в запрос.
Использование параметров запроса
В области Параметры запроса в списке ПРИМЕНЕННЫЕ ШАГИ щелкните шаг запроса.
После выполнения шага запроса, который меняет форму данных шага запроса добавляется под действие выбранного запроса. Дополнительные сведения о том, как изменить форму данных, читайте статью данные фигуры.
Использование формулы
При необходимости щелкните шаг запроса в области Шаги. Шаг запроса добавляется после выбранного шага.
Щелкните значок “формулы” ( ) слева от строки формул.
Новую формулу при их создании в форме =
Новый тип формулу = Class.Function(ReferenceStep[,otherparameters]).
Пример: у вас есть таблица со столбцом Gender и вы хотите добавить в зависимости от его gender столбца со значением «Мс» или «Г». Формула будет = Table.AddColumn ( “Префикс”, если каждая [Gender] = “F”, а затем “Мс” еще “г.”)
Примечание: Когда вы добавляете шаг запроса, в последующих шагах может возникнуть ошибка. Она выдается, если новый шаг изменяет поля, такие как имена столбцов, которые используются в любом из последующих шагов.
Изменение шага
Существует два способа изменить существующий шаг.
Использование параметров запроса
В списке ПРИМЕНЕННЫЕ шаги щелкните шаг, который вы хотите изменить.
Щелкните значок шестеренки ( ) или щелкните правой кнопкой мыши и выберите команду Изменить параметры.
В диалоговом окне для выбранного шага измените значения.
Нажмите кнопку ОК.
Использование строки формул
В списке ПРИМЕНЕННЫЕ шаги щелкните шаг, который вы хотите изменить.
В строке формул при необходимости измените значения в формуле. Дополнительные сведения о формулах Power Query см. Дополнительные сведения о формулах Power Query.
В диалоговом окне редактора запросов щелкните Обновить.
Удаление шага
Нажмите значок слева от шага, который вы хотите удалить ( ) или
Щелкните шаг правой кнопкой мыши и выберите Удалить или Удалить до конца.
Изменение порядка шагов
Вы можете переместить шаг вверх или вниз в области Шаги. При изменении позиции шага в области Шаги один или несколько из шагов могут выполняться неправильно. После изменения порядка шагов убедитесь, что все шаги работают должным образом.
Перемещение шага вверх или вниз в области Шаги
В области Шаги щелкните шаг, который вы хотите переместить.
В контекстном меню выберите команду Переместить вверх или Переместить вниз для перемещения выбранного шага на одну позицию вверх или вниз по списку.
Примечание: Чтобы переместить шаг на несколько позиций, повторите эти действия. Когда шаг находится в верхней или нижней части списка, одна из команд перемещения недоступна.
Примечание: Редактор запросов отображается только при загрузке, редактировании или создании нового запроса с помощью Power Query. В видео показано окно редактора запросов, которое отображается после изменения запроса в книге Excel. Чтобы просмотреть редактор запросов, не загружая и не изменяя существующий запрос в книге, в разделе Получение внешних данных на вкладке ленты Power Query выберите Из других источников > Пустой запрос. В видео показан один из способов отображения редактора запросов.
Шаг #2. Расчеты в Эксель (изучаем Excel с нуля)
Давайте рассмотрим следующий пример.
Допустим, что у нас есть 100 000 рублей и мы их хотим поместить на банковский депозит под 11% годовых с ежемесячной капитализацией.
Ежемесячная капитализация – это присоединение начисленных за месяц процентов к сумме вклада. Соответсвенно, в следующем месяце начисление процентов происходит уже к новой сумме и так далее.
Давайте посмотрим на доход, который мы при этом получим. Для этого создадим соответствующую таблицу.
Сделаем активной ячейку А1, с клавиатуры введем слово « Месяц », затем выберем ячейку B1 и введем в нее « Размер вклада ». Нажмем клавишу Enter .
Вы видите, что название в ячейке В1 перекрыло часть соседней ячейки С1, но это не означает, что в ячейку С1 было что-то помещено. У Эксель есть такая особенность – если соседняя ячейка не занята, а в текущую ячейку информация не помещается из-за ее размеров, то программа выводит весь текст, не ограничиваясь размерами ячейки.
Давайте введем в ячейку С1 « День недели »:
Мы видим (1), что название из ячейки В1 обрезалось границами ячейки С1. Опять же, это не означает, что название было частично стерто. Оно просто не отображается полностью, но если мы обратим внимание на строку формул (2), то обнаружим там содержимое активной ячейки. В строке формулы отображается все содержимое любой выбранной ячейки и мы видим, что введенный нами текст никак не изменился (не обрезался).
Мы можем изменить ширину столбца, чтобы текст ячейки отображался полностью. Для этого наводим указатель мыши на границ между столбцами В и С. Указатель мыши изменится и это означает, что теперь можно захватить границу (нажав и удерживая левую кнопку мыши) и переместить ее в нужную нам сторону, изменив ширину столбца В.
Если же нужно сделать так, чтобы в ячейку помещалась вся введенная нами информация, то наводим указатель мыши на границу между столбцами и дважды щелкаем мышью.
Таким образом мы можем автоматически скорректировать ширину столбцов по ширине размещенного в ячейках текста.
Теперь нам нужно установить табличный курсор в ячейку A2 и ввести название месяца. Например, мы сделали вклад в ноябре и поэтому введем название месяца в ячейку А2 и нажимаем клавишу Enter . Далее можем продолжить ввод названий месяцев – декабрь, январь, февраль и так далее… Но давайте воспользуемся функцией автозаполнения , чтобы автоматизировать этот процесс.
Если присмотреться к табличному курсору, то мы увидим в правом нижнем его углу квадратик, который называется маркером заполнения .
Наведем на него указатель мыши, который сразу же изменится на крестик, и захватим маркер, то есть мы нажмем и будем удерживать нажатой левую кнопку мыши и начнем ее перемещать вниз, выделяя ячейки от А2 до А13. Отпустим левую кнопку мыши и увидим, что Эксель сам заполнил месяцы по порядку:
Эта функция автозаполнения работает аналогично и с днями недели, то есть если мы в соответствующем столбце напишем «понедельник» и затем повторим проделанную операцию, то получим список, в котором дни недели будут идти по порядку.
Чтобы удалить введенные данные, мы должны сначала их выделить. Для этого выбираем первую ячейку, информацию из которой нужно удалить, нажимаем и удерживаем левую кнопку мыши и ведя мышью по таблице останавливаемся на последней ячейке, выделяя весь диапазон. Нажимаем на клавишу Delete , чтобы удалить содержимое выделенных ячеек:
Давайте вернемся к нашей таблице и рассчитаем доход от вклада за первый месяц. Для этого в ячейку В2 введем расчетную формулу:
Формула означает, что сумма нашего вклада в размере 100000 будет умножена на 11%, а поскольку процентная ставка годовая, то делим все на 12 месяцев.
Если ввод данных в ячейке начинается со знака равенства, то Эксель автоматически понимает, что сейчас последует формула, которую нужно вычислить. После нажатия клавиши Enter обнаружим в ячейке В2 вычисленное значение (1, см. рис. ниже), при этом если мы выберем ячейку В2, то в поле формулы сможем увидеть не вычисленное значение, а формулу (2) и при необходимости можем ее отредактировать в этом поле, что мы сейчас и сделаем.
Дело в том, что мы получили размер дохода от вклада за месяц, а нам нужно видеть всю сумму целиком, поэтому добавим в формулу размер вклада. Формула будет выглядеть так: =100000*11%/12+100000
Далее нам нужно определить размер дохода в следующем месяце.
Получаем формулу: =B2*11%/12 .
То есть мы сумму вклада не вводим вручную, а берем готовое значение из ячейки B2, указав ее адрес. При этом обратите внимание на то, что адрес ячейки В2 в формуле имеет цвет и сама ячейка обрамляется рамкой такого же цвета.
Ну а чтобы увидеть, какова у нас будет сумма вклада во втором месяце, изменим формулу, добавив значение размера вклада за прошлый месяц. То есть к уже введенной формуле необходимо прибавить значение, находящееся в ячейке В2. Мы можем ввести адрес ячейки вручную с клавиатуры или укажем ячейку мышью:
Этот способ ввода формул более удобный, так можно просто указать нужную ячейку, а не искать ее адрес.
Ну а теперь нам нужно повторить тоже самое для следующего месяца. Формула будет такая: =B3*11%/12+В3
Давайте проанализируем ситуацию. Для удобства включим отображение в таблице формул, а не значений. Для этого на вкладке Формулы (1, см. рис. ниже) включим инструмент Показать формулы (2). В итоге в таблице будут отображаться формулы, а не значения (3).
Если проанализируем уже введенные формулы, то увидим, что в последующих формулах у нас будет изменяться только адрес ячейки в столбце В. Это означает, что формула отличается только адресом ячейки !
Что нам это дает? А дело в том, что мы снова можем воспользоваться автозаполнением данных. Да-да, тоже самое, что мы делали с месяцами!
Точно также захватываем за край табличный курсор на ячейке В3 и перемещаем его до ячейки В13.
Если мы теперь посмотрим на вставленные в ячейки формулы, то увидим, что они отличаются только значениями адресов ячеек столбца В – в каждой последующей формуле адрес ячейки увеличивается на единицу:
Отличается только формула в ячейке В2, в которой находится сумма вклада – 100000 рублей.
Давайте несколько улучшим нашу таблицу и сделаем так, чтобы сумма вклада задавалась отдельно.
С помощью мыши выделим все ячейки с введенные нами данными и немного переместим их. Для этого наведем указатель мыши на границу выделения и он изменит свой вид, указывая на то, что теперь мы можем захватить область мышью и переместить ее. Переместим выделенную область на пять строк ниже:
Если мы теперь обратим внимание на формулы, то обнаружим, что значения адресов ячеек в них также автоматически изменились и поэтому расчет по прежнему отталкивается от первой строки нашей таблицы, а точнее от ячейки, которая теперь имеет адрес А7.
Это свойство Экселя очень упрощает нам создание всевозможных таблиц. Более подробно об этом поговорим в следующий раз. Пока же давайте создадим отдельную графу для суммы вклада – в ячейку А1 введем название всей нашей таблицы – « Расчет дохода от вклада », а в ячейку А3 – « Вклад ». Далее в ячейку В3 вынесем сумму вклада – 100 000 рублей:
Теперь нужно отредактировать формулу в ячейке В7 – заменим значение вклада на значение из ячейки В3. Для этого в строке формулы выделим сумму двойным щелчком мыши на ней и укажу ячейку В3:
Можем выйти из режима отображения формул (отключим инструмент Показать формулы на вкладке Формулы ) и проверим работу таблицы, изменив сумму вклада:
Таким образом значения в таблице зависят от суммы вклада, то есть от значения ячейки В3.
Мы можем вынести в отдельную строку процентную ставку, чтобы была возможность ее также изменять. Но для этого нам понадобятся еще кое-какие дополнительные сведения, о которых речь пойдет на следующем шаге .
Если вам удобнее воспринимать информацию в видео-формате, то посмотрите ролик:
Если вам интересен Эксель, то подписывайтесь на этот Дзен-канал или мой видео канал на YouTube.
Мгновенное заполнение в Excel
Одной из самых гениальных и полезных, на мой взгляд, функцией Excel является «Мгновенное заполнение» (доступно с Microsoft Office 2013 года). Оно творит такие чудеса, что за пару движений пальцами вы сэкономите время и нервы от выполнения огромной рутинной работы. Вместе с тем функция проста как дважды два и подойдёт для пользователей любого уровня. Почувствуйте себя всемогущим, мгновенно решив сложные задачи, это как щелчок Таноса, только лучше)) Заинтриговал?)) Теперь поподробней:
1. У нас имеется таблица с фамилиями, именем и отчеством. Из неё нам нужно сделать «Фамилию и инициалы». Для этого мы в соседнюю строку вводим как это должно быть, затем при вводе на следующей строке у нас появляется предлагаемый вариант, если он нас устраивает, то нажимаем «Enter», если список не появился жмём Ctrl+ E и вуаля, у вас готов весь список по заданной форме.
Также можно вписать только Фамилию и имя и получить желаемый результат, можно вывести инициалы вперёд, можно оставить только фамилию, в общем вариантов много и Excel сделает как вам нужно
Здесь есть одно правило, вводимый текст должен быть непосредственно рядом с исходным текстом, не важно слева или справа, но, чтобы рядом, иначе выйдет ошибка
Также значения из одного столбца с текстом можно разбить на три разных. Заполняем каждый столбец как нам нужно, нажимаем Ctrl+E на нижней пустой ячейке и получаем готовый результат.
Также он работает и в обратную сторону, больше не нужны формулы СЦЕПИТЬ, ЛЕВСИМВ, ПРАВСИМВ, ПСТР и т.д.
Можно задать регистр и сделать фамилии, написанные верхним регистром или полностью капсом. Можно исправлять слова с НеПрАвИлЬнЫм регистром. Если между словами нет пробела, это тоже не проблема, главное, чтобы Ф.И.О. начиналось с заглавной буквы, для определения начала слова
Можно наоборот из заглавных букв сделать печатные, но в этом случае может неправильно отобразиться результат, в этом случае нажимаем на значок с молнией, где отменям мгновенное заполнение, затем вводим ещё раз в следующую строку и нажимаем Ctrl+ E и в этот раз отобразится как нужно.
2. Вы восхищены его супер возможностями? Тогда возрадуйтесь, потому что это ещё не всё)) Есть возможность склейки текста. Итак, у нас имеются значения, разбитые по ячейкам, но нам нужно, чтобы они были в одной ячейке и ещё с дополненным текстом. Вводим в первую строку так, как нам нужно, повторяем то же действие на второй строке и на третьей нажимаем Ctrl+E и радуемся результатом
3. Вы уже кричите от того почему вы не знали об этом раньше? Тогда продолжаем удивляться «Мгновенным заполнением»))
У нас есть текст с цифрами в ячейках и нам нужно вывести в другой столбец только цифры, делать это вручную означает почувствовать себя Золушкой, перебирающей горох, но волшебным нажатием Ctrl+E, получаем нужный результат, причём Excel не важно, есть ли там лишние пробелы, знаки или прочее, он как настоящий мужик – берёт и делает.
То же самое он делает и с извлечением только текста
Если попадается разношёрстный текст, где первым стоит слово затем цифра, а в нижних строках имеется сначала цифра потом текст, лучше сначала в первых двух строках дописать цифры перед текстом, затем нажать Ctrl+E, тогда Excel правильно определит алгоритм и выведет текст без ошибок
4. Ты до сих пор сомневаешься в возможностях мгновенного заполнения? Говоришь он не справится с датами? Забери свои слова обратно, даты он изменяет на изи, причём определяет не только цифровые, но и текстовые, главное, чтобы они были в именительном падеже, либо на внятном сокращении месяца
5. Ты тайный поклонник нестандартных дат? Не волнуйся, мгновенное заполнение удовлетворит твои желания. Он может изменить обычную дату в Unix-формат ГГММДД. Для этого меняем формат соседнего столбца на текстовый, затем вводим форму значений в пару ячеек, чтобы правильно задать алгоритм и нажав Ctrl+E получаем результат
Вы ещё не устали удивляться великолепным «Мгновенным заполнением»? Тогда получайте очередную порцию волшебства))
6. У нас имеется столбец с телефонными номерами, но они выглядят как простые цифры, мы же придадим им красоты, добавив + и заключив код оператора в скобки. Преобразуем цифры и соседний столбец в текстовый формат, затем вводим нужный параметр в первые две ячейки соседнего столбца и нажав Ctrl+E, гордимся проделанной работой
7. Для тех, кто выгружает даты из различных ERP и CRM-систем, дата представляется 8-значным числом в формате ГГГГММДД. Исправлять в нормальный вид можно было функцией ДАТАЗНАЧ, либо вручную, но теперь ловите лайфхак от героя «Мгновенного заполнения», главное покажите ему правильный алгоритм, дальше он всё сам сделает за вас))
При использовании мгновенного заполнения помните следующие моменты:
– первое правило напомню, ячейки должны находиться рядом с определяемым столбцом;
– при выявлении шаблона учитываются все значения в строке – слева и справа от вводимого столбца, поэтому лишние колонки, способные запутать алгоритм или внести помехи, лучше заранее отделить от рабочих данных пустыми столбцами или удалить;
– мгновенное заполнение также работает и в умных таблицах;
– ошибка или опечатка при наборе ячеек-образцов может привести к тому, что мгновенное заполнение не сможет найти нужное значение и не сработает;
– бывает, что шаблон определяется некорректно, поэтому всегда проверяйте полученные результаты (хотя бы выборочно).
Как вам «Мгновенное заполнение», поделитесь впечатлениями в комментариях))
Источники:
https://support.office.com/ru-ru/article/%D0%98%D0%B7%D0%BC%D0%B5%D0%BD%D0%B5%D0%BD%D0%B8%D0%B5-%D0%BF%D0%B0%D1%80%D0%B0%D0%BC%D0%B5%D1%82%D1%80%D0%BE%D0%B2-%D1%88%D0%B0%D0%B3%D0%B0-%D0%B7%D0%B0%D0%BF%D1%80%D0%BE%D1%81%D0%B0-power-query-3e221afc-c764-4cd9-9a96-a5a5a7688e46
https://zen.yandex.ru/media/id/5c4d8a530aaeef00ac928ac3/5c52f58b84869700acac6a89
https://pikabu.ru/story/mgnovennoe_zapolnenie_v_excel_6999444
