Quem utiliza o Microsoft Excel pode se beneficiar de fórmulas que ajudam em trabalhos como cálculos matemáticos, formatação de textos, inserção de datas em planilhas e muito mais. Trabalhar com linhas e quadros envolve muita organização e paciência.
Para quem precisa otimizar suas planilhas há diversas fórmulas do Microsoft Excel que facilitam o trabalho do usuário. Com apenas alguns caracteres é possível indicar uma célula que realizará a soma do valor de outras células assim que um novo número for inserido, ou formatar um texto automaticamente assim que ele for colado. Veja alguns exemplos e torne sua vida mais fácil.
Conheça 10 fórmulas de Excel que vão ajudar em seu trabalho (Foto: Reprodução/André Sugai)
SOMA
SOMA é a fórmula mais conhecida e básica que existe no Excel. Com ela, você soma o valor de células. Para utilizar, basta selecionar uma célula e escrever =SOMA(A1:A3) - A1:A3 é o intervalo de células que possuem valores que serão somados onde escrever a fórmula.
Fórmula de soma (Foto: Reprodução/André Sugai)
MÉDIA
Essa fórmula vai calcular a média entre as células escolhidas. Assim, ao digitar =MÉDIA(A1:A7) você terá a média dos valores contidos entre as células que vão de A1 até A7.
Fórmula para calcular a média (Foto: Reprodução/André Sugai)
ARRED
ARRED permite arredondar números definindo a quantidade de dígitos. Ao usar a fórmula =ARRED(A1;2) o usuário indica que vai arredondar o valor da célula A1 usando 2 dígitos.
Fórmula para arredondar valores (Foto: Reprodução/André Sugai)
MÁXIMO
Em =MÁXIMO(A1:A12) há o maior número no intervalo entre as células A1 e A12.
Fórmula para encontrar o valor máximo (Foto: Reprodução/André Sugai)
AGORA
Ao escrever =AGORA() você terá como retorno a data e hora atual em que inseriu a fórmula.
Fórmula para inserir data e hora (Foto: Reprodução/André Sugai)
A fórmula ARRUMAR ajuda na formatação de textos que possuam espaçamentos desnecessários entre palavras. Usando =ARRUMAR(A1) você indica que o texto contido na célula A1 deve ser arrumado.
Fórmula para eliminar espaços entre palavras (Foto: Reprodução/André Sugai)
NÚM. CARACT
Para saber o número exato de caracteres contidos em uma célula, incluindo os espaços entre as palavras, digite =NÚM.CARACT(A1) onde A1 é a célula com o texto que será analisado.
Fórmula para contar o número de caracteres (Foto: Reprodução/André Sugai)
CARACT
A fórmula permite inserir caracteres especiais em ASCII utilizando números que variam entre 1 e 255, para inserir o símbolo de copyright basta informar 169 na fórmula =CARACT(169);
Fórmula para inserir caracteres especiais (Foto: Reprodução/André Sugai)
VALOR
A fórmula de VALOR converte strings em números. Por padrão, os números digitados no Excel são convertidos para strings. Se você for utilizar algum programa que vá obter os dados numéricos, precisará converter esses dados para numerais utilizando a fórmula =VALOR(5000), sendo o 5000 o valor que será convertido;
Fórmula converter string em números (Foto: Reprodução/André Sugai)
DIREITA e ESQUERDA
A função DIREITA e ESQUERDA retorna o número de caracteres de uma célula, assim o usuário pode definir o número exato de caracteres que deseja e indicar a célula para que obtenha o resultado. No exemplo usamos a fórmula =DIREITA(A1;5) onde informamos que desejamos os 5 caracteres da direita na célula A1;
Fórmula para alinhar o conteúdo para direita ou esquerda (Foto: André Sugai/TechTudo)
O Microsoft Excel possui centenas de fórmulas para todos os tipos de trabalho podem facilitar suas planilhas, dentro do próprio programa é possível pesquisar e conhecer novas ferramentas.
Update: In April 2015 Phase II of the TrueCrypt audit was completed, effectively giving TrueCrypt a clean bill of health. We now therefore recommend using VeraCrypt, a TrueCrypt fork which has fixed most of the weaknesses found during the audit, and is under active development. Please see VeraCrypt & how-to basics for more details.
The sudden demise of TrueCrypt under very suspicious circumstances came as a shock to many who had come to rely not just on its secure file or full disk encryption, but its practical functionality, and the fact that it was a mature product whose open-source code was being carefully audited at the time of its demise with promising results.
While conspiracy theories abound over what happened, the practical problem is finding a secure alternative. Unfortunately this is not as easy as it sounds, as no true drop-in replacement for TrueCrypt exists. In this article we will therefore we look at what secure open source encryption options areavailable for those wanting to secure their files.
It should be noted that while all the programs listed here are open source (the notion of using the closed source Microsoft Bitlocker, as suggested by the devs when they pulled the plug on TrueCrypt is so ridiculous and bizarre that even our hard-bitten and cynical BestVPN team starts to reach for their tin hats just at the thought), but none them have been nearly as extensively vetted as TrueCrypt (if at all).
Being open source and at least open to auditing, does however makes these programs the most secure options available.
Platforms: Windows, OSX, Linux (Crypt4All Lite for Android is compatible) Encryption: AES_256 Pros: Per file encryption, very easy to use Cons: individual file encryption only
This very easy to use Java based file encryption program integrates with the OS, providing simple file encryption using the right-click menu button (Windows and Linux, or drag and drop for OSX). File decryption is performed by simply by double-clicking the encrypted .aes file, and entering the password you supplied when creating it. A command line interface is also supported.
Although only individual files can be encrypted, this limitation can be overcome somewhat by creating zip files out of folders, and then encrypting the zip file with AES Crypt.
Platform: Windows Encryption: AES_128 with HMAC-SHA1-128 data verification Pros: Per file encryption, right-click OS integration, can encrypt all files in a folder and multiple files at once, can use keyfile instead of a passphrase (more secure as greater entropy), more options than AES Crypt, stand-alone portable version available, very easy to use Cons: Windows only
Like AES Crypt, AxCrypt is a single-file encryption program, although it can easily encrypt/decrypt all files in a folder (not the folder itself), and multiple files at once. It also allows the creation and use of a keyfile (which is both easier than entering a pass phrase, and improves security as passwords generated in the keyfile are very strong), and will permanently shred files to prevent later recovery. A funky portable stand-alone version of AxCrypt is also available – perfect for carrying around on a USB stick.
However, although more fully featured than AES Crypt, AxCrypt is only compatible with Windows (and AxCrypt files can only be decrypted using AxCrypt), which somewhat limits the utility of this otherwise excellent little program.
Platform: Windows Encryption: AES_256, Twofish and Serpent Pros: Full disk encryption (including OS disk), setup file only 1Mb, can use key file instead of password Cons: No per-file or per-folder encryption, no ‘hidden volumes’, no ‘hide drive’ option, unmounted drives still visible to system, Windows only
Perhaps the closest thing to a straight drop-in replacement for TrueCrypt, DiskCryptor is a full disk encryption program for Windows that allows you to encrypt hard drives (including CD’s USB sticks etc.), or individual partitions, including the ability to encrypt the partition and disk on which Windows is installed. It does however lack many of the bells and whistles that TrueCrypt offered, such as ‘hidden volumes’. Earlier versions of DiskCryptor were compatible with TrueCrypt containers, but with version 5 this is no longer the case.
Encrypted drives can be mounted and unmounted, but a glitch in the program means that unmounted drives / partitions are still visible in Windows Explorer (where they can’t be accessed, but could be accidentally formatted), so the option to hide disks would be good.
Platforms: Linux, Windows (using encfs4win), Android (using Cryptonite app). With a bit of effort it is possible to get EncFS to run in OSX *. Encryption: whatever libraries are available, usually Blowfish and AES. ‘Paranoid mode’ uses AES_256, , ‘filename block encoding with IV chaining per file, external IV chaining, MAC block headers’ Pros: Easy to use, great for secure cloud storage, files encrypted individually Cons: Files in ‘personal’ folder are not encrypted, metadata (i.e. filenames and when a file is uploaded is modified in the cloud) is visible. Note that these are not really ‘cons’, but the logical consequences of EncFS’s strengths. Users should be aware of them however.
EncFS creates an encrypted volume, which is typically stored in a cloud storage folder (e.g. Dropbox) for easy cloud synching. This folder can be mounted locally as virtual drive, from where files can be accessed unencrypted, just as if they were files in a regular folder. When files are added to or changed in the virtual drive, they are encrypted and added to/changed in the encrypted folder.
An advantage when using EncFS to encrypt files kept in cloud storage is that (unlike TrueCrypt) each file in a volume is encrypted and stored individually, so a change to one file does not mean re-uploading an entire encrypted container.
Check out our step by step how-to guide to using encfs4win, or see here for a great guide to using EncFS in Linux .
A great how-to showing how easy EncFS is to use is available here.
*Update: Reader Joe Lee offers this advice for running EncFS in Mac OSX:
‘I have been using EncFS on my Mac for some time now. You can easily install it usingHomebrew “brew install encfs”, and mount your Volume “encfs -o volname=MySecretDrive ;Path/To/EncFS /Volume/MySecretDrive”. Couldn’t be any simplier.’
Platforms: Linux, DragonFly BSD, Android (Rooted only, using LUKS Manager) Encryption: Large library available, including blowfish, twofish and AES Pros: Very capable full disk encryption Cons: For experts only, impenetrable documentation
LUKS (Linux Unified Key Setup) is the standard for Linux hard disk encryption. It is built-in to most Linux distros, and can be used with the dm-crypt subsystem to provide transparent full disk encryption.
In many ways dm-crypt/LUKS is very similar to TrueCrypt, but has the advantage that it stores all necessary setup information in the partition header, enabling the user to transport or migrate this data seamlessly. Unfortunately it is very user unfriendly, is command-line only, reading the documentation requires a degree in computer science, and so is only recommended to experts. The best instructions on using LUKS that we can find are available here.
Platforms: Android Encryption: AES_256 Pros: Stealth mode to hide vaults, leaves no trace of any temporary files in the system Cons: Does not delete original files, can be somewhat slow, still very much in alpha development
This promising new Android app lets you create password protected vaults in which you can store any kind of file (including local Google Docs). Encryption / decryption times can be very slow, and the original files remain in their folder so need to be deleted manually, but app is still in alpha, so issues are to be expected.
We were concerned about internet permissions for a ‘software analytics app called “crashlytics”’, so we contacted the developer, who responded very promptly, saying,
‘About crashlytics, it is a software (still in beta i think) which has the ability to take user’s crashes and organize them in an orderly way for me to track the bugs.
since we are still alpha, I hope to use crashlytics to help squash those bugs.’
Given that the app is still in development this seems fair enough to us. We can’t recommend Secrecy yet, as development is still in its very early stages, but do think it is an app to watch.
Conclusion
Leaving aside dm-crypt/LUKS, what is interesting is that each of these programs does its own specific thing, with very little crossover in functionality between them. The best advice, therefore, is to use them all – using the right tool for the job at hand – AES Crypt for encrypting individual files (or AxCrypt for Windows-only users who do not plan on sharing their files), Diskryptor for full disk encryption, and EncFS for secure cloud storage.
Unfortunately there is no open source full disk encryption option available for Mac owners (that we know of), but Linux users willing to get to grips with the complexities if LUKS have a very flexible and secure choice.
The rest
Other open source encryption programs we have not listed here are:
FreeOTFE – won’t run on Windows 7+ 64-bit because uses unsigned drivers
TruPax – packs files into TrueCrypt containers, which can be opened in TrueCrypt or in TruPax. We have not listed this above because TrueCrypt itself is suspect. Windows/Linux/OSX.
Published 2014-09-19
Written by Douglas Crawford
I am a freelance writer, technology enthusiast, and lover of life who enjoys spinning words and sharing knowledge for a living. Find me on Google+
As you are probably already aware, a certain person decided to smear a large number of highly classified documents over the internet. Part of these documents however included the ‘ANT’ Hardware Catalogue.
ANT is a division of the NSA responsible for developing hardware devices and software for use by the ‘Tailored Access Operations’ division.
The full catalogue is available from multiple sources – Although This gives a good overview of the capabilities of each device.
The one product we’re going to focus on here is called SPARROW-II As you can see – this system is used as a mobile wireless network (WLAN) detection point.
A small computer intended to be used for WLAN collection, including from UAVs. Hardware: IBM Power PC 405GPR processor, 64 MB SDRAM, 16 MB of built-inflash, 4 mini PCI slots, CompactFlash slot, and 802.11 B/G hardware. Running Linux 2.4 and the BLINDDATE software suite. Unit price (2008): $6K. (Wikipedia)
Another thing to note at the very bottom is the price: $6000 If you’re unsure as to why we might be interested in finding out the locations of wireless networks – Wikipedia explains the concept Here. It’s also a very useful way of profiling a companies exposure during a security assessment. Normally wardriving is conducted from a car (hence the driving). But the SPARROW system allows an aircraft or small UAV to map networks, giving a large amount of capability. It also allows discovery of networks in very sparsely populated areas like, say, deserts… So the obvious thing to do now is to try and build an open-source version of this piece of hardware. Let’s break down exactly what it is we want the system to do:
Sniff WLANs
Associate WLANs with a location
Log the locations
Operate autonomously (and independant of mains power)
Now our budget is considerably lower than that of the NSA… With that in mind, the following components are used to build the system:
Alpha AWUS036H – Fairly standard USB Wifi adapter for WLAN detection (£20) – amzn.to/1fepQvc
Raspberry Pi Model B (512Mb RAM)- Cheap, small linux computer that will provide the ‘brains’ for the system (£30)- amzn.to/1ioc4Xi
8Gb SD Card – Storage for the Raspberry pi (£5) –amzn.to/1rwDgEy
Ublox GY-NEO6MV2 GPS Module – The easiest way to map a network to a set of co-ordinates. (£10) – ebay.eu/1lSoOI3
12000mAh USB Battery – Provides power in order to keep the system running – (~£25) amzn.to/1fiBLrt
USB hub – Allows us to use multiple peripherals with the Raspberry pi, and solves power issues (~£10)
So in total, that brings us to a cost of around £100. Of course, that’s assuming you pay full price. Ebay (and similar sites), or an Academic discount can be used to obtain this equipment at much cheaper rates… So how does it all fit together? The diagram below shows how all of the various bits plug into each other:If you are aiming to try to build this yourself, this tutorial is really good for interfacing the GPS Module to the Raspberry Pi using the UART GPIO pins Here (Google translate works wonders!) For testing purposes, an ethernet cable was used to allow SSH access to the Raspberry pi. When airborne however, this can also be acheived by using another 802.11 Wifi adapter as a downlink, or by using a 3G internet dongle. (In my case, I went for the 802.11 option). This means we can now remotely see the location of the system, using any number of programs that make use of the gpsd daemon (gpsmon is shown below) More importantly for wifi-mapping though, we can use the aircrack-ng suite of tools (Homepage Here). This allows us to:
Generate a log of every wireless network we’ve seen, along with it’s gps location.
Listen to every wireless client (phone/laptop/fridge) that is on, and map it
Break into insecure wireless networks
If you want to learn how to use those tools, there are far better explanations than I can give out in the wider internet. airodump-ng is below, with the local networks shown. And then, a map of all of the WLAN networks on a street in Bristol: I would like to show a specific example however: What a lot of people don’t realise is that their iPhone/Blackberry/Android/whatever phone has WiFi on by default. When a wireless client (aka Phone) is on, it is constantly asking everyone within range if they are one of a list of networks it ‘remembers’. Why does this matter? It’s trivial to create a rogue access point that pretends to be every network that anyone asks for. The wireless client will then connect to it, no questions asked, and allow it to inject malicious content into browsing sessions, sniff passwords, etc. For an example of this attack scenario, see Here. On a (slightly) less malicious level, knowing where a phone has been is useful intelligence data. It also allows us to potentially locate homes and work locations of targets. airodump-ng can show probe requests (from the aircrack-ng suite), but it’s not very good at only showing them. Instead, the command:
tcpdump -i mon0 ‘(type mgt subtype probe-req)’ -e
Will give us the requested network names, and the MAC address of the sending device. With a small amount of python magic (available at the bottom of the post), we can get a nicer display of all of the network names associated with each client, and the manufacturer of the device.
So now all we need to to is go and attach this to an aircraft So in summary, we’ve increased the amount of RAM, storage and computational power over the SPARROW-II system, whilst using a budget 60 times smaller. However, the resulting system is considerably heavier than the SPARROW-II. Currently, it weighs in at around 500g, which is quite a lot for a UAV system. However, about a third of this weight is plastic case, which could be removed and replaced with styrofoam padding. If I ever get my small UAV working again, I’ll be sure to give it a test-flight!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
#!/usr/bin/python
# Generates list of WLAN Clients in area, along with their requested networks