Как в excel удалить лишние столбцы

Как удалить пустые столбцы и строки в Excel 2013 – самый быстрый и простой способ

Если в Вашей таблице Excel присутствует много пустых строк, Вы можете удалить каждую по отдельности, щелкая по ним правой кнопкой мыши и выбирая в контекстном меню команду Delete (Удалить), но для большой таблицы это займёт уйму времени! Существует более простой и быстрый способ удалить пустые столбцы или строки в Excel.

Сначала займёмся пустыми строками. Потом удалим пустые столбцы. Это делается практически точно так же.

Выделите область электронной таблицы Excel, в которой Вы хотите удалить пустые строки

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

Нажмите Find & Select (Найти и выделить) в разделе Editing (Редактирование) на вкладке Home (Главная) и в раскрывшемся меню выберите Go To Special (Выделить группу ячеек).

В диалоговом окне Go To Special (Выделить группу ячеек) выберите пункт Blanks (Пустые) и нажмите ОК.

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

В разделе Cells (Ячейки) на вкладке Home (Главная) нажмите Delete (Удалить) и в выпадающем меню выберите Delete Sheet Rows (Удалить строки с листа).

Все пустые строки будут удалены, а остальные окажутся смежными.

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

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

Повторяем те же шаги: нажмите Find & Select (Найти и выделить) в разделе Editing (Редактирование) на вкладке Home (Главная) и в раскрывшемся меню выберите Go To Special (Выделить группу ячеек).

В диалоговом окне Go To Special (Выделить группу ячеек) выберите пункт Blanks (Пустые) и нажмите ОК.

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

В разделе Cells (Ячейки) на вкладке Home (Главная) нажмите Delete (Удалить) и в выпадающем меню выберите Delete Sheet Columns (Удалить столбцы с листа).

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

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

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

Используйте опцию Go To Special (Выделить группу ячеек) с большой осторожностью, особенно в таких ситуациях, как описана здесь. Если Вы не обратили внимания, то в процессе всех манипуляций проведенных автором, была удалена первая строка в таблице, а он этого даже не заметил!

How to Edit a Query in PQ

If you want to make some changes in the query which is already in your workbook you can simply edit it and then make those changes. On the Data tab, there’s a button named Queries and Connections.

When you click on this button, it opens a pane on the right side that lists all the queries that you have in the current workbook.

You can right-click on the query name and select edit and you will get it in the power query editor to edit.

When you edit a query, you can see that all the steps which you have performed earlier are listed in the “Applied Steps” that you can also edit or you can perform new steps.

And once you are done with your changes you can simply click on the “Close & Load” button.

Другие операции с символами в Excel

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

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

Хотите быстро удалять любые лишние символы или пробелы в ваших таблицах?!SEMTools существенно расширит возможности вашего Excel.

Как удалить каждую вторую строку в Excel?

Проредить таблицу можно с помощью макроса. Например, такого:

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

1. В конце таблицы делаем вспомогательный столбец. Заполняем чередующимися данными. Например, «о у о у о у» и т.д. Вносим значения в первые четыре ячейки. Потом выделяем их. «Цепляем» за черный крестик в правом нижнем углу и копируем буквы до конца диапазона.
2. Устанавливаем «Фильтр». Отфильтровываем последний столбец по значению «у».
3. Выделяем все что осталось после фильтрации и удаляем.
4. Убираем фильтр – останутся только ячейки с «о».

Вспомогательный столбец можно устранить и работать с «прореженной таблицей».

Вставка двух и более столбцов

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

1. Выделить область по горизонтали – столько ячеек, сколько планируется вставить столбцов.
2. Пользуясь ранее приведенными способами добавления колонок, вставить нужное количество. Стоит выбирать тот метод, который наиболее удобен и понятен для работы.

После того как нажаты все необходимые кнопки, слева от выделенного фрагмента появятся пустые столбики. Можно продолжать работу в программе «Эксель».

Ответы 2

Во-первых, ваши данные должны быть согласованы как в исходной таблице, так и в таблице преобразования, если вы собираетесь ссылаться на информацию между ними. Вы не можете использовать «Rolls» в одном и «Roll» в другом для обозначения одного и того же. Точно так же вы не можете использовать для этой цели «Большой ящик» и «Большой ящик». Я изменил «Rolls» на «Roll» и «LargeBox» на «Large Box». С этим сказано…

