ТОП 10 самых используемых математических функций. Полезные функции в excel

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

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

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

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

Мгновенное заполнение

Эта функция точно есть в Excel 2013 года. К примеру, в списке с полными ФИО нужно сократить имена и отчества. Например: из Алексеев Алексей Алексеевич в Алексеев А. А. Для этого в соседнем столбце необходимо прописать 2-3 строчки таких сокращения вручную, а дальше программа предложит автоматически повторить действия с оставшимися данными – потребуется просто нажать enter.

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

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

1. Выделить область
2. Скопировать данные
3. Правой кнопкой мыши нажать на ячейку, куда должны переместиться данные, в появившемся окне выбрать значок «Транспонировать»

В старых версиях Excel такого значка нет, зато эта команда выполняется с помощью нажатия комбинации клавиш ctrl + alt + V и выбора функции «транспонировать». И такими несложными движениями мыши в руке столбцы и строки меняются местами.

 

Условное форматирование

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

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

Чтобы функция заработала, нужно открыть вкладку «Главная», выделить поле с ячейками, в группе инструментов «Стили» найти значок «Условное форматирование» и выбрать подходящий вариант – это может быть гистограмма, цветовая шкала или набор значков. Этой же командой можно самостоятельно установить правила выделения ячеек.

 

Спарклайны

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

1. Нажать «Вставка»
2. Открыть «Спарклайны»
3. Выбрать команду «График» или «Гистограмма»
4. В открывшемся окне указать диапазон с числами и ячейки, в которых должны появиться спарклайны, основанные на этих числах

 

Макросы

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

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

 

 

Прогнозы

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

1. Указать не меньше 2 ячеек с исходными данными
2. Открыть «Данные» – «Прогноз» – «Лист прогноза»
3. В окошке «Создание листа прогноза» кликнуть на график или гистограмму
4. В области «Завершение прогноза» установить дату окончания, нажать «Создать»

 

Заполнить пустые ячейки списка

Это избавит от монотонного ввода одинаковых фраз во множество ячеек. Конечно, можно воспользоваться старым добрым копипастом (копировать ctrl+c, вставить ctrl+v), но, если нужно заполнить не 10 ячеек, а, например, сотню-другую, и местами текст будет разным, – следующая подсказке точно пригодится.

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

Дальше выделяется столбец с нашими днями недели, во вкладке «Главная» нажимаются кнопки «Найти и выделить», «Выделить группу ячеек», «Пустые ячейки». Потом в первой пустой ячейке нужно поставить знак «=», стрелкой «вверх» на клавиатуре вернуться к заполненной ячейке (на примере в таблице – «понедельник»). Нажать ctrl+enter. Готово, теперь все пустые ячейки должны заполниться продублированными данными.

 

Найти ошибки в формуле

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

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

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

 

«Умная» таблица

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

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

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

 

Копирование с сохранением форматов

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

 

Удалить пустые ячейки

Быстро расправиться со всеми ненужными пустыми ячейками очень просто:

1. Выделить столбец
2. Вкладка «данные»
3. Нажать «фильтр»

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

 

Найти отличия и совпадения двух областей

Если нужно быстро найти одинаковые или разные данные в 2-х списках, эту работу Excel проделает сам. Несколько кликов – и программа выделит схожие или отличные элементы:

Необходимо выделить списки (при этом зажать ctrl). Во вкладке «Главная» перейти к кнопке «Условное форматирование». Дальше нажать «Правила выделения ячеек» – «Повторяющиеся значения» или «Уникальные». Готово.

 

Быстрый подбор значений

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

1. Открыть «Данные» – «Работа с данными» – «Анализ «что если» – «Подбор параметра»
2. В область «Установить в ячейке» вставить ссылку на ячейку с нужной формулой
3. В области «Значение» написать нужный результат формулы
4. В области «Изменяя значение ячейки» вставить ссылку на ячейку с корректируемым значением и нажать Ок

 

Быстрое перемещение

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

 

Быстро добавить новые значения в диаграмму

Новые данные в уже готовую диаграмму можно поместить просто с помощью обычного копипаста: выделить необходимые значения, скопировать их (ctrl+c) и вставить в диаграмму (ctrl+v).

 

Расширенный поиск

Комбинация ctrl + f, как все знают, ведет в меню поиска, который найдет любую информацию в этой программе. Функция имеет несколько секретов – знаки «?» и «*» включат в поиске настоящего сыщика: с их помощью можно отыскать данные, если нет уверенности в точности запроса. Знак вопроса заменит один неизвестный символ, а астериск (этот знак в простонародье называют звездочкой) – сразу несколько неизвестных.

Когда в куче данных нужно найти именно эти знаки – перед ними ставят значок «~». Тогда программа не примет их за неизвестные символы.

Восстановление несохраненного файла

На случай наступления конца света.

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

MS Excel 2010. Последовательность команд такая: «Файл» — «Последние» — «Восстановить несохраненные книги» (кнопка внизу справа).

MS Excel 2013. «Файл» — «Сведения» — «Управление версиями» — «Восстановить несохраненные книги». И программа откроет тайный мир всех временных копий книг, которые создавались или изменялись, но не были сохранены.

 

Горячие комбинации клавиш

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

 

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

 

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

