Уроки Excel /index.php/uroki-excel/17-nadstrojka-ot-office-menu Tue, 17 Mar 2015 23:57:34 +0300 Joomla! - Open Source Content Management ru-ru Функции ВПР2 ГПР2 /index.php/uroki-excel/17-nadstrojka-ot-office-menu/52-funktsiya-vpr2 /index.php/uroki-excel/17-nadstrojka-ot-office-menu/52-funktsiya-vpr2

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

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

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

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

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

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

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

Все значения ключа

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

Использование составного ключа

{loadmodule mod_custom, download_xlam}]]>
andrey.morin88@gmail.com (Super User) Надстройка от оffice-menu Tue, 10 Dec 2013 18:38:06 +0400
Функции МАКСЕСЛИ МИНЕСЛИ /index.php/uroki-excel/17-nadstrojka-ot-office-menu/53-funktsii-maksesli-minesli /index.php/uroki-excel/17-nadstrojka-ot-office-menu/53-funktsii-maksesli-minesli ]]> andrey.morin88@gmail.com (Super User) Надстройка от оffice-menu Wed, 11 Dec 2013 18:01:38 +0400 Функция ЗнаковПослеЗапятой /index.php/uroki-excel/17-nadstrojka-ot-office-menu/73-funktsiya-znakovposlezapyatoj /index.php/uroki-excel/17-nadstrojka-ot-office-menu/73-funktsiya-znakovposlezapyatoj ]]> andrey.morin88@gmail.com (Super User) Надстройка от оffice-menu Sat, 06 Sep 2014 11:03:03 +0400 Функция ЧислоПрописью /index.php/uroki-excel/17-nadstrojka-ot-office-menu/74-funktsiya-chislopropisyu /index.php/uroki-excel/17-nadstrojka-ot-office-menu/74-funktsiya-chislopropisyu

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

Синтаксис:

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

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

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

  • =ЧислоПрописью(12345,678901) – функция вернет строку «двенадцать тысяч триста сорок пять целых шестьсот семьдесят восемь тысяч девятьсот одна миллионная»;
  • =ЧислоПрописью(2014) – результатом будет строка «две тысячи четырнадцать».
{loadmodule mod_custom, download_xlam}]]>
andrey.morin88@gmail.com (Super User) Надстройка от оffice-menu Sat, 06 Sep 2014 12:25:37 +0400
Функции ТекстСуммы ТекстРазницы ТекстУмножения ТекстДеления /index.php/uroki-excel/17-nadstrojka-ot-office-menu/75-funktsii-tekstsummy-tekstraznitsy-tekstumnozheniya-tekstdeleniya /index.php/uroki-excel/17-nadstrojka-ot-office-menu/75-funktsii-tekstsummy-tekstraznitsy-tekstumnozheniya-tekstdeleniya

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

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

Синтаксис:

=ТекстСуммы(Число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}]]>
andrey.morin88@gmail.com (Super User) Надстройка от оffice-menu Sat, 06 Sep 2014 19:41:04 +0400
Функция ПЕРТЕКСТ /index.php/uroki-excel/17-nadstrojka-ot-office-menu/76-funktsiya-pertekst /index.php/uroki-excel/17-nadstrojka-ot-office-menu/76-funktsiya-pertekst

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

Синтаксис:

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

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

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

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

{loadmodule mod_custom, download_xlam}]]>
andrey.morin88@gmail.com (Super User) Надстройка от оffice-menu Thu, 02 Oct 2014 09:56:11 +0400
Объединение ячеек Excel без потери данных /index.php/uroki-excel/17-nadstrojka-ot-office-menu/77-ob-edinenie-yacheek-excel-bez-poteri-dannykh /index.php/uroki-excel/17-nadstrojka-ot-office-menu/77-ob-edinenie-yacheek-excel-bez-poteri-dannykh

У стандартного объединения в 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}]]>
andrey.morin88@gmail.com (Super User) Надстройка от оffice-menu Thu, 02 Oct 2014 16:59:44 +0400