Начните с загрузки обеих таблиц в PowerQuery. Другими словами, у вас будет два запроса: один для таблицы продаж и один для матрицы преобразования единиц измерения. (Когда я привел вашу таблицу продаж в качестве источника, я просто назвал ее «Продажи». Я назвал вашу матрицу преобразования единиц «Матрица преобразования единиц».)

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

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

Нажмите «Добавить столбец» > «Пользовательский столбец» и заполните экран следующим образом:

… и нажмите OK, чтобы увидеть что-то вроде этого:

Нажмите на столбец BaseUnit, чтобы выбрать его, затем нажмите Transform> Fill> Down, чтобы получить что-то вроде этого:

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

Нажмите «Главная» > «Объединенные запросы» и заполните экран следующим образом, используя любое имя, которое вы использовали для своей матрицы преобразования единиц, где вы видите UnitConversionMatrix:

…и нажмите ОК.

Затем щелкните в правом верхнем углу нового столбца, который только что был добавлен:

… и настройте диалоговое окно следующим образом:

… и нажмите OK, чтобы увидеть что-то вроде этого:

Затем нажмите «Добавить столбец» > «Пользовательский столбец» и настройте его следующим образом:

… и нажмите OK, чтобы увидеть что-то вроде этого:

Затем вы можете удалить столбец Value и переместить столбец S.Units2, чтобы получить это:

Наконец, я думаю, что у вас могла быть математическая ошибка при расчете ItmC, которая дала 120 для S.Units2. Если 1 коробка = 10 упаковок и 1 упаковка = 10 штук, то 2 коробки должны равняться 2*(10*10 штук), то есть 200 штук, верно?

Также новичок в силовом запросе в Excel, поэтому не уверен, связан ли он. После добавления новых столбцов в существующие запросы я обнаружил, что не могу просмотреть новые столбцы в своем запросе на добавление, который добавляет около 4 запросов в одну большую таблицу. Даже после обновления я не мог видеть эти новые столбцы. Я обнаружил, что если я отредактирую запрос, перейду на вкладку просмотра и выберу параметры запроса, я могу выбрать символ настроек по отношению к источнику, и как только я нажал «ОК» в последующем диалоговом окне, все мои новые столбцы внезапно появились в запрос.

Надеюсь, это поможет кому-то еще, кто также слепо ориентируется в чем-то, что кажется таким тривиальным.

Другие вопросы по теме

Power Query не может добавлять столбцы при обновлении импорта CSVPower Query — добавленные запросы извлекаются из одной папки — требуется только 1 запрос для запуска новых файловЕсть ли способ в Power Query рассчитать промежуточный итог на основе двух столбцов, которые сбрасываются на основе текстового значения?Есть ли простой способ создать столбец с повторяющимся целевым значением в Power bi?Как добавить добавочные строки в Power QueryОбновление таблицы из Power query изменяет ссылку на формулуОбъединить сопоставление запросов по датам в нескольких строкахГрафик средних продаж за будний день на основе данных, состоящих из часовФормула DAX для отображения неповторяющихся значений и их подсчетаПосле добавления я получаю нулевые значения в заголовках основной таблицы

Power Query Language (M Code)

As I mentioned earlier that for every step you performed in power query it generates a code (at the backend) which is called M Code. On the Home tab, there is a button called “Advanced Editor” which you can use to see the code.

And when you click on the advanced editor it will show you the code editor and that code looks something like below:

M is a case sensitive language and like all the other languages it uses variables and expressions. The basic structure of code looks like below where the code starts with the LET expression.

In this code, we have two variables and the values defined to them. In the end, to get the value, IN expression has been used. Now when you click OK it will return the value assigned to the variable “Variablename” in the result.

Check out this resource to learn more about Power Query Language.

In the End

What is Excel Power Query?

Power Query is a data transforming engine which you can use to get data from multiple sources, clean and transform that data and then use it further in the analysis.

You can’t afford to avoid the POWER QUERY. If you think like this, a lot of things which we do with Excel functions or VBA codes can be automated using it, and I’m sure this tutorial inspires you to use it more and more.

But now you need to tell me one thing. Which thing do you like most about the POWER QUERY?

You must check out these tutorials