vse-kursy.com

ТОП 10 самых полезных функций Excel



       Добрый день уважаемый пользователь!

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

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

     А теперь давайте рассмотрим более подробно те самые полезные функции Excel, с которых стоит осваивать такую огромную галактику Excel:

1.    Функция СУММ. Самой первой функцией стоит изучить функцию СУММ, без нее просто не обходится ни одно математическое действие в таблицах, просуммировать ячейки, диапазон ячеек или даже множество разбросанных значений в документе, это всё во власти функции СУММ. Также для удобства использования Excel предоставляет вам возможность использования инструмента «автосумма», что еще более упрощает работу, вам стоит нажать одну кнопочку и весь диапазон чисел будет посчитан в одно мгновение от 2 до миллиона значений, увы, на калькуляторе это будет дольше и не гарантирует правильный результат. Функция таит в себе свои хитрости и секреты, которые вам очень пригодятся.
2.    Функция ЕСЛИ. Второй по важности изучения, стоит функция ЕСЛИ. Эта логическая функция позволит вам производит множество логических вычислений по многим условиям. Функция имеет возможность вложения, а это позволит вам работать с вариантами условий. Может быть, вы и испугаетесь некой сложности функции, но не стоит пугаться ее обманчивости – она очень проста и доступна. Максимально полезна она будет для экономистов и любых аналитиков.
3.    Функция СУММЕСЛИ. Третьей важной функцией в моем обзоре станет функция СУММЕСЛИ. Эта функция соединяет математические и логические разделы в одном лице и позволит вам собрать и просуммировать значения со всего диапазона по заданному критерию, а это очень поможет, когда строк и столбцов в таблице великое множество. Конечно, есть альтернативы по получению аналогичного результата, но всё же, все остальные варианты будут сложнее. Функция будет очень полезна и бухгалтерам и экономистам.
4.     Функция ВПР. Четвёртой по счёту рассмотрим функцию ВПР. Эта функция с раздела «Ссылки и массивы», является одной из самых полезных и мощных функций при работе с массивами. Поиск и работа с полученными данными из массива ваших данных будет эффективным при использовании функции ВПР, но у нее есть одно ограничение, она ищет только в вертикальных списках, хотя данные списки используются в 95%, это компенсирует ее недостаток. А если вам нужно горизонтальный поиск, вам поможет функция ГПР. Аналогом этой функции может стать соединение других функций, таких как ПОИСКПОЗ и ИНДЕКС, но о них отдельно. Очень полезная функция для анализа любых финансовых результатов и построений «дашбордов».
5.    Функция СУММЕСЛИМН. Пятой функцией нашего топ списка самых полезных функций Excel станет функция СУММЕСЛИМН. Эта функция может все, что умеет третья функция нашего списка, но только немножко больше, а именно суммировать не по одному критерию, а по многим, всё же 127 поддерживаемых критериев это очень сильно. Не стоит забывать, что для корректной работы со многими критериями и диапазонами необходимо пользоваться абсолютными ссылками. Станет полезной многим бухгалтерам и экономистам при работе с большими объемами данных.
6.      Функция ЕОШИБКА. Эта простая функция, которую я предоставил под номером шесть в моем списке ТОП 10, часто спасала меня и помогала получить результат. Достаточно часто мы можем предугадать, что возникнет та или иная ошибка, а если она возникает в средине вычислений, то ломается вся наша  вычислительная линейка. Эта функция позволит нам проигнорировать ошибку и подставить вместо нее нужный результат, что позволит сделать намного больше полезных и точных вычислений, особенно актуально применение совместно с логическими функциями. Очень-очень полезная функция, особенно для экономистов и аналитиков, так как при их работе частенько приходится работать с ошибками, которые возникают.
7.    Функция ПОИСКПОЗ. Седьмую ступеньку нашей пирамиды занимает функция ПОИСКПОЗ, которая, как и функция ВПР работает с массивами, ищет и возвращает значения согласно заданным критериям. По большому счёту эта функция часто является альтернативой функции ВПР, особенно когда ее совместить в гармоничный симбиоз с функцией ИНДЕКС. В этом случае вы сможете получить ряд преимуществ, как то поиск с левой стороны, поиск значения более чем 255 символов, а также добавлять и удалять столбики в таблицу поиска, а также многое другое.  Пригодится любым специалистам, которые работают с большими объемами информации.
8.    Функция СЧЁТЕСЛИ. На восьмом месте я разместил функцию СЧЁТЕСЛИ, которая совмещает математическое начало и логическое, своеобразное соединение функции СЧЁТ и функции ЕСЛИ. Эта функция самое-то в случае, когда вам нужно будет сосчитать что-либо и где-либо, это могут быть и текстовые значения, и даты, и числовые значения в массивах и многое другое. Несмотря на то, что функция СЧЁТЕСЛИ производит подсчёт только по одному критерию, всё же ее польза большая, да и этого зачастую с головой хватает. Функция в работе достаточно проста и неприхотлива, да и пригодится в работе специалисту любой финансовой специальности.
9.      Функция СУММПРОИЗВ. Предпоследней из списка функций моего топ списка станет функция СУММПРОИЗВ, не стоить думать, что она имеет также последние значение в работе, как раз наоборот, многие из специалистов считают ее одним из первых в работе хороших экономистов. Она отлично работает с массивами данных, несмотря на простоту ее синтаксиса, ее функциональность огромна, и осуществлять поиск и выборку данных с массивов она делает легко, быстро и чётко. Функция станет незаменима в работе для экономических специальностей.
10.      Функция ОКРУГЛ. Ну, вот добрались и до конца нашего списка самых полезных функций в Excel, который предоставлен, функцией ОКРУГЛ, с раздела статистических функций.  Почему именно ее я включил ее, потому что взял во внимание работу бухгалтера, который когда делает расчёт и у него пропадает копейка, это уже личная трагедия и головная боль. Так что, несмотря на ее простоту и непритязательность, ее польза в правильном предоставлении данных станет очень полезной и нужной. Является важной для бухгалтерских вычислений и получения точного результата.

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

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

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

      До встречи на страницах TopExcel.ru.

