Уроки Excel

Функции ВПР2 ГПР2

2013-12-10T18:38:06+04:00

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

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

В надстройке также имеется функция ГПР2, ее описание аналогично данному, но последняя осуществляет горизонтальный поиск.

Синтаксис: =ВПР2(Ключ; Таблица; Столбец_поиска; [Столбец_Ключа]), где:

Ключ – обязательный аргумент. Значение либо ссылка на ячейку, содержащую значение для поиска. Когда необходимо определить несколько ключей, они должны быть записаны через символ амперсанда – &;
Таблица – обязательный аргумент. Диапазон, содержащий искомые значения и ключи;
Столбец_поиска – обязательный аргумент. Целое число, указывающее на порядковый номер столбца в таблице, из которого необходимо возвратить значение;
Столбец_Ключа – необязательный аргумент. Значение по умолчанию 1. Целое число, указывающее на порядковый номер столбца в таблице, в котором находится значение ключа. В случаях использования нескольких ключей, номера их столбцов записываются в виде массива (внутри фигурных скобок) через точку с запятой – {1; 3; 7}. Имейте в виду, что порядок указания столбцов должен совпадать с порядком задания ключей.

Пример использования:

На приведенном скриншоте функция ВПР2 возвращает для ключей 1 и 4 по два значения, разделенных точкой с запятой. При включенном переносе текста в ячейках, каждое последующее значение начинается с новой строки.

В следующей примере осуществляется поиск значений для составного ключа. Если значение не найдено, то возвращается ошибка #Н/Д.

{loadmodule mod_custom, download_xlam}

Функции МАКСЕСЛИ МИНЕСЛИ

2013-12-11T18:01:38+04:00

Функция ЗнаковПослеЗапятой

2014-09-06T11:03:03+04:00

Функция ЧислоПрописью

2014-09-06T12:25:37+04:00

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

Синтаксис:

=ЧислоПрописью(Число), где

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

Пример использования:

=ЧислоПрописью(12345,678901) – функция вернет строку «двенадцать тысяч триста сорок пять целых шестьсот семьдесят восемь тысяч девятьсот одна миллионная»;
=ЧислоПрописью(2014) – результатом будет строка «две тысячи четырнадцать».

{loadmodule mod_custom, download_xlam}

Функции ТекстСуммы ТекстРазницы ТекстУмножения ТекстДеления

2014-09-06T19:41:04+04:00

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

Синтаксис функций ТекстСуммы, ТекстРазницы, ТекстУмножения и ТекстДеления ничем не отличается, поэтому рассмотрим синтаксис только одной из них.

Синтаксис:

=ТекстСуммы(Число1; Число2; [Скобки]), где

Число1 – обязательный аргумент. Число, выступающее в качестве первого слагаемого;
Число2 – обязательный аргумент. Число, выступающее в качестве второго слагаемого;
Скобки – необязательный аргумент. Логическое значение, указывающее:
- ИСТИНА – заключить выражение в скобки;
- ЛОЖЬ – не заключать выражение в скобки (значение по умолчанию).

В качестве первого и второго аргумента может быть использована любая из функций: ТекстСуммы, ТекстРазницы, ТекстУмножения или ТекстДеления. Такая возможность позволяет выводить выражения, состоящие более чем из 2-x операций.

Примеры использования:

=ТекстСуммы(2;5) – выведет строку «2+5=7»;
=ТекстСуммы(ТекстСуммы(2;5);11) – применение вложенной функций создает следующую строку «2+5+11=18»;
=ТекстУмножения(ТекстСуммы(2;5;ИСТИНА);11) – пример использования скобок, для верного отображения порядка вычислений. Результатом формулы будет «(2+5)•11=77».

{loadmodule mod_custom, download_xlam}

Функция ПЕРТЕКСТ

2014-10-02T09:56:11+04:00

Данная функция надстройки для Excel переворачивает текст, переданный ей в качестве аргумента.

Синтаксис:

=ПЕРТЕКСТ(Текст), где

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

Пример использования:

=ПЕРТЕКСТ("Привет") – функция вернет перевернутую строку «тевирП».

{loadmodule mod_custom, download_xlam}

Объединение ячеек Excel без потери данных

2014-10-02T16:59:44+04:00

У стандартного объединения в Excel имеется недостаток, который заметен только при объединении нескольких ячеек с данными, когда сохраняется значение только верхней левой ячейки выделенного диапазона, а остальные затираются. Но часто возникает необходимость сохранить все данные во время объединения.

Настройка office-menu включает в себя возможность объединения ячеек без потери данных с возможностью их разделения специальными символами.

Установив надстройку (ссылка для ее скачивания расположена в конце статьи), на ленте появится новая вкладка «office-menu», на которой располагаются кнопки объединения без потерь и 2 поля для указания разделителей.

Кнопка «Объединить без потерь»

Данная кнопка предназначена для объединения всех ячеек внутри выделенного диапазона. Объединяя их, надстройка применяет символы-разделители, которые Вы можете задать самостоятельно (подробнее ниже в этой статье). По умолчанию для разделения значений, расположенных на одной строке листа Excel, используются квадратных скобки, а для разделения разных строк применяется символ переноса строки. Для лучшего понимания на рисунке показан пример:

Кнопка «Объединить строки»

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

Кнопка «Объединить столбцы»

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

Кнопка «Разбить по шаблону»

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

Разделители значений и строк

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

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

Например, объединим ячейки таким образом, чтобы их значения были окружены html-тегами ячейки таблицы (<td> и </td>), а строки html-тегами строки таблицы (<tr> и </tr>). Для этого запишем в поле «Разделитель значений:» строку «<td>~</td>», а в поле «Разделитель строк:» – «<tr>~</tr>». После объединения без потерь в области появится такой текст:

<tr><td>Знач.1</td><td>Знач.2</td><td>Знач.3</td></tr><tr><td>Знач.4</td><td>Знач.5</td><td>Знач.6</td></tr><tr><td>Знач.7</td><td>Знач.8</td><td>Знач.9</td></tr><tr><td>Знач.10</td><td>Знач.11</td><td>Знач.12</td></tr>

Для указания разделителем символа тильда запишите его 2 раза подряд (~~).

{loadmodule mod_custom, download_xlam}