Как применить формулу на весь столбец в excel

Как применить формулу ко всему столбцу в Excel (5 простых способов) | Самоучитель excel / word

Добрый день, друзья!

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

СОДЕРЖАНИЕ БЛОГА

1. Двойным кликом по маркеру автозаполнения

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

• В ячейке C2 введите формулу: = B2 * 15%.

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

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

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

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

2. Перетаскивая маркер автозаполнения

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

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

Может быть интересным: Как преобразовать краткий формат даты в название месяца?

3. Использование опции Заполнить (она находится на ленте)

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

• Выделите все ячейки, в которых вы хотите применить формулу (включая ячейку C2). В группе редактирования щелкните значок "Заполнение"

4. Путем копирования-вставки ячейки.

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

• Скопируйте ячейку C2 (используйте сочетание клавиш Ctrl + C). Выделите все ячейки, в которых вы хотите применить ту же формулу (кроме ячейки C2). Вставьте скопированную ячейку Ctrl + V

5. Использование сочетания клавиш Ctrl+D

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

• Выделите все ячейки, в которых вы хотите применить формулу (включая ячейку C2). Удерживая клавишу Ctrl, нажмите клавишу D.

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

На этом у меня всё. 🏁 Если вам понравился сегодняшний трюк, ставьте лайки 👍 и подписывайтесь на канал. Если хотите посмотреть еще уроки загляните в СОДЕРЖАНИЕ , обязательно еще что-нибудь присмотрите )) Спасибо!

Как протянуть (распространить) формулу в Excel: несколько способов

Рассмотрим несколько способов протягивания (копирования) формул в строках и столбцах программы Excel.

Первый способ: протянуть ячейку в столбце или строке.

Чтобы протянуть (распространить) формулу из одной ячейки в несколько ячеек столбца или строки следует выполнить ряд действий:
1. Записать функцию (формулу) в ячейку и нажать ENTER.
2. Навести курсор на нижний правый угол ячейки таким образом, чтобы он приобрел форму тонкого черного крестика.
3. Нажать и удерживать левую кнопку мыши.
4. Не отпуская кнопку мыши потянуть крестик в нужном направлении. В ту сторону, в которую следует распространить значение ячейки.

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

Для быстрого заполнения столбца формулой или значением из ячейки достаточно выполнить следующие действия:
1. Записать функцию (формулу) в ячейку и нажать «ENTER».
2. Навести курсор на нижний правый угол ячейки таким образом, чтобы он приобрел форму тонкого черного крестика.
3. Кликнуть двойным кликом по правому нижнему углу ячейки.

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

Третий способ: протянуть формулу без изменения (смещения) исходных ячеек.

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

Четвертый способ: протянуть формулу при помощи копирования.

Подходит для работы под фильтрами.
Выполняется следующим образом:
1. Записать функцию (формулу) в ячейку и нажать ENTER.
2. Копировать значение ячейки при помощи выпадающего контекстного меню правой кнопкой мыши или сочетанием клавиш «ctrl»+»c».
3. Выделить нужный диапазон.
Для быстрого выделения вниз достаточно нажать сочетание клавиш:
«Ctrl»+»shift»+ стрелка вниз
Для быстрого выделения в сторону достаточно нажать сочетание клавиш:
«Ctrl»+»shift»+ стрелка в сторону

4. После того, как выделили диапазон, следует вставить скопированную формулу при помощи выпадающего контекстного меню правой кнопкой мыши или сочетанием клавиш «ctrl»+»v».

Работа в Excel с формулами и таблицами для чайников

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

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

Формулы в Excel для чайников

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

В Excel применяются стандартные математические операторы:

Оператор	Операция	Пример
+ (плюс)	Сложение	=В4+7
- (минус)	Вычитание	=А9-100
* (звездочка)	Умножение	=А3*2
/ (наклонная черта)	Деление	=А7/А8
^ (циркумфлекс)	Степень	=6^2
= (знак равенства)	Равно
	Меньше
>	Больше
	Меньше или равно
>=	Больше или равно
	Не равно

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

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

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

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