Сбалансировать бюджет — все равно что попасть в рай. Каждый этого хочет, но не желает делать то, что для этого нужно.Ф. Грэм

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

topexcel.ru

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

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

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

Это большой список аккаунтов. Для программы нам нужно подготовить тестовый файл, каждая строка которого будет содаржать всю информацию об аккаунте. Вид строки такой: Логин:Пароль#прокси@Юзерагент

Конечно, мы можем подготовить этот файл вручную, а можем поступить хитрее и воспользоваться формулой Excel «=СЦЕПИТЬ»В данном случае она будет выглядеть так: =СЦЕПИТЬ (B2;":";C2;"#";D2;"@";E2)Через точку с запятой «;», перечисляем все ячейки, которые нужно соединить в одну строку. Если между ячейками должны быть какие-либо символы, то их мы заключаем в кавычки "".Протягиваем формулу на все строки и получаем вот такую красоту:Осталось только выделить весь столбец и скопировать его в нужный нам текстовый файл. Очень просто.

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

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

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

3. Выделение дубликатовБывает так, что вам нужно посмотреть есть ли повторы в таблице. Можно вручную ее просматривать и листать, а можно сделать следующее:Выделяете нужные ячейки -> Во кладе «Главное» нажимаете «Условное форматирование» -> Правила выделения ячеек -> Повторяющиеся значения -> выбираете нужное форматирование (внешний вид таких ячеек) -> ГОТОВОГотово. Эксель подсветит вам дубли, с которыми вы можете дальше работать.Наглядно этот процесс показан на анимации ниже:



Многие могут спросить: «А если я хочу удалить повторы автоматически, что мне делать?»Для этого вам нужно воспользоваться другой функцией в экселе «Удалить дубликаты». Мы об этом уже писали здесь, поэтому расписывать данный процесс не буду, а просто покажу этот процесс анимированно. Искать будем дубли по столбцу «Логин» и удалять всю строку в которой есть дубли логина.4. Разделить текст по столбцам

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

Логин1;Пароль1;Прокси1;Юзерагент1Логин2;Пароль2;Прокси2;Юзерагент2Логин3;Пароль3;Прокси3;Юзерагент3Логин4;Пароль4;Прокси4;Юзерагент4Логин5;Пароль5;Прокси5;Юзерагент5Логин6;Пароль6;Прокси6;Юзерагент6Логин7;Пароль7;Прокси7;Юзерагент7Логин8;Пароль8;Прокси8;Юзерагент8Логин9;Пароль9;Прокси9;Юзерагент9

т. е. каждый элемент строки отделен от другого каким-то разделителем. В нашем примере это точка с запятой «;"Итак, что мы делаем:Открываем Excel, копируем в какой-либо столбец нужные нам строки. Далее, выделяем то, что нам нужно разделить и переходим во вкладку «Данные» -> ищем кнопку «Текст по столбцам» -> Указываем формат данных «с разделителями» и нажимаем далее -> выбираем из списка нужный разделитель или указываем свой «другой» -> нажимаем ГОТОВО и нужный нам столбец превратился в столько столбцов, сколько данных в нем было.Чтобы посмотреть наглядно, я подготовил небольшую анимацию:



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

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

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

Удачи вам в заработке первого (или уже не первого) миллиона из интернета 😉

prudbros.ru

Полезные функции Google Таблиц, которых нет в Excel / Хабр

Cтатья написана в соавторстве с Ренатом Шагабутдиновым.

В этой статье речь пойдет о нескольких очень полезных функциях Google Таблиц, которых нет в Excel (SORT, объединение массивов, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)

Очень много букв, но есть разборы интересных кейсов, все примеры, кстати, можно рассмотреть поближе в Google Документе goo.gl/cOQAd9 (файл-> создать копию, чтобы скопировать файл себе на Google Диск и иметь возможность редактирования). Оглавление:

— Если результат формулы несколько ячеек — Объединение нескольких диапазонов данных для использования в формулах — SORT — Как в SORT добавить заголовки таблицы? — FILTER — FILTER, два условия и работа с датой — Интерактивный график при помощи FILTER и SPARKLINE — IMPORTRANGE — Импорт форматирования из исходной таблицы — IMPORTRANGE как аргумент другой функции — IMAGE: добавляем изображения в ячейки — GOOGLETRANSLATE и DETECTLANGUAGE: переводим текст в ячейках