Create a Pivot Table from Multiple Files

Резюме

Теперь вы можете представить глубину проблемы. Хорошо, если у нас есть какие-то признаки, по которым мы можем опознать левую верхнюю ячейку нужного диапазона. Однако если ее значение не фиксировано и может быть любым (например, изменяющаяся дата или имя менеджера), задача в рамках Power Query становится почти невыполнимой.

В любом случае, решить ее средствами Power Query на данный момент очень сложно. Даже если написать функцию, проверяющую первые столбцы/строки на пустоты и удаляющие их, пока не наткнемся на заполненную строку, мы не избавлены от ситуации, когда ненужные нам строки или столбцы на самом деле не пустые и содержат какие-то значения: тогда мы не сможем удалить строки/столбцы по признаку пустоты. Каким-то способом заставить Power Query получать данные, начиная с ячейки А1, на данный момент невозможно.

Относительно практичный способ для предварительной обработки неразмеченных листов – вручную или посредством макроса присвоение имен нужным диапазонам. Это может быть сделано несколькими строками кода VBA, но подходит уже не для всех, и далеко не всегда возможно (что если нужный файл недоступен для редактирования?).

Еще одним трюком может стать обязательное размещение в ячейке А1 хоть какой-то информации или формата: можно написать маленький макрос, проверяющий адрес UsedRange и при необходимости маркирующий ячейку А1 как «использованную». Но это тоже «костыль», которому не место в таком серьезном инструменте, как Power Query или Power BI.

Я считаю использование UsedRange для определения диапазона импорта ошибочным решением. Power Query не различает форматы ячеек, и смысла в UsedRange никакого нет.

По моему мнению, необходимо, чтобы Power Query при импорте с неразмеченного листа брал данные начиная с ячейки А1 и до пересечения последней строки и последнего столбца, содержащих какие-либо значения. В таком случае мы можем гарантировано ориентироваться на начало блока, содержащего данные, и не будем брать лишние (пустые) столбцы и строки, идущие после последних заполненных ячеек. В крайнем случае, такой вариант импорта может быть опциональным, но это будет в любом случае лучше, чем сейчас.

PS. Если вы хотите поднять свои знания о Power Query для Excel и Power BI, и научиться применять этот инструмент правильно, очень рекомендую следующие книги:

• Как подключить геймпад xbox 360 к xbox 360

    

• С регистратора не скачиваются файлы

    

• Как сменить ip адрес на компьютере для авито

    

• Как выключить блокировку на ноутбуке асус

    

• Net framework 3 5 что это

Как удалить пустые строки в Excel — убираем автоматически

1. Выделите ячейки, строки или столбцы, которые вы хотите удалить.
2. Щелкните правой кнопкой мыши и выберите нужный вариант удаления, например Удалить ячейки (со сдвигом вверх), Удалить ячейки (со сдвигом влево), Удалить строки,или Удалить столбцы.

При этом можно выделять несколько не смежных столбцов, удерживая клавишу Ctrl на клавиатуре. После того как столбцы выделены, необходимо навести курсор на выделенное поле, кликнуть по нему правой кнопкой мыши и выбрать пункт «Скрыть» из контекстного меню. Выделенные столбцы будут скрыты.

Как очистить данные в ячейках Excel? Очистка данных по условиям

Как вы знаете, удалить данные/информацию в ячейках Excel можно:

• нажатием кнопки Delete на клавиатуре, после выделения нужной области
• нажатием кнопки Удалить содержимое Главная — раздел Редактирование — в нем жмите кнопку Очистить содержимое (см. описание выше)
• можно удалить не только данные, но и сами ячейки. Выделив, к примеру, строку, кликнуть правой кнопкой мыши и нажав кнопку Удалить (она же Ctrl + кнопка минус )

Помимо этого можно удалить данные и по нужным условиям.

Самое простое решение — сперва отобрать данные для удаления автофильтром . После чего вы можете удалить в выбранных ячейках все, что вам нужно — формат, данные или даже сами ячейки ( Ctrl + кнопка минус )

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

Так где ловушка?

Такое непредсказуемое поведение UsedRange при импорте с неразмеченного листа в Power Query или Power BI необходимо учитывать. Нам вряд ли помешают пустые строки и столбцы, находящиеся после данных, но пустые строки и столбцы, находящиеся перед данными – настоящая проблема , которую нельзя упускать из виду.

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