Ссылки можно комбинировать в рамках одной формулы с простыми числами.

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

В нашем примере:

1. Поставили курсор в ячейку В3 и ввели =.
2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:

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



Как в формуле Excel обозначить постоянную ячейку

Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.

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

1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:
2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

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

Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.

Ссылки в ячейке соотнесены со строкой.

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

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

1. Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

• $В$2 – при копировании остаются постоянными столбец и строка;
• B$2 – при копировании неизменна строка;
• $B2 – столбец не изменяется.

Как составить таблицу в Excel с формулами

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

Простейшие формулы заполнения таблиц в Excel:

1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+"=", чтобы вставить столбец.
2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.
3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» - выбираем формулу для автоматического расчета среднего значения.

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

Расчет значений по данным в ячейках таблицы в Numbers на Mac

В формулы можно вставлять ссылки на ячейки, диапазоны ячеек и даже целые столбцы и строки с данными, в том числе ссылки на ячейки в других таблицах и на других листах. Приложение Numbers использует для вычисления формулы значения в ячейках, на которые даны ссылки. Например, если включить в формулу ссылку «А1», она будет указывать на значение в ячейке A1 (в столбце А и в ряду 1).

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

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

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

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

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

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

  СУММ(Лист 2::Таблица 1::C2:G2)

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

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

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

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

Нижеприведенная формула ссылается на столбец по названию его заголовка «Количество гостей» и на ячейку B2 в таблице на другом листе.

Excel скопировать формулу на весь столбец - Полезные советы по MS Excel

MS Excel

Быстрое заполнение ячейки

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

Ограничение числа столбцов и строк

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

Для более быстрого выделения столбцов нажмите комбинацию клавиш Ctrl+"стрелка вправо" — курсор окажется в последнем столбце; выделите его, переместитесь в начало таблицы и выделите первый из скрываемых столбцов при нажатой клавише Shift. Для быстрого выделения строк проделайте ту же операцию, применив вместо комбинации Ctrl+"стрелка вправо" комбинацию Ctrl+"стрелка вниз«.

Быстрая нумерация столбцов и строк

Столбцы и строки в таблицах Excel приходится нумеровать довольно часто. Самый простой и быстрый способ добиться такой нумерации — поставить в начальной ячейке исходный номер, например «1», установить мышь в правом нижнем углу ячейки (курсор станет напоминать знак «плюс») и при нажатой клавише Ctrl протащить мышь вправо (при нумерации столбцов) или вниз (в случае нумерации строк). Но нужно иметь в виду, что данный способ работает не всегда — все зависит от конкретной ситуации.

В таком случае можно воспользоваться формулой =СТРОКА() при нумерации строк или =СТОЛБЕЦ() при нумерации столбцов — эффект будет тот же

Печать заголовков на каждой странице

По умолчанию заголовки столбцов выводятся только на первой странице. Чтобы они печатались на всех страницах, в меню Файл выберите команду Параметры страницы > Лист и в группе Печатать на каждой странице в поле Сквозные строки укажите строку с подписями столбцов — заголовки появятся на всех страницах документа.

Заполнение вычисляемого столбца автоматически

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

Воспользуйтесь условным форматированием (команда Формат > Условное форматирование) и установите для шрифта ячеек белый формат, например, в том случае, если содержимое ячейки равно нулю

Если речь идет только о нулях, то можно поступить еще проще, запретив отображение нулевых значений командой Сервис->Параметры-> Вид — для этого уберите флажок Нулевые значения в разделе Параметры окна

Запуск калькулятора Windows из Excel

Если при работе в Excel вам постоянно требуется калькулятор Windows, то совсем необязательно для его запуска каждый раз выбирать команду Пуск > Программы > Стандартные > Калькулятор. В Excel предусмотрена возможность поместить кнопку калькулятора на панель инструментов. Для этого откройте окно Настройка с помощью команды Сервис > Настройка, перейдите на вкладку Команды и в списке Категории выберите Сервис. Затем найдите в списке команд значок калькулятора, прокрутив список команд вниз (рис. 20), и перетащите этот значок на панель инструментов. Теперь для запуска калькулятора вам будет достаточно щелкнуть по этой кнопке.