Если результат формулы занимает больше одной ячейки

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

SORT

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

Синтаксис функции:

=SORT(сортируемые данные; столбец_для_сортировки; по_возрастанию; [столбец_для_сортировки_2, по_возрастанию_2; ...]) Пример на скриншоте ниже, мы ввели формулу только в ячейку D2 и сортируем данные по первому столбцу (вместо ИСТИНА/ЛОЖЬ можно вводить TRUE/FALSE).(здесь и далее — примеры для российских региональных настроек таблицы, рег. настройки меняются в меню файл → настройки таблицы)Как в SORT добавить заголовки таблицы?

С помощью фигурных скобок {} создаем массив из двух элементов, шапки таблицы A1:B1 и функции SORT, элементы отделяем друг-от-друга с помощью точки с запятой.

Как объединить несколько диапазонов данных и отсортировать (и не только)?

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

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

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

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

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

Ну а теперь вернемся к горизонтальному массиву и вставим его в функцию SORT. Будем сортировать данные по первому столбцу, по убыванию.

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

Все разобранные примеры можно рассмотреть поближе в Google Документе.

FILTER

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

Синтаксис функции:

FILTER(диапазон; условие_1; [условие_2; ...])Одно условие

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

Введем в ячейку E3 вот такую формулу:

=FILTER(A3:C7;B3:B7=«Наталья Чистякова») Обратите внимание, синтаксис немного отличается от привычных формул, вроде СУММЕСЛИН, там диапазон условия и само условие отделялось бы при помощи точки с запятой.

Введенная в одну ячейку формула возвращает нам массив из 9-ти ячеек с данными, но после примеров с функцией SORT мы этому уже не удивляемся.

Помимо знака равенства (=) в условиях можно использовать еще >, >=, <> (не равно), <, <=. Для текстовых условий подходят только = и <>, а для чисел или дат можно использовать все эти знаки.Два условия и работа с датой

Давайте усложним формулу и добавим в нее еще одно условие, по дате продаж, оставим все продажи начиная с 01.02.17

Так будет выглядеть формула, если вводить аргументы условия сразу в нее, обратите внимание на конвертацию текстовой записи даты при помощи ДАТАЗНАЧ:

=FILTER(A3:C7;B3:B7=«Наталья Чистякова»;A3:A7>=ДАТАЗНАЧ(«01.02.17»)) Или вот так, если ссылаться на ячейки с аргументами:=FILTER(A3:C7;B3:B7=I6;A3:A7>=J6)Интерактивный график при помощи FILTER и SPARKLINE

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

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

• выделяем ячейку Е2;
• меню Данные → Проверка данных;
• правила: Значение из диапазона и в диапазоне выбираем столбец с сотрудниками из исходных данных, не переживайте, что фамилии повторяются, в выпадающем списке останутся лишь уникальные значения;

Нажимаем «Сохранить» и получаем выпадающий список в выбранной ячейке:

Ячейка с выпадающим список станет условием для формулы FILTER, напишем ее.

=FILTER(C3:C7;B3:B7=E2) И вставим эту формулу в функцию SPARKLINE, которая на основе полученных данных будет рисовать в ячейке график. =sparkline(FILTER(C3:C7;B3:B7=E2))

Так это выглядит в динамике:

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

IMPORTRANGE

Для переноса данных из одного файла в другой в Google Таблицах используется функция IMPORTRANGE.

В каких случаях она может пригодиться?

• Вам нужны актуальные данные из файла ваших коллег.
• Вы хотите обрабатывать данные из файла, к которому у вас есть доступ «Только для просмотра».
• Вы хотите собрать в одном месте таблицы из нескольких документов, чтобы обрабатывать или просматривать их.
Эта формула позволяет получить копию диапазона из другой Google Таблицы. Форматирование при этом не переносится — только данные (как быть с форматированием — мы расскажем чуть ниже).

Синтаксис формулы следующий:

IMPORTRANGE(spreadsheet key; range string) IMPORTRANGE(ключ; диапазон) spreadsheet_key (ключ) — последовательность символов атрибута «key=» (ключа) в ссылке на таблицу (после «spreadsheets/…/»).

Пример формулы с ключом:

=IMPORTRANGE(«abcd123abcd123»; «sheet1!A1:C10») Вместо ключа таблицы вы можете использовать полную ссылку на документ:=IMPORTRANGE(«docs.google.com/a/company_site.ru/spreadsheet/ccc?key=0A601pBdE1zIzHRxcGZFVT3hyVyWc»; «Лист1!A1:CM500») В вашем файле будет отображаться диапазон A1:CM500 с Листа1 из файла, который находится по соответствующей ссылке.

Если в исходном файле может меняться количество столбцов или строк, вводите во втором аргументе функции открытый диапазон (см. также подраздел «Диапазоны вида A2:A»), например:

Лист1!A1:CM (если будут добавляться строки) Лист1!A1:1000 (если будут добавляться столбцы)! Имейте в виду, что если вы загружаете открытый диапазон (например, A1:D), то вы не сможете вставить никакие данные вручную в столбцы A:D в файле, где находится формула IMPORTRANGE (то есть в конечном, куда загружаются данные). Они как бы “зарезервируются” под весь открытый диапазон — ведь его размерность неизвестна заранее.

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