Представьте, что у вас есть несколько (или много) файлов Excel, которые заполняют разные пользователи. Файлы имеют одинаковую структуру данных, по крайней мере, в той части, которую вы хотите извлечь. Например, искомые данные всегда находятся в диапазоне E3:H9 (4 столбца и 7 строк). Первые четыре столбца и первые две строки – пустые, не содержат значений (как на рисунках выше).

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

1. Выбрать конкретный лист,
2. Оставить только столбцы с пятого по восьмой (E:H),
3. Удалить первые две строки (так как данные начинаются с третьей строки),
4. Оставить первые 7 строк в получившейся таблице.

Обычно все эти шаги не вызывают больших затруднений. Мы можем написать пользовательскую функцию или воспользоваться встроенным механизмом комбинирования данных из нескольких источников (на основе примерного файла). Однако результат и в том и в другом случае может оказаться обескураживающим.

Начиная со второго шага, мы ориентируемся на структуру листа: нам нужны данные в столбцах 5-8, соответственно мы будем пытаться удалить первые 4 столбца. Однако, как мы уже увидели выше, диапазон данных, переданных в Power Query, может как включать, так и не включать пустые столбцы (в зависимости от того, были ли изменены ячейки в нем).

Соответственно, если Power Query загрузит лист начиная со столбца A, то нужно удалять четыре первых столбца – они пустые и лишние. Если же в первых 4 столбцах листа нет данных или форматирования (то есть они не попали в UsedRange), они не загрузятся в Power Query. Первым загруженным окажется пятый столбец E, с которого начинается нужный диапазон данных. Но тогда четыре первых столбца нельзя удалять !

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

Как видите, на листе Sample4 импортируемый диапазон начинается в ячейке А1 – это четыре лишних пустых столбца. Если при разворачивании столбца мы оставим только 4 первых столбца (как нам предложит Power Query по умолчанию), данные с листа Sample4 полностью потеряются:

Куда делись данные с четвертого листа?

Если попросить Power Query загрузить все заголовки столбцов и развернуть таблицы полностью, то мы увидим, куда исчезли наши данные:

Данные совсем не там, где ожидалось их увидеть!

Можно заметить, что последний блок не только «уехал» вправо, но и расстояние между блоками непредсказуемо. Ну и как теперь объяснить Power Query, что диапазон, имеющий точную позицию на листе, может оказаться где угодно?

Как удалить строки/столбцы из нескольких диапазонов

При работе с несколькими несмежными диапазонами можно сразу «объединить и удалить», объединив пару функций в одной формуле:

1. Сложите несколько диапазонов в один, используя функцию VSTACK или HSTACK.
2. Удалите определенное количество строк или столбцов из массива с накоплением, используя функцию DROP.

В зависимости от того, как структурированы исходные данные, вам может подойти один из следующих подходов.

Объединение диапазонов по вертикали и удаление строк или столбцов

Предположим, у вас есть 3 отдельных диапазона, как показано на скриншоте ниже. Для объединения диапазонов по вертикали сверху вниз используется формула VSTACK:

=VSTACK(A4:D7, A11:D15, A19:D23)

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

Например, чтобы удалить последний столбец из сложенного массива, формула принимает следующий вид:

=DROP(VSTACK(A4:D7, A11:D15, A19:D23), , -1)

И результат выглядит следующим образом:

Объединяйте диапазоны по горизонтали и удаляйте строки или столбцы

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

=HSTACK(B3:E6, H3:K6)

Затем вы вставляете приведенную выше формулу в аргумент массива функции DROP и настраиваете аргументы строк и столбцов по мере необходимости.

В этом примере мы используем -1 для аргумента строки, чтобы удалить последнюю строку:

= УДАЛИТЬ (СТЕК (B3: E6, H3: K6), -1)

Пошаговое руководство по вашему первому скрипту Microsoft Power Query — gadgetshelp,com

• Между двумя запросами должно быть соединение или соответствие критериям. (например, столбец StudentID обоих запросов, которые должны совпадать друг с другом)
• Количество строк зависит от критериев соответствия между запросами
• Количество столбцов зависит от того, какие столбцы выбраны в наборе результатов. (В результате Merge создаст структурированный столбец).