Суммы для групп ячеек

Предположим, что у вас имеется информация по продажам, перевозкам и пр., например, за месяц. Нужно найти промежуточные суммы по данным одного из столбцов за каждый день по отдельности. Для этого необходимо накапливать сумму по строкам до тех пор, пока дата остается прежней, а когда она меняется — следует переходить к вычислению новой суммы. Как правило, для решения такой задачи прибегают к созданию макроса. Но есть способ проще — воспользуйтесь функцией ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь), указав равенство дат в качестве логического выражения. Тогда сумма должна накапливаться при истинности условия и формироваться заново в противном случае

Стоимость рабочего дня

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

Есть более удобный выход из положения. Можно вводить время в текстовом формате, тогда внешне оно будет выглядеть привычно: «часы:минуты». Затем нужно будет перевести время в соответствующий ему числовой формат с помощью функции ВРЕМЗНАЧ(время_как_текст) и сформировать формулу вычисления стоимости отработанного времени.

Для этого в отдельные ячейки таблицы вначале введите стоимость часа и коэффициент пересчета — они потребуются для проведения вычислений. Коэффициент пересчета равен числовому значению одного часа, которое вычисляется с помощью функции ВРЕМЗНАЧ

После этого для определения стоимости часа введите функцию вида =Стоимость часа*ВРЕМЗНАЧ(Отработанное время)/Коэффициент пересчета

Сложная фильтрация

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

Например, пусть у нас имеется таблица ввоза-вывоза в область некоторого товара. Задача — оставить в таблице только те записи, где груз прибыл из Москвы (то есть в имени станции отправки фигурирует комбинация «МОС», например МОС-ТОВ-КИЕВ или МОС-ТОВ-ПАВ), и код груза — «593014».

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

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

Автор: Алексей Шмуйлович

12 простых приёмов для ускоренной работы в Excel

Николай Павлов

1. Быстрое добавление новых данных в диаграмму

Если для построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

2. Мгновенное заполнение (Flash Fill)

Предположим, у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.). Чтобы сделать это, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно. Подобным образом можно извлекать имена из email, склеивать ФИО из фрагментов и так далее.

3. Копирование без нарушения форматов

Вы, скорее всего, знаете о волшебном маркере автозаполнения. Это тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, так как копируется не только формула, но и формат ячейки. Этого можно избежать. Сразу после того, как потянули за чёрный крест, нажмите на смарт-тег — специальный значок, появляющийся в правом нижнем углу скопированной области.

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Excel скопирует вашу формулу без формата и не будет портить оформление.

4. Отображение данных из таблицы Excel на карте

В Excel можно быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин «Карты Bing» (Bing Maps). Это можно сделать и по прямой ссылке с сайта, нажав кнопку Get It Now.

После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней. При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.

5. Быстрый переход к нужному листу

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

6. Преобразование строк в столбцы и обратно

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

1. Выделите диапазон.
2. Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
3. Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose). В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).

7. Выпадающий список в ячейке

Если в какую-либо ячейку предполагается ввод строго определённых значений из разрешённого набора (например, только «да» и «нет» или только из списка отделов компании и так далее), то это можно легко организовать при помощи выпадающего списка.

1. Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
2. Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data → Validation).
3. В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
4. В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.

8. Умная таблица

Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home → Format as Table), то наш список будет преобразован в умную таблицу, которая умеет много полезного:

1. Автоматически растягивается при дописывании к ней новых строк или столбцов.
2. Введённые формулы автоматом будут копироваться на весь столбец.
3. Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.
4. На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.

9. Спарклайны

Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и так далее.

10. Восстановление несохранённых файлов

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

На самом деле есть шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» → «Последние» (File → Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks).

В Excel 2013 путь немного другой: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks).

В последующих версиях Excel следует открывать «Файл» → «Сведения» → «Управление книгой».

Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.

11. Сравнение двух диапазонов на отличия и совпадения

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

1. Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).
2. Выберите на вкладке «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения» (Home → Conditional formatting → Highlight Cell Rules → Duplicate Values).
3. Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.

12. Подбор (подгонка) результатов расчёта под нужные значения

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом: всего-то пара десятков итераций «недолёт — перелёт» — и вот оно, долгожданное попадание!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Данные» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert → What If Analysis → Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

Если этот обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!

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

Как скопировать формулу/формулу на весь столбец?

Очень удобно использовать формулы в Excel 2016. Благодаря им мы можем быстро автоматизировать различные расчеты по конкретным данным. В этом совете мы покажем вам Как скопировать формулу на примере вычисления произведения двух ячеек. Копировать можно тремя способами:

Для копирования с помощью маркера заполнения

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

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

Данная формула будет скопирована. Здесь стоит напомнить, что такую ​​операцию можно выполнять как в столбце, так и в строке.

Копировать с помощью кнопки Копировать

Опция копирования с помощью кнопки Копировать позволит использовать готовую формулу в любом месте электронной таблицы.

1. В начале выбираем строку, в которой находится формула, либо создаем свою.
2. Затем перейдите к Панель формул и выберите формулу.
3. Выбрав формулу, перейдите на ленту Главная и в разделе Буфер обмена нажмите команда Копировать . Такой же эффект можно получить, нажав клавиши Ctrl + C .

Формула скопирована. Теперь мы можем вставить нашу формулу в любое место рабочего листа, нажав кнопку Вставить находится в разделе Буфер обмена в меню Главная , или нажав Ctrl + V .

Автоматическое создание вычисляемых столбцов

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

1. Теперь вам просто нужно ввести формулу в первой строке столбца.Это можно сделать вручную, набрав целевую функцию, или воспользоваться разделом Библиотека функций в меню Формулы , выбрав готово.
2. После создания формулы она должна автоматически заполняться во всем созданном столбце. Если это не так, возможно, отключена опция Остановить автоматическое создание вычисляемых столбцов .
3. Эффектом отключения этой опции будет наличие только одной кнопки в панели Параметры автозамены , как это видно на график ниже.Запускаем недостающую опцию, зайдя в меню File .
4. Теперь выберите раздел Опции из доступных пунктов.
5. В появившемся окне перейдите на вкладку Проверка .
6. В разделе Параметры автозамены щелкните Параметры автозамены .
7. Нам нужно только перейти на вкладку Автоформат, набрав , и отметить опцию Заполнить формулы в таблицах для создания вычисляемых столбцов .Одобряем весь процесс кнопкой ОК .

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

Вам нравится эта статья? Поделись с другими!

.

Использовать вычисляемые столбцы в таблице Excel

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

Примечание. Исключения вычисляемого столбца создаются в следующих ситуациях:

• Вводит данные, отличные от формулы, в ячейку вычисляемого столбца.

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

• Введите новую формулу в вычисляемый столбец, в котором уже есть одно или несколько исключений.

• Копировать данные, не соответствующие формуле вычисляемого столбца, в вычисляемый столбец.

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

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

  Примечание: Это исключение не помечено.

• Перемещает или удаляет ячейку, указанную в строке вычисляемого столбца, на другом листе.

Уведомление об ошибке появляется, только если включена фоновая проверка ошибок. Если вы не видите ошибку, перейдите к > Параметры> > Формулы> и Формулы> убедитесь, что установлен флажок Включить фоновую проверку ошибок.

• Если вы используете Excel 2007, нажмите кнопку «Офис», а затем выберите «Параметры Excel » > « Формулы».

• Если вы используете Mac, перейдите к Excel в строке меню , затем нажмите «Настройки» > «Формулы и и списки» > «Проверка ошибок».

.

Ярлык для применения формулы ко всему столбцу в Excel [закрыто]

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

Есть ли ярлык, аналогичный CTRL + пробел , который применит формулу ко всему столбцу или к выбранной части столбца?

Автор: A-B-B, 24.03.2011

3 ответа

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

570

Автор: soo, 24.03.2011 21:41:02

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