Так, если в ячейку A1 вы введете ссылку на документ (без кавычек), из которого нужно загрузить данные, а в ячейку B1 — ссылку на лист и диапазон (тоже без кавычек), то импортировать данные можно будет с помощью следующей формулы:

=IMPORTRANGE(A1;B1)

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

Импорт форматирования из исходной таблицы

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

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

После копирования листа выделите все данные (нажав на левый верхний угол):

И нажмите Delete. Все данные исчезнут, а форматирование останется. Теперь можно ввести функцию IMPORTRANGE и получить полное соответствие исходного листа — как в части данных, так и в части формата:

IMPORTRANGE как аргумент другой функции

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

Рассмотрим простой пример — среднее значение по продажам из диапазона, находящегося в другом документе.

Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз).

Сначала импортируем этот диапазон:

IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D») А потом используем это как аргумент функции СРЗНАЧ (AVERAGE):=СРЗНАЧ(IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D»)) =AVERAGE(IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D»)) Получаем результат, который будет обновляться при добавлении новых строк в исходном файле в столбце D.

IMAGE: добавляем изображения в ячейки

Функция IMAGE позволяет добавлять в ячейки Google Таблиц изображения.

У функции следующий синтаксис:

IMAGE(URL, [mode], [height], [width]) URL – единственный обязательный аргумент. Это ссылка на изображение. Ссылку можно указать напрямую в формуле, взяв в кавычки:=IMAGE(“http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg”)

Или же поставить ссылку на ячейку, в которой ссылка хранится:

= IMAGE(B1)

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

На практике бывает, что ссылки на изображения хранятся на отдельном листе, и вы достаете их с помощью функции ВПР (VLOOKUP) или как-то иначе.

Аргумент mode может принимать четыре значения (если его пропустить, по умолчанию будет первое):

1. изображение растягивается до размеров ячейки с сохранением соотношения сторон;
2. изображение растягивается без сохранения соотношения сторон, целиком заполняя
3. изображение вставляется с оригинальным размером;
4. вы указываете размеры изображения в третьем и четвертом аргументам функции [height] и [width]. [height], [width], соответственно, нужны только при значении аргумента mode = 4. Они задаются в пикселях.
Посмотрим, как на практике выглядят изображения с четыремя разными значениями аргумента mode:

Четвертый режим может быть удобен, если вам нужно подбирать точный размер изображения в пикселях, меняя параметры height (высота) и width (ширина). Картинка будет сразу обновляться. Обратите внимание, что при всех режимах, кроме второго, могут оставаться незаполненные области в ячейке, и их можно залить цветом:

GOOGLETRANSLATE и DETECTLANGUAGE: переводим текст в ячейках

В Google Таблицах есть занятная функция GOOGLETRANSLATE, позволяющая переводить текст прямо в ячейках:

Синтаксис функции следующий:

GOOGLETRANSLATE (text,[source_language], [target_language])text – это текст, который нужно переводить. Можно взять текст в кавычки и записать прямо в формулу, но удобнее сослаться на ячейку, в которой текст записан. [source_language] – язык, с которого мы переводим; [target_language] – язык, на который мы переводим.

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

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

Тут пригодится функция DETECTLANGUAGE. У нее единственный аргумент – текст, язык которого нужно определить:

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

Евгений Намоконов и Ренат Шагабутдинов, а еще мы ведем канал в телеграмме, где разбираем разные кейсы с Google Таблицами, если вам интересно — заглядывайте в гости, ссылку можно найти в моем профиле.

habr.com

ТОП 10 самых используемых математических функций в Excel

         Добрый день уважаемый читатель!

Темой этой статьи я решил сделать подборку функций, которые помогут вам в математических вычислениях, поэтому прошу любить и жаловать «ТОП 10 самых используемых математических функций». Замечу это, наверное, один из самых популярных разделов, функции которого пользуются громадной популярностью. Эти функции я рассматривал и по-отдельности, так что вы сможете перейти по ссылкам и ознакомится более подробнее с ними. Вы с собственной практики знаете, что никакая экономическая или бухгалтерская деятельность не проходит без работы с математическими операциями, а это означает что этот раздел MS Excel очень нужен и полезен.

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

    Достаточно проста в использовании, и популярная функция. Главная ее особенность — это округление значений согласно всем математическим правилам, то есть, если число от 1 до 4 то, функция ОКРУГЛ будет округлять результат до 0 вниз, а если число от 5 до 9, то округление будет производиться вверх до 10. Эта функция очень нужна, когда результат очень важен и каждая копеечка на счету, особенно это касается работы в бухгалтерском учёте. Очень часто возникает цепочка вычислений, когда число после запятой уходит в бесконечность, а вам нужно только два знака после запятой для обозначения копеек, в случае, когда вам такие значения нужно суммировать может оказаться что потеряны копейки в вычислениях. Они конечно не потеряны, а просто скрыты, но от этого вам легче не будет, а вот эта функция поможет вам в промежуточных вычислениях получить правильные результаты и как результат получить точные цифры и меньше головной боли.

2. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

    Одна из интересных функций, так как ее аналог — это итоги при форматировании из простой таблицы в умную. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ позволит вам найти промежуточные итоги по нужному вами критерию в указанном диапазоне. Но, увы, диапазон для подсчёта промежуточных итогов должен быть только вертикальным, и поскольку большинство таблиц вертикальные это не является проблемой. Функция функционально может подводить итог по 11 формулах. Что очень расширяет ее границы использования, это и среднее значение, и сумма, и минимальное с максимальным значениями, и отклонения, и многие другие полезные функции. А также данная функция может игнорировать вложенные итоги, чтобы избежать двойного суммирования.

         Функция СЛУЧМЕЖДУ позволит вам случайным образом получить любое число в заданных диапазонах значений. Генерация таких чисел позволит вам тестировать формулы, заполнять диапазоны случайными числами, формировать списки вопросников, да и многое другое. Применение функции достаточно специфично и не всегда ее можно использовать, но вот при проработке новых форм и заполнений диапазонов случайными числами, это отличный инструмент, хотя для многих она хорошо и в других возможностях.

        Это, наверное, самая популярная функция в Excel. Я совсем не представляю себе таблиц, где функция СУММ не будет использована по своему назначению. Там, где надо просуммировать два и более числа, вы всегда встретите эту функцию. Эта функция заслужено занимает список 10 самых распространённых функций Excel, а не только математического раздела. При создании таблиц для экономических или бухгалтерских расчётов, обязательно будет использована функция СУММ, так как она является основой для основ. О пользе и местах где применяется СУММ можно много говорить, так как эти данные очень огромные, но надо идти дальше по нашему списку.

       Это также одна из самых полезных функций в MS Excel. Пользуются этой функцией в основном опытные пользователи, так как она соединяет в себе логическую функцию ЕСЛИ и математическую СУММ, а вот такую полезную смесь добавили в раздел «Математические функции». В этом заключается ее сложность, это соединение двух начал, но это только на первый взгляд так кажется, сама по себе функция очень проста, удобна и полезна. Необычайно полезной станет при проведении анализа, когда с массы данных нужно произвести суммирование ваших данных по нужному критерию, выбрать данные за месяц, по определённому контрагенту и прочее. Функция СУММЕСЛИ также является одной из самых полезных и нужных инструментов при работе с данными в бухгалтерской и экономической деятельности, особенно в последнем случае.

       Шестой функцией нашего списка станет функция СУММЕСЛИМН. По большому счёту, эта функция полностью копирует возможности предыдущей функции нашего списка за исключением той детали, что суммирование можно проводить не по одному критерию, а по нескольким, что значительно повышает производительность формулы, но требует специфических условий для ее применения. Если у вас много данных и вам их нужно просуммировать по группам и общим критериям, тогда СУММЕСЛИМН именно то, что сможет вам помочь.

         Это еще одна из топовых функций в арсенале MS Excel, это функция СУММПРОИЗВ. Эта функция является благодатью для экономистов, так как она совмещает в себе возможности таких функций как СУММ, ЕСЛИ, СУММЕСЛИ и СУММЕСЛИМН и является мощным инструментом при работе с диапазонами значений и массивами. И я замечу, что одновременная работа с 255 массивами, это очень сильно и мощно. Если вам функция СУММПРОИЗВ еще неизвестна, то я советую вам ее более детально изучить, так ее возможности помогут вам увеличить собственную производительность.

8. Функция ОТБР

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

       Еще одна из функций округления, это функция ОКРУГЛВВЕРХ. Как вы уже догадались, эта функция всегда округляет до ближайшего большого числа по модулю, то есть если у вас 12, то округление будет 20, если в условиях формулы стоят другие критерия, то будет округлять и к 100 и к 1000 и так далее. Я частенько ее использую, когда есть необходимость плановые расчёты вести в тысячах и все мои полученные данные аккуратно округляются к ближайшей тысяче, что делает данные наглядными и красивыми, отнюдь не влияя негативно на полученный результат.

      Следующая наша функция, это будет функция ОКРУГЛВВЕРХ, является антиподом предыдущей функции нашего списка ТОП 10 математических функций. Как видно из самого названия основное отличие ее это в вертикальной направленности округления, то было вверх, а эта функция округляет вниз. Ее применение также порадует экономистов или других специалистов финансового сектора, которые не нуждаются в абсолютно точных значениях, а существует необходимость их округления, в этом случае функция ОКРУГЛВНИЗ поможет привести ваш отчёт в красивый и функциональный документ.

        Ну, вот мы и рассмотрели ТОП 10 самых используемых математических функций в Excel. Это были самые полезные и распространённые функции математической группы, и знакомство с ними принесет вам только пользу и улучшит вашу работу с данными в Excel.

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

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

       До встречи на страницах TopExcel.ru!

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

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

topexcel.ru

Текстовые функции в Excel. Часть №3

       Добрый день уважаемый читатель!

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

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

1.  Функция Т;
2.  Функция ПЕЧСИМВ;
3.  Функция РУБЛЬ;
4.  Функция ПОИСК;
5.  Функция ТЕКСТ;
6.  Функция СЦЕПИТЬ;
7.  Функция ФИКСИРОВАННЫЙ.

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

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

      Синтаксис функции:

            = Т(_значение_), где:

• значение – ссылка на значение которое необходимо проверить.

      Пример применения:

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

      Синтаксис функции:

           = ПЕЧСИМВ(_текст_), где:

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

      Пример применения:

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

       Синтаксис функции:

            = РУБЛЬ(_число_, _число_знаков_), где:

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

       Пример применения:       Стоить помнить, что форматирование ячейки с помощью функции РУБЛЬ, в отличие от стандартного форматирование через контекстное меню, превращает свой результат в текст, в отличие от других способов. Но, в принципе, использовать в формулах результат функции возможно, так как MS Excel имеет возможность превращать числа которые вносятся как текст, в процессе вычисления в числовые значения.

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

      Синтаксис функции:

           = ПОИСК(_искомый_текст_; _текст_для_начала_поиска_;[_начальная_позиция_]), где:

• искомый текст – указывается текстовое значение которое необходимо отыскать. Можно использовать символы подстановки;
• текст для начала поиска – указывается текст или ссылка на ячейку содержащую текстовое значение;
• начальная позиция – является необязательным аргументом и при его отсутствии по умолчанию имеет значение 1. Указывает с какой позиции в тексте необходимо начать поиск.

       Пример применения:

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

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

      Синтаксис функции:

           = ТЕКСТ(_значение_;_формат_), где:

• значение – указывается числовое значение или указывается ссылка на него;
• формат – вводится формат который будет применен к аргументу «Значение».

      Пример применения:

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

        Синтаксис функции:

             = СЦЕПИТЬ(_текст№1_;_ [текст№2]_;….), где:

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

       Пример применения:        Обращаю ваше внимание что функция не умеет ставить пробелы между аргументами, поэтому вы сделаете это самостоятельно. Так же при желании можно сцеплять текст с помощью знака амперсанда «&» не используя функцию. Более детально о возможностях вы прочтете в статье «Как используется функция СЦЕПИТЬ в Excel».

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

       Синтаксис функции:

            = ФИКСИРОВАННЫЙ(_число_;_число_знаков_;_без_разделителей_), где:

• число – это ссылка на числовое значение или число, которое будет округлено и превращено в текст;
• число знаков – указываем количество цифр после запятой;
• без разделителей – этот аргумент является логическим значением и если он указан как ИСТИНА, то функция не будет включать разделители тысяч в текст который возвращается.

       Пример применения:       Обращаю ваше внимание что для аргумента «Число знаков» есть возможность указать до 127 значащих цифр, также если аргумент отрицательный, число будет округлено до десятичного знака, а в случае отсутствия аргумента, по умолчанию его значение будет равно 2. Если для аргумента «Без разделителей» указана ЛОЖЬ или он отсутствует, разделители тысяч будут включены. Также напоминаю, что отформатированное число функцией ФИКСИРОВАННЫЙ будет переделано в текст.

        Ну вот и описаны все запланированные текстовые функции в Excel. Я надеюсь подборка в 21 функцию вам понравилась и стала полезной. Не забудьте также посмотреть часть 1 и часть 2 этой статьи.

       А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

       Не забудьте поблагодарить автора!

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

topexcel.ru

Новые возможности Excel 2013 - Excel



Примечание: Служба SkyDrive теперь называется OneDrive, а приложение SkyDrive Pro — OneDrive для бизнеса. Дополнительные сведения об этих изменениях можно найти в статье От SkyDrive к OneDrive.

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

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

Самые полезные функции

Быстрое начало работы



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

Мгновенный анализ данных



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

Мгновенное заполнение столбца данных



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

Создание подходящей диаграммы



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

Использование срезов для фильтрации данных



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

Отдельные окна для книг



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

Новые функции Excel



Были добавлены некоторые математические, тригонометрические, статистические и инженерные функции, функции для работы с датой и временем, ссылками, а также логические и текстовые функции. Также добавлены функции для работы с веб-службами REST. Дополнительные сведения см. в статье Новые функции Excel 2013.

Сохранение файлов и обмен ими в Интернете



В Excel упрощено сохранение книг в веб-хранилищах, таких как OneDrive и корпоративная служба Office 365. Также упрощен обмен листами с другими пользователями. Независимо от своего устройства и местоположения каждый пользователь работает с последней версией листа — возможна даже совместная работа в реальном времени. Дополнительные сведения об этих функциях см. в статье Сохранение книги в Интернете.

Внедрение данных листа в веб-страницу

Для обмена листами в Интернете их можно внедрять в веб-страницы. Другие люди смогут поработать с данными в приложении Excel Online или открыть внедренные данные в Excel.

Обмен листами Excel в онлайн-встрече

Независимо от вашего местоположения и устройства (смартфона, планшета или ПК) с его помощью можно подключиться к онлайн-встрече и обмениваться книгами, если на нем установлено приложение Lync. Дополнительные сведения см. в статье Публикация книги в Интернете.

Сохранение файлов в новом формате

Теперь можно сохранять и открывать файлы в новом формате Strict Open XML Spreadsheet (XLSX). Он позволяет считывать и записывать даты в формате ISO8601, что устраняет проблему с ошибочной интерпретацией 1900 года как високосного. Дополнительные сведения см. в статье Сохранение книги в другом формате.

Новые функции для создания диаграмм

Изменения ленты диаграмм



Новая кнопка Рекомендуемые диаграммы на вкладке Вставка помогает выбирать подходящий для данных тип диаграммы. Похожие типы диаграмм, например точечные и пузырьковые, теперь объединены в группы. Также добавлена кнопка для комбинирования диаграмм. При выборе диаграммы появляется упрощенная лента "Работа с диаграммами". В ней отображаются только вкладки Конструктор и Формат, что облегчает поиск нужных элементов.

Быстрая настройка диаграмм



Три новых кнопки позволяют быстро выбирать и просматривать изменения элементов диаграммы (например, заголовков или меток), ее внешнего вида и стиля или отображаемых данных. Дополнительные сведения см. в статье Форматирование диаграммы.

Форматируемые метки данных



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

Просмотр анимации в диаграммах

Диаграммы меняются при изменении исходных данных. Это делает внесение изменений не только интереснее, но и нагляднее.

Широкие возможности анализа данных

Создание подходящей сводной таблицы



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

Создание различных типов сводных таблиц с помощью одного списка полей



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

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

Новая модель данных Excel позволяет использовать полезные функции анализа, ранее доступные только после установки надстройки Power Pivot. Помимо создания обычных сводных таблиц, теперь можно создавать сводные таблицы на базе нескольких таблиц Excel. Импортируя различные таблицы и создавая связи между ними, можно получать результаты анализа, недостижимые при использовании обычных сводных таблиц. Дополнительные сведения см. в статье Создание модели данных в Excel.

Power Query

В наборах приложений Office профессиональный плюс 2013 и Office 365 профессиональный плюс доступна надстройка Power Query для Excel. Она упрощает поиск информации в общедоступных и корпоративных источниках, а также подключение к ним. Это средство поддерживает новые способы поиска данных, а также функции для простого преобразования и слияния информации из нескольких источников для последующего анализа в Excel. Дополнительные сведения см. в статье Обнаружение и объединение данных с помощью Power Query для Excel.

Power Map



Если вы используете Office 365 профессиональный плюс, Office 2013 или Excel 2013, вы можете воспользоваться надстройкой Power Map для Excel. Power Map — это средство для трехмерной (3D) визуализации данных, позволяющее взглянуть на информацию под разными углами с использованием географических данных и параметров времени. Вы даже можете обнаружить тенденции, которые не смогли увидеть в традиционных двумерных (2D) таблицах и диаграммах. Надстройка Power Map есть в пакете Office 365 профессиональный плюс, но для ее использования с Office 2013 или Excel 2013 нужно скачать ознакомительную версию. Дополнительные сведения об этой пробной версии см. в статье Power Map для Excel. Дополнительные сведения об использовании Power Map для создания виртуальных трехмерных обзоров на основе выбранных данных см. в статье Приступая к работе с Power Map.

Подключение к новым источникам данных

Чтобы использовать несколько таблиц в модели данных Excel, теперь можно подключаться к дополнительным источникам данных и импортировать данные из них в Excel в виде обычных или сводных таблиц. Например, вы можете подключиться к таким источникам, как OData, Windows Azure DataMarket и источникам данных SharePoint. Также можно подключаться к источникам дополнительных поставщиков OLE DB.

Создание связей между таблицами

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

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

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

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

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

Использование вычисляемых компонентов и показателей OLAP

В данные сводной таблицы, подключенной к кубу OLAP, можно добавлять вычисления, основанные на MDX. Необязательно использовать модель объектов Excel — создавать показатели и вычисляемые компоненты и управлять ими можно непосредственно в Excel.

Создание автономной сводной диаграммы

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

Представление Power View



В наборе приложений Office профессиональный плюс есть представление Power View, которое вызывается с помощью соответствующей кнопки на ленте. Вы сможете взглянуть на свои данные под разными углами с помощью интерактивных, эффективных и удобных функций анализа, визуализации и представления информации. Представление Power View позволяет создавать и использовать диаграммы, срезы и другие средства визуализации данных на одном листе. Дополнительные сведения см. в статье Представление Power View в Excel 2013.

Новые и улучшенные надстройки и конвертеры

Power Pivot для надстройки Excel

В Office профессиональный плюс 2013 и по подписке на Office 365 профессиональный плюс вместе с Excel устанавливается надстройка Power Pivot. В Excel теперь встроен модуль анализа данных Power Pivot, что позволяет создавать простые модели данных непосредственно в приложении. Power Pivot позволяет создавать более сложные модели. С ее помощью можно фильтровать данные при импорте, задавать собственные иерархии, поля вычислений и ключевые показатели эффективности (KPI), а также использовать язык выражений анализа данных (DAX) для создания сложных формул. Дополнительные сведения см. в статье Надстройка Power Pivot в Excel 2013.

Надстройка Inquire

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

support.office.com

---

• 
  Как редактировать hosts файл
• 
  Xmp в биосе что это
• 
  Установка виндовс 7 с флешки через биос пошаговая инструкция
• 
  Линукс с флешки установить
• 
  Как на ноутбуке включить курсор на
• 
  Интел сокеты по годам
• 
  Новые учебники
• 
  Как на ноутбуке включить курсор на
• 
  Интел сокеты по годам
• 
  Новые учебники
• 
  Как узнать пароль от роутера wifi