Вы можете удалить данные из таблицы непосредственно в окне запроса. На картинке внизу, я щелкнул правой кнопкой мыши по заголовку столбца и выбрал Use First Row As Header, что означает Использовать первую строку в качестве заголовка.

Редактор запросов Power Query

Разберем подробнее интерфейс редактора запросов Power Query.

Если окно редактора у вас закрыто, откройте его в меню Данные → Получить данные → Запустить редактор запросов. Или щелкните 2 раза мышкой по названию запроса на вкладке Запросы и подключения.

Итак, в редакторе Power Query есть:

1. Лента редактора запросов для вкладок меню: Главная, Преобразование, Добавить столбец, Просмотр.
2. Перечень созданных запросов, который можно свернуть / развернуть.
3. Строка формул.
4. Название самого запроса.
5. Примененные шаги запроса: записанные шаги получения или преобразования данных. Их можно редактировать, выбирая в списке, изменять последовательность шагов, добавлять новые или удалять.
6. Область предварительного просмотра, в которой выводится результат преобразования данных для каждого шага.
7. Меню для данных, которое открывается при нажатии правой кнопкой мышки.
8. При выборе правой кнопкой мыши названия шага появляется его контекстное меню.

Как удалить скрытые строки в Excel?

Однажды пользователь скрыл некую информацию в строках, чтобы она не отвлекала от работы. Думал, что впоследствии данные еще понадобятся. Не понадобились – скрытые строки можно удалить: они влияют на формулы, мешают.

В тренировочной таблице скрыты ряды 5, 6, 7:

Будем их удалять.

1. Переходим на «Файл»-«Сведения»-«Поиск проблем» — инструмент «Инспектор документов».
2. В отрывшемся окне ставим галочку напротив «Скрытые строки и столбцы». Нажимаем «Проверить».
3. Через несколько секунд программа отображает результат проверки.
4. Нажимаем «Удалить все». На экране появится соответствующее уведомление.

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

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

Сколько столбцов в рабочем листе?

Количество столбцов в Excel варьируется в зависимости от версии, так в версии Excel 2003 столбцов 256, а в версиях 2007 и 2010 уже 16 384. Сумму значений столбца можно узнать, выделив столбец и посмотрев параметр «Сумма выделенных ячеек» в строке состояния. Строка состояния находится внизу окна, под горизонтальной полосой прокрутки.

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

Как убирать из таблицы одинаковые ячейки?

1. На вкладке «Данные» нажмите опцию «Удалить дубликаты».

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

• нажмите «Снять выделение»;
• установите щелчком мышки метку в списке, где требуется.

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

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

1. На вкладке «Данные», в блоке «Фильтр», клацните подраздел «Дополнительно».

2. В окне «Расширенный фильтр» укажите, где сохранять отредактированный список (на месте или в отдельный файл).

3. Клацните по окошку «Только уникальные записи». Нажмите «OK».

Типы данных Power Query

Типы данных — это неотъемлемая составляющая любого запроса PQ. Поэтому их обязательно нужно определять. Как правило, типы данных задаются после всех выполненных преобразований. Делается это одним из трех способов:

• через вкладку «Главная», далее «Тип данных» («Преобразование»), далее «Определить тип данных»;
• через клик указателем мыши по значку типа данных в названии столбца;
• через вкладку «Преобразование», далее «Дата», далее «Выполнить анализ».

Проверку правильности задания типов данных нужно проводить перед каждым сохранением запроса. При этом рекомендуется вручную удалять автоматически добавляемый шаг «Измененный тип».

Через меню закладок

Меню закладок – это панель вверху рабочего окна приложения. Каждая из закладок объединяет в себе набор функций и действий, которые можно совершать в редакторе. На панель закладок вынесено большинство действий из контекстного меню.

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

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

1. зайти в панель закладок «Главная»;
2. выделить столбец;
3. отыскать на панели кнопку «Удалить»;
4. нажать кнопку.

Если выделить не весь столбец, а только ячейки в таблице, рекомендуется воспользоваться подменю (маленькая стрелочка вниз, справа от надписи) для корректного удаления.

1. выделить ячейки;
2. на панели закладок открыть подменю «Удалить»;
3. выбрать «удалить ячейки»;
4. поставить галочку на «столбец»;
5. Ок.

Через объединение ячеек

По факту, действие не удаляет столбец, а соединяет его с соседним, делая из двух – один.

1. выделить ячейки, которые необходимо объединить;
2. на панели закладок «Главная» найти кнопку «Объединить и поместить в центре»;
3. справа от кнопки найти маленькую стрелочку;
4. нажать на стрелочку – появится подменю;
5. в подменю выбрать «Объединить по строкам»;
6. редактор сообщит, что при объединении, данные из правого столбца будут утеряны. Кликаем «Ок»;

Как отменить удаление

Любое действие в редакторе отменяется комбинацией клавиш Ctrl+Z.

Если вы удалили столбец и желаете сразу его восстановить – нажмите комбинацию. Переключать язык не нужно!

Можно воспользоваться стрелкой в левом верхнем углу. Она действует по тому же принципу, что и комбинация клавиш – отменяет только что совершенное действие.

Стрелка не относится к панели закладок, поэтому доступна из любой закладки.

Если после удаления столбца были совершены иные действия, восстановить столбец можно «отмотав» назад при помощи стрелки

Обратите внимание – все, что было создано после удаления столбца, будет отменено

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

Полезная информация

Можно ли удалить столбец при помощи кнопки Delete ?

Клавиша Delete удаляет не столбец, а все данные из столбца.

Если ячейка столбца участвовала в формуле

При удалении столбца выбьет ошибку и потребует исчезнувшую ссылку.

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

При объединении столбцов, ячейка не исчезает, а «маскируется». Это означает, что ссылка остается активной. Данные в замаскированной ячейке обнуляются и вычисление искривляется. Формула не изменилась, вычисления прошли, но так как ячейка пустая, программа по определению считает данные равными нулю.

По условию, должны были перемножиться данные из ячеек С5 и Е5. Так как С5 принимается за 0, результат умножения также равен нулю.

Если ячейка из столбца не принимала прямого участия в формуле

При расчете, программа автоматически изменяет координаты ячейки на новые, в соответствии со сдвигом и проводит вычисления.

К примеру: в ячейке F3 стоит формула логической проверки и умножения. В ней принимаю участие ячейки В3, С3 и Е3.

После удаления столбца D, произошли сдвиги – столбцы Е и F сдвинулись на один влево. При этом, формула видоизменилась, подставив для вычисления новые координаты. Результат остался неизменным.

Что происходит, когда удаляют столбец из объединенных ячеек?

В нашем случае, шапка таблицы состоит из двух строк. Во всех столбцах, кроме «Цена», две ячейки объединены в одну по строкам. В столбце «Цена» в первой строке объединены ячейки по столбцам: из ячеек В1 и С1 сделали одну. Если удалить столбец В, то надпись «Цена» также удалиться и верхняя строка останется пустой.

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

Where is Power Query (How to Install it)?

Below you can see how to install access to the power query in the different versions of Microsoft Excel.

Excel 2007

If you are using Excel 2007, I’m sorry PQ is not available for this version so you need to upgrade to the latest version of Excel (Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).

Excel 2010 and Excel 2013

For 2010 and 2013, you need to install an add-in separately which you can download from this link and once you install it, you’ll get a new tab in the Excel ribbon, like below:

• First, download the add-in from here (Microsoft’s Official Website).
• Once you have downloaded the file, open it and follow the instructions.
• After that, you’ll automatically get the “Power Query” tab on your Excel ribbon.

If somehow that “POWER QUERY” tab doesn’t appear, there is no need to worry about it. You can add it using the COM Add-ins option.

Go to File Tab ➜ Options ➜ Add-ins.

• In “Add-In” options, select “COM Add-ins” and click GO.
• After that, tick mark “Microsoft Power Query for Excel”.

In the end, click OK.

Excel 2016, 2019, Office 365

If you are using Excel 2016, Excel 2019, or you have OFFICE 365 subscription, it’s already there on the Data tab, as a group named “GET & TRANSFORM” (I like this name, do you?).

Excel Mac

If you are using Excel in Mac I’m afraid that there is no power query add-in for it and you can only refresh an existing query but you can’t create a new one and or even edit a query (LINK).

Совмещаем все таблицы воедино и определяем финалисток

Соединять таблицы воедино и добавлять результат попадания в финал будем поэтапно. Сначала необходимо сделать 3 запроса к каждой таблице. Вкладка «Данные», группа «Скачать и преобразовать», команда «Из таблицы».

Команда для создания запроса Power Query

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

Выгрузка запроса

В результате в редакторе запросов у нас должны появиться три подключения.

Три созданных запроса в редакторе Power Query

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

Выбираем первый запрос, дублируем его с помощью контекстного меню на самом запросе, сортируем таблицу по результату забега и оставляем только первые две строки, с помощью команды «Сократить строки» на вкладке «Главная» редактора запросов.

Команда Сократить строки в Power Query

Дальше добавляем новый столбец командой «Добавить пользовательский столбец», а в формуле столбца просто пишем значение «TRUE», не забывая изменить заголовок столбца на «Finals».

Добавление пользовательского столбца

В завершение неплохо переименовать запрос, чтобы однозначно его идентифицировать, например, «Round1Finals». Такую же операцию по выявлению финалисток следует произвести для второго и третьего раунда.

Следующим шагом будет выявление двух финалисток среди оставшихся спортсменок, т.е. двух лучших среди всех раундов, исключив шестерых отобранных. Для этого нам снова необходимо дублировать один их трех первых запросов запросов, а затем, с помощью команды «Комбинировать» добавить данные остальных двух, например, если взять запрос к первой таблице и добавить данные запросов ко второй и третей таблицы, то получится вот так:

Соединение нескольких запросов

И результат добавления всех трех запросов.

Результат добавления всех трех запросов

Дальше с помощью той же команды «Комбинировать», но выбираем «Слияние запросов» добавляем столбцы «Finals» из запросов: Round1Finals, Round2Finals, Round3Finals. Эту операцию следует проделать трижды, а в качестве столбцов для согласования лучше выбрать номер атлета (теоретически можно и имя, но могут попасться атлеты с одинаковыми именами).

Диалоговое окно слияния запросов

В трех новых столбцах следует отобразить результат прохождения в финал, т.е. трижды выбрать столбце «Finals».

Нахождение двух финалисток вне раунда

Теперь, следует убрать спортсменок, которые прошли в финал, т.е. отобразить только записи, которые содержать в последних трех столбцах значение «null». Фильтр делается по заголовку столбца, ровно также, как работает стандартный Автофильтр в Excel. Дальше сортируем записи по результату, оставляем только 2 первые и добавляем новый столбце «Finals» в который записываем значение «TRUE», а три столбца «Finals» которые остались от предыдущих запросов удаляем. В завершение можно переименовать наш запрос в «FinalAccrossRounds».

Найдены последние две финалистки (всего их

Теперь с помощью знакомой команды «Комбинировать» соединяем всех финалисток в одну таблицу и называем получившийся запрос «FinalAllRounds».

Список всех восьми финалисток

Наконец, пришло время скомбинировать итоговую таблицу, которую, в конечном итоге выгрузим на рабочий лист в качестве ответа. Итоговая таблица будет содержать в себе объединение трех первых таблиц (Комбинировать/Добавить запросы) и столбец «Finals» из таблицы «FinalAllRounds». В роли «столбца-ключа» выступит номер атлета.

Итоговая таблица которая будет выгружена на лист Excel

Осталось только поменять пустые значения на «FALSE». Делается это командой «Замена значений» из группы «Любой столбец» вкладки «Преобразование» или из контекстного меню по заголовку столбца.

Замена значений в Редакторе запросов

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

Результат работы Power Query на листе Excel

Осталось только посчитать возраст атлетов, это делается формулой основу которой будет составлять функция ДОЛЯГОДА (YEARFRAC).

Простая формула, главное знать, что есть для таких целей специальная функция.

Итоговая таблица.

Конечный результат уже с расчетом возраста

Изменение параметров шага запроса (Power Query)

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

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

Для удобства также приводим ссылку на оригинал (на английском языке) .

Примечание: Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать. Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция «Скачать и преобразовать» в Excel 2016.

Примечание: Краткое видео о том, как вывести Редактор запросов читайте в конце этой статьи.

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

Примечание: Вставка или удаление промежуточного шага запроса может нарушить работу запроса. При попытке вставить новый шаг в Power Query отображается предупреждение Вставка шага.