• выберите ячейку, содержащую формулу, и нажмите CTRL + SHIFT + внизу , чтобы выбрать остальную часть столбца ( CTRL + SHIFT + END , чтобы выбрать данные до последней строки)
• Заполнить, нажав CTRL + D
• Используйте CTRL + UP , чтобы вернуться вверх

На Mac используйте CMD вместо CTRL .

Альтернатива, если формула находится в первой ячейке столбца:

• Выберите весь столбец, щелкнув заголовок столбца или выбрав любую ячейку в столбце и нажав CTRL + пробел
• Заполнить, нажав CTRL + D

186

Автор: robinCTS, 14.06.2017 18:13:03

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

88

Автор: Фил, 01.03.2012 16:28:38 .

Мгновенное завершение строк данных — совет 24

Завершение строк данных в Excel 2010

Excel 2010 - Быстрое заполнение таблицы - Совет 24

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

Копия

Самый известный способ завершения строк данных (например, в таблицах) — это копирование -> вставка , то есть Ctrl + C , затем Ctrl + V .Вы просто копируете формулу или значение один раз, а затем вставляете их. В зависимости от того, как вы вставляете, вы можете копировать формулу только один раз или даже тысячи раз.

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

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

Перетаскивание мышью

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

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

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

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

• Вы копируете формулу/ячейку - Ctrl + C .
• Вы переходите к соседней колонке.
• Ctrl + стрелка вниз , чтобы перейти в конец столбца, если у вас есть пустые ячейки в таблице, это замедлит вас, потому что вам придется снова нажимать этот ярлык каждый раз, когда вы сталкиваетесь с пустым полем.
• Переход к столбцу, который вы хотите завершить.
• Нажимаешь сочетание клавиш - Ctrl+Shift+стрелка вверх - благодаря этому ты выделяешь все поля снизу вверх. Включая ячейку, в которую вы впервые ввели формулу.
• Вы нажимаете Ctrl + V , что означает вставку скопированной формулы.

Мгновенный метод

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

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

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

Прямая ссылка на видео на youtube -
Excel 2010 - Мгновенное заполнение таблицы - подсказка 24

.

ссылок на ячейки Excel - Avendi

MS Office

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

Обращение к ячейкам в формулах

Каждая ячейка в Excel имеет свой адрес, который состоит из буквы столбца и номера строки, например, ячейка на пересечении столбца C и строки 3 имеет адрес C3.

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

Ссылки тесно связаны с формулами. Данные формулы могут быть:

• введены вручную
• рассчитаны по другой формуле
• получены из ячеек Excel

В следующем примере данные для функции ДАТА.РАЗНИЦА () поступают из ячеек B2 и C2.

В следующем примере данные для функции ДАТА.РАЗНИЦА () поступают из разных источников. Одна дата берется из ячейки с адресом B2, а вторая дата вводится вручную как аргумент функции.

В следующем примере данные для функции ДАТА.РАЗНИЦА () поступают из разных источников. Одна дата берется из ячейки с адресом B2, а вторая дата вычисляется с помощью функции СЕГОДНЯ(). Функция СЕГОДНЯ() вернет в качестве результата текущую дату, и эта дата будет использоваться в качестве аргумента функцией ДАТА.РАЗНИЦА().

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

Вы можете использовать ссылки:

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

Типы ссылок на ячейки:

• относительные
• абсолютные
• смешанные

По умолчанию Excel использует относительные ссылки в формулах.

Относительные ссылки

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

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

Абсолютные ссылки

Этот тип ссылки предотвращает изменение адреса в формуле при ее копировании в другую ячейку. Абсолютный адрес содержит знак доллара ($) перед буквой столбца и перед номером строки, например, $C $3. Тот же заблокированный (замороженный) адрес остается в формуле, скопированной в другую ячейку. Ссылки на столбцы и строки взяты из фактического адреса ячейки и не изменяются при копировании.

В приведенном ниже примере вознаграждение каждого сотрудника конвертируется из злотых в доллары США.Формула была введена в ячейку C2 и перетащена в ячейку C5. Вместе с изменением положения ячейки, содержащей формулу:

• в первой части формулы (на символ деления), изменились адреса ячеек, из которых собираются данные для расчетов
• в первой части формулы. во второй части формулы (после символа деления) не изменился адрес ячейки, из которой извлекаются данные, так как этот адрес (Е2) был заблокирован и принял вид $E$2

Смешанные ссылки

Этот тип ссылки заключается в блокировке строки или столбца, т.е.$С3, С$3. Ссылка на строку является относительной, а ссылка на столбец — абсолютной, или наоборот. После копирования формулы изменяется только разблокированное измерение.

В приведенном ниже примере мы рассчитываем сумму, подлежащую оплате за купленные товары, по двум ценам. Формула была введена в ячейку B3 и перетащена в ячейку C6. С изменением положения ячейки, содержащей формулу:

• в первой части формулы (до знака умножения) изменился номер строки. Буква столбца осталась неизменной, так как заблокировано
• во второй части формулы (после знака умножения) буква столбца изменилась.Номер строки не изменился, так как был заблокирован

Изменение типа ссылки

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

Сочетание клавиш F4 работает следующим образом:

• ячейка C3 - нажатие клавиши F4 введет абсолютную адресацию и вы получите адрес $C$3
• ячейка $C$3 - после нажатия клавиши F4 вы получите адрес C$3
• ячейка C$3 - после нажатия клавиши F4 вы получите адрес ячейки $C3
• $C3 - после нажатия клавиши F4 вы получите адрес C3

Ссылки на именованную ячейку или именованная область

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

В приведенном ниже примере для ячейки с адресом A3 использовалось имя «товары», а для ячейки с адресом B2 использовалось имя «цена». В формуле используются ссылки на названия «искусство» и «цена». Таким образом, Excel использовал абсолютную ссылку, и результат формулы недействителен. Независимо от того, куда мы копируем или перетаскиваем формулу, благодаря абсолютной ссылке данные для расчетов всегда берутся из ячейки А3 (штук) и из ячейки В2 (цена).

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

Ниже имя "art_range" использовалось для диапазона ячеек A3:A6, а имя "price_range" использовалось для диапазона B2:C2. Формула работает правильно.

Ссылки на ячейки с другого листа

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

Если в имени листа есть пробел, он будет заключен в апостроф.

Ссылки на ячейки из другой книги

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

Следующий пример ссылается на другую книгу на том же компьютере.

Если в имени книги есть пробел, оно будет заключено в апостроф.

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

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

.

Как преобразовать метку времени UNIX в дату в Excel?

1.	В пустой ячейке рядом со списком меток времени введите эту формулу = R2 / 86400000 + ДАТА (1970,1,1), нажмите клавишу Enter.
3.	Теперь ячейка находится на читаемой дате.

Как преобразовать метку времени в дату в Excel?

Чтобы преобразовать дату в отметку времени, формула может решить эту проблему.Предположим, вы выбрали пустую ячейку Cell C2 и введите в нее эту формулу = (C2-DATE (1970,1,1)) * 86400 и нажмите клавишу Enter, если вам нужно, вы можете применить диапазон с помощью этой формулы перетащив маркер автозаполнения .

Как преобразовать время UNIX в обычное время?

Общая формула преобразования временной метки UNIX в обычную дату выглядит следующим образом: = (A1 / 86400) + DATA (1970,1,1) , где A1 — расположение номера временной метки UNIX.

Как преобразовать время эпохи в дату в Excel?

С электронной таблицей Excel вы можете легко преобразовать и временную метку Unix, и дату. Введите эту формулу = (A1-Data (1970,1,1)) * 86400 в пустую ячейку и нажмите «Ввести ».

Использует ли Excel время Unix? секунд, прошедших с 1 января , 1970, 00:00 Excel использует аналогичный расчет для значений даты.Однако Excel вычисляет значение даты на основе 1 января 1900 года, а Excel кодирует свои метки времени как доли дней, а не секунд.

Какой формат метки времени?

Automated Time Time Time Parsing

9006- 10-011 10-011 26 02: 31: 29.573

Пример	Пример
гг.
гг.	2017-07-04 * 13: 23: 55
гг-ММ-дд ЧЧ: мм: сс, SSS ZZZZ	11-02-11 16: 47: 35.985 +0000
гг-ММ-дд GH: мм: сс, SSS
гг-ММ-дд ЧЧ: мм: сс	10-04-19 12:00:17

Какое текущее время?

В специальном регистре CURRENT TIMESTAMP (или CURRENT_TIMESTAMP) указывается: метка времени, основанная на чтении часов при выполнении операторов SQL на сервере приложений .

Как вручную преобразовать дату в метку времени в Unix?

В этой статье мы покажем вам, как конвертировать устаревшие метки времени UNIX.

...

Преобразование метки времени в дату.

1.	В пустой ячейке рядом со списком меток времени введите эту формулу = R2 / 86400000 + ДАТА (1970,1,1), нажмите клавишу Enter.
3.	Теперь ячейка находится на читаемой дате.

Как вычисляется дата эпохи?

(Данные2-Данные1) * 86400

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

Как преобразовать дату в эпоху?

Преобразовать удобочитаемую дату в эпоху

long epoch = new java.text.SimpleDateFormat ("MM/dd/yyyy HH:mm:ss") .parse("01/01/1970 01:00:00 ")). GetTime()/1000; Временная метка в секундах, уберите "/1000" для миллисекунд. date +%s -d "1 января 1980 г., 00:00:01" Замените '-d' на '-ud', чтобы ввести время по Гринвичу / UTC.

Как применить функцию ко всему столбцу в Excel?

Выберите ячейку с формулой и соседние ячейки, которые вы хотите заполнить.Нажмите «Главная»> «Заполнить» и выберите «Вниз», «Вправо», «Вверх» или «Влево». Сочетание клавиш: вы также можете нажать Ctrl + D , чтобы заполнить формулу в столбце, или Ctrl + R, чтобы заполнить формулу справа от строки.

Как прочитать временную метку Unix?

Чтобы найти текущую временную метку Unix , используйте параметр % s в команде date . Параметр % s вычисляет временную метку Unix, находя количество секунд между текущей датой и эпохой Unix.Вы получите другой результат, если запустите указанную выше команду даты.

.90 000 X. LOOKUP — Революция в поиске Excel спустя 30 лет? - SkuteczneRaporty.pl

С 2010 года я веду блог SkuteczneRaporty.pl, посвященный визуализации данных, информационным панелям и новостям в Excel. Время от времени здесь всплывает другая проблема, поэтому статья ВПР и по сей день является самым посещаемым местом на этом сайте. Поэтому нельзя игнорировать новые формулы в Excel 365, которые уже есть в открытом доступе: X.ПОИСК и X.ПОИСКПОЗ. Это настоящая революция после 30 лет ВПР?

В 1979 году формула ПРОСМОТР появилась в VisiCalc, предшественнице Excel, а в 1980-х годах она стала частью Excel. Большинство отчетов Excel, которые я встречал в компаниях, по-прежнему функционируют на комбинированных таблицах ВПР и сводных таблицах. И вот, в 2020 году его преемник — формула Х. ПОИСК (XLOOKUP в английской версии Excel) выходит на широкий круг пользователей.

Если у вас есть Office 365 и формула еще не доступна, вам нужно дождаться обновления.Первоначально он был доступен участникам программы предварительной оценки, теперь он является стандартным, но некоторые организации используют метод отложенного 4-месячного обновления. Это один из параметров панели администрирования Office 365.

Большинство пользователей используют ВПР с последним аргументом 0 или ЛОЖЬ. Мы также понимаем, что сама формула имеет ограничения. Некоторые из них:

• Только правильный поиск (если кто-то не заменит его на ИНДЕКС и ПОИСКПОЗ.ПУНКТ)
• Всегда находит первый элемент сверху
• Возвращает ошибку при сбое
• Иногда работает медленно с большими файлами (хотя в Excel 365 должно было улучшиться)

Синтаксис обеих формул очень похож, однако новая формула имеет больше параметров (6 аргументов против 4).

Отличие 1: если опустить аргумент типа поиска, поиск будет точным, т. е. значение по умолчанию равно 0.

Отличие 2: Мы передаем искомый массив и возвращаемый массив отдельно.

Это позволяет выполнять поиск как слева, так и по горизонтали. Раньше приходилось использовать комбинацию формул ИНДЕКС и ПОИСКПОЗ.

Diff 3: значение может быть указано в случае ошибки

Этому соответствует четвертый аргумент X. Игнорируя этот аргумент, мы все равно получаем ошибку #Н/Д. Раньше нам требовалась дополнительная формула ЕСЛИОШИБКА.

Отличие 4: если точное значение не найдено, мы можем найти ячейку ниже.

При обычном использовании ВПР вы можете найти только ячейку выше, либо используя последний аргумент как 1, либо опуская аргумент. Данные еще нужно отсортировать. С новой формулой у нас есть 3 варианта: 0 - точно, -1 - вверх, 1 - вниз.

Отличие 5: Понятно, что мы можем искать неточно.

В Excel это поведение ВПР было тщательно скрыто, и немногие пользователи Excel знают об этом. Я сам не знал, пока после первоначальной публикации статьи мне не написал читатель Кшиштоф (спасибо!).Больше людей знают это поведение для окна поиска (Ctrl + F), где мы можем ввести символы:

? - один символ

* - несколько символов

~ -?, * или ~ (если эти 3 символа должны быть частью текста)

Diff 6: X.LOOKUP — это формула массива.

Это означает, что мы можем указать более 1 ячейки для возврата, и результат будет распределен по следующим столбцам. В приведенном ниже примере, стоя в ячейке B2, я указал, что хочу получить результаты из 2-х столбцов — G и H — и одним нажатием Enter вылил формулу в ячейку рядом с — C2.

Отличие 7: метод поиска можно изменить.

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

Опции:

1 - от начала до конца (по умолчанию)

-1 - с конца на начало

2 и -2 — используйте более быструю двоичную поисковую систему, но данные должны быть отсортированы

Отличие 8: Поиск по горизонтали и по вертикали одновременно.

Excel также имеет формулу HLOOKUP, которую также можно заменить на X.LOOKUP. X.LOOKUP немного похож на INDEX.

Отличие 9: Возможность создавать логические условия.

Поскольку X.Search работает как формула массива, мы можем проверить, выполняются ли условия, и вернуть первое найденное, удовлетворяющее им.

• Не у всех есть Excel 365
• Не многие пользователи знают эту формулу

Я предпочитаю ежедневно использовать слияние в Power Query, но вижу 5 причин для использования этой формулы в сценариях Excel:

• Поиск по умолчанию — точность
• Одна формула для всего
• Поиск фрагмента текста, который был доступен при ПОИСКЕ.ВЕРТИКАЛЬ, только сейчас мне стал виден
• Поиск с конца
• Одновременный поиск нескольких фрагментов информации с помощью формулы массива (один щелчок Enter)

X. ПРОСМОТР — не единственная формула поиска. Рядом с MATCH (в английской версии MATCH) у нас теперь стоит формула X.MATCH, но переведенная как X.MATCH (XMATCH). И будь умным здесь человек. Его аргументы очень похожи, и после прочтения статьи вы легко сориентируетесь в этом.

Здесь вы можете скачать файл Excel с 10 примерами X. ПРОСМОТР.

Поделитесь своим мнением в комментарии.

.

---

Смотрите также

• 
  Как создать восстановительную флешку windows 10
• 
  Как зарегистрироваться в телеграм канал
• 
  Как сбросить биос msi
• 
  Как поменять кнопки мыши местами
• 
  Как в повер поинте сделать степень
• 
  Как перенести информацию с телефона на флешку
• 
  Значки за выслугу лет steam
• 
  Как поставить пароль на роутер асус
• 
  Тп линк как зайти в настройки роутера
• 
  Как отключить режим в самолете на компьютере с помощью клавиатуры
• 
  Точки